Use Triggers/Stored Procedures to Keep Change Hist

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

MSDE (2000)

Use Triggers/Stored Procedures to Keep Change Hist

Author

Topic

bgalok
Starting Member

2 Posts

Posted - 2008-11-06 : 20:37:33

I am looking to use triggers and/or stored procedures to capture a change history on all tables in a specific database.

To do this I planned to use 2 tables:
Change_History_Header
Change_History_Detail

I would like the Header Table to contain
ID,(Altered Tables)TableName,(Altered Tables)Primary Key, Timestamp, UserName,

The Detailed Table would then contain
HeaderID,LineID,(Altered Column)Column_Name, old_value, new_value.

So if INSERT to tableA:

ID FirstName LastName Email
100 Brian Galok Brian.Galok@aol.com

Change_Header
ID Table PrimaryKey timestamp UserName
123 tableA 100 11/6/2008 8:10:35 PM DOMAIN\user

Change Detail
HeaderID LineID Column_Name old_value new_value
123 01 ID 100
123 02 FirstName Brian
123 03 LastName Galok
123 04 Email Brian.Galok@aol.com

Then tableA record 100 is UPDATED TO
ID FirstName LastName Email
100 Brian Galok Brian.Galok@hotmail.com

Change_Header
ID Table PrimaryKey timestamp UserName
123 tableA 100 11/6/2008 8:30:15 PM DOMAIN\user
124 tableA 100 11/6/2008 8:33:01 PM DOMAIN\user2

Change Detail
HeaderID LID Column_Name old_value new_value
123 01 ID 100
123 02 FirstName Brian
123 03 LastName Galok
123 04 Email Bgalok@aol.com
124 01 Email Bgalok@aol.com Brian.Galok@hotmail.com

This should also work for INSERTS and UPDATES on all tables

Delete a record will be handled separately by moving information to an archived table.

Can anyone point me in the right direction.
I started by creating a trigger on a table to populate the Header on an update but i would also i need it to work on insert:

----------
ALTER TRIGGER Copy_Cust_w_trigger_update
ON dbo.Copy_Cust_w_trigger
FOR update
AS

insert into Change_History_Header
Select 'Customer', i.Customer_ID, getDate(), SUSER_SNAME() From inserted i inner join deleted d on i.Customer_ID = d.Customer_ID

----------

And After the Header table is populated to detail would need to be populated but I'm not sure how I can pull out the only the fieldnames that have been edited. But I know I can use the insert and delete tables to get the new and the old values once i have field name(s).

Brian

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 01:07:37

Why are you hardcoding table names also? i feel you should be logging each tables changes to seperate tables.Just make the audit/history table for those whom you want to track changes.Create a trigger on each to log changes.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-11-07 : 10:58:56

See this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113363

If it is not practically useful, then it is practically useless.

bgalok
Starting Member

2 Posts

Posted - 2008-11-07 : 11:09:19

I planned to use a trigger on each table that i want to audit changes for and since the trigger is unique to the table i hard-coded the table name.

I could create a separate audit table for each table and insert the entire record form the deleted table along with any additional audit information I want to collect but I don't want the entire record just the columns that have changed.

Also I wanted to try consolidate all changes into one table and use the combination of table_name and the records primary key to link the changes.

Brian

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 11:14:25

so you want to put NULLs for unchanged columns?
Didnt understand why you need to consolidate all into a single table? Wont columns of all tables be different?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources