Solved

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

Posted on 2007-03-27
4
183 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Generate Scripts Fails 5 36
SSRS 2013 - Overlapping reports 2 22
Can > be used for a Text field 6 42
SQL Server Import/Error Wizard error 12 19
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

770 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