Solved

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

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

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 143

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 Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Stored Proc - Rewrite 42 60
Select single row of data for each ID in Select Statement 7 28
TSQL XML Namespaces 7 24
MS SQL query to show nearest date 6 42
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

828 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