Solved

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

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Word Template Mail merge with vb.net 4 40
SQL 2012 and SQL 2014 always on 9 25
Slow SQL query 12 27
Merge Statement 3 0
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
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
Viewers will learn how the fundamental information of how to create a table.

747 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

12 Experts available now in Live!

Get 1:1 Help Now