Solved

How do I create a trigger to capture who is changing data in a table ?

Posted on 2007-03-27
4
180 Views
Last Modified: 2012-06-21
I would like to create a trigger to track who made what changes to a table, but I'm really lost on this one.

I have created a table named "ChangeLog" with the following fields:
ChangedDate
TableName
FieldName
BeforeData
AfterData
ChangedBy

I have just barely started the coding:

CREATE TRIGGER [TRIGGER NAME] ON [dbo].[ShippingMaster]
FOR INSERT, UPDATE, DELETE
AS
INSERT INTO ChangeLog
(ChangeDate, DataTable, Field, BeforeData, AfterData, ChagedBy)
VALUES(GetDate(),  ===> now I'm lost...

I guess my biggest question is how do I capture values for who is changing the data, and what the before and after values are ?
0
Comment
Question by:SteveB2175
  • 2
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18802970
do you really need to store each field value, ie those that changed?
would it not be much easier to store the entire record?

anyhow:

CREATE TRIGGER [TRIGGER NAME] ON [dbo].[ShippingMaster]
FOR INSERT, UPDATE, DELETE
AS
INSERT INTO ChangeLog
(ChangeDate, DataTable, Field, BeforeData, AfterData, ChagedBy)
SELECT GetDate(), 'ShippinMaster', 'Field1', d.Field1, i.Field1, suser_name()
FROM inserted i
JOIN deleted d
  ON i.PK_OF_TABLE = d.PK_OF_TABLE
AND i.Field1 <> d.Field1

INSERT INTO ChangeLog
(ChangeDate, DataTable, Field, BeforeData, AfterData, ChagedBy)
SELECT GetDate(), 'ShippinMaster', 'Field2', d.Field2, i.Field2, suser_name()
FROM inserted i
JOIN deleted d
  ON i.PK_OF_TABLE = d.PK_OF_TABLE
AND i.Field2 <> d.Field2

and repeat that for each and every field you want to log.

now, if the field can have NULL values, the last row of the conditions needs even to be changed:
AND ( i.Field2 <> d.Field2 OR  ( i.Field2 IS NULL and  d.Field2 IS NOT NULL) OR  ( i.Field2 IS NOT NULL and  d.Field2 IS NULL)  )

0
 

Author Comment

by:SteveB2175
ID: 18803325
Hmmm, some real good stuff here.  I did not know about the 'inserted' and 'deleted' keywords.  That helps a lot.
My original intent was to keep a single ChangeLog table and write just the fields that were changed, from many data tables to ChangeLog.  Seemed small and efficient at the time.  From what you have shown me that is going to me too labor intesive coding all the triggers.
So - maybe I should keep a unique change log table for each data table in the database ?
If I do that, how do I insert the entire record ?  I tried inserted.* w/o luck.
Also, suser_name didn't work for me, but System_User did.
Thanks !!
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18805844
>Seemed small and efficient at the time.  
small in number of columns, but large in number of rows, as that table would have to store the changes of all the tables you wanted to monitor

>From what you have shown me that is going to me too labor intesive coding all the triggers.
apart from the fact that you, as "dba" (?), should not feat labor work to protect your database / job, you could write some scripts, based on information_schema.columns to help write most of the code...

>So - maybe I should keep a unique change log table for each data table in the database ?
I would do that not because of the work, but because of the managability of the log table(s).

>If I do that, how do I insert the entire record ?  I tried inserted.* w/o luck.
does the log table have the same columns than the original table?
again, you should put the effort to list all column names to protect against failures...
0
 

Author Comment

by:SteveB2175
ID: 18807815
OK, I found a missing column in my change log, and I was able to create a working trigger:

CREATE TRIGGER [trShippingMasterLog] ON [dbo].[ShippingMaster]
FOR INSERT, UPDATE, DELETE
AS
INSERT INTO ShippingMasterCL
SELECT  * , GetDate(), System_User, 'A'  FROM INSERTED

INSERT INTO ChangeLog
SELECT *, GetDate(), System_User, 'D'  FROM DELETED

The 'A' and 'D' helps me determine if the the transaction was an insert, update (A) or delete (D) when I review the change log.

Thanks so much for your help.  I'm struggling at a new job, and EE is the only thing getting me through right now !!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now