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

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 ?
SteveB2175Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
SteveB2175Author Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveB2175Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.