Using Triggers to log data changes in key tables

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

SQL Server Development (2000)

Using Triggers to log data changes in key tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-15 : 08:29:16

Robert writes "Hi,

I would like to use triggers to perform the audit function in my web application of logging changes to data in key tables. What I would like to do is capture the before and after picture of the row along with the datetime it happened. The examples in the manuals (6 volume set from MS) were a bit light on and I was hoping that this forum may have some more serious examples?

Yours in anticipation and gratitide.

Robert"

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-15 : 10:19:35

It's probably lite because it's a matter of strategy.

My suggestion...

For every table, have a mirror history table. Make it identical...even the name, just add an _H to the end.

Make sure all of your base tables have "data administration" columns.

This to track who added a record and when, as well as updates.

The history table should be identical except for three additional columns.

Hist add by (the user who caused the event), the time of the event, and the action type (delete or update)

No need to worry about the "after" event, it'll be the current resident in your base table.

Here's a sample of the trigger:



CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE 
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
	BEGIN

		Insert Into Company_H (
		       HIST_ADD_TYPE
		      ,HIST_ADD_BY
		      ,HIST_ADD_SYSUSER_BY
		      ,Company_Name
		      ,Active_Ind
		      ,Psft_Company_Id
		      ,FEIN
		      ,Has_NonQual_Taxes
		      ,Has_Qual_Taxes
		      ,Created_By
		      ,Created_Ts
		      ,Updated_By
		      ,Updated_Ts
		)
		Select
		       'U'
		      ,(Select Inserted.Updated_By from Inserted 
			Where Deleted.Company_Name	= Inserted.Company_Name)
		      ,user
		      ,Company_Name
		      ,Active_Ind
		      ,Psft_Company_Id
		      ,FEIN
		      ,Has_NonQual_Taxes
		      ,Has_Qual_Taxes
		      ,Created_By
		      ,Created_Ts
		      ,Updated_By
		      ,Updated_Ts	  
		From  Deleted	   
	END


If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

	BEGIN

		Insert Into Company_H (
		       HIST_ADD_TYPE
		      ,HIST_ADD_BY
		      ,HIST_ADD_SYSUSER_BY
		      ,Company_Name
		      ,Active_Ind
		      ,Psft_Company_Id
		      ,FEIN
		      ,Has_NonQual_Taxes
		      ,Has_Qual_Taxes
		      ,Created_By
		      ,Created_Ts
		      ,Updated_By
		      ,Updated_Ts
		)
		Select
		       'D'
		      ,user
		      ,user
		      ,Company_Name
		      ,Active_Ind
		      ,Psft_Company_Id
		      ,FEIN
		      ,Has_NonQual_Taxes
		      ,Has_Qual_Taxes
		      ,Created_By
		      ,Created_Ts
		      ,Updated_By
		      ,Updated_Ts	  
		From  Deleted	
	END

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-15 : 10:30:19

Taking X's concept a bit further, ideally you would want a generic trigger that dynamically processes any table layout; this is done through queries to information_schema.columns and the use of the COLUMNS_UPDATED() function in your trigger logic.

Even better still is the use of a data warehouse, if you have the time and the budget for it.

Jonathan
{0}

Dave Kawliche
Starting Member

20 Posts

Posted - 2003-07-15 : 10:43:02

Here is a link pretty detailed walkthrough of an audit trigger scheme that makes it very easy to reconstruct data from any point in time. It is similar to Brett's suggestion but instead of tracking HIST_ADD_TYPE Updated_ts it just tracked StartDateTime and EndDateTime for the timespan when records in the audit table held valid data:

http://accesshelp.net/view.aspx?_@id=53431

best regards,

Dave Kawliche
http://AccessHelp.net
http://1ClickDB.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources