SQL Triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Triggers

Author

Topic

ravenhawk08
Starting Member

8 Posts

Posted - 2014-05-09 : 10:17:10

I was recently introduced to SQL Update triggers. I was able to create a trigger that captures changes being made to a single column in a table. I now need to capture changes that could be made to any one of 6 columns in the same table. The information I need to capture is: what column was changed, the date it was changed, who changed it and the before and after values. The current trigger captures all of the above 'except' the name of the column that changed.
Is there a way to identify what columns have changed using a trigger and still capture the information mentioned above?

I am using SQL 2008 and I've done some research on Change Data Capture however, I've seen posts where CDC is not able to capture WHO made the changes.

Below is the code for the current trigger. If what I am trying to do is possible, what changes would I need to make to this code?

ALTER TRIGGER [dbo].[trg_Audit] ON [dbo].[Working_Table]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO OPS.dbo.Working_Tracking (
ROW_NUM
, OWNER
, BEFORE
, AFTER
, DateModified
, MODIFIED_BY)
SELECT
inserted.ROW_NUM
, inserted.[OWNER]
, deleted.[STATUS]
, inserted.[STATUS]
, GETDATE ()
, SUSER_NAME()
FROM inserted
INNER JOIN deleted on inserted.ROW_NUM = deleted.ROW_NUM
and inserted.STATUS != deleted.STATUS;

END

Any insight you can provide would be appreciated.

Thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-09 : 11:17:40

So if 5 of the 6 columns had new values would that be 5 new rows in [Working_Tracking]? So you would need a new column in your tracking table, right?
Or do you want 1 row for any row that changed in [working_table]? ie: a bunch of new columns in your tracking table ([before] and [after] for each of the 6 columns with NULL values for columns that didn't change? Or perhaps same value in in [before] and [after] if no change?

Be One with the Optimizer
TG

ravenhawk08
Starting Member

8 Posts

Posted - 2014-05-09 : 11:52:52

Below is an example of how I envisioned this. Lets say someone updated all 6 values for the same row in the database table. The audit trail would have a separate row for each change and each row would identify the specific column that was updated. But if a column was NOT updated, then it would not appear in the audit trail. So I only want to see 'actual' changes and not the same 6 columns each time.

Hope this makes sense.

ROW_NUM OWNER COLUMN BEFORE AFTER DateModified MODIFIED_BY
30 BOB SMITH STATUS Open Recovered 2014-05-08 11:03:34.570 BILLY BOB
30 BOB SMITH AMOUNT 50.00 150.00 2014-05-07 16:36:09.087 BILLY BOB
30 BOB SMITH RESOURCE CLIENT CUSTOMER 2014-05-07 16:37:30.863 BILLY BOB
30 BOB SMITH BALANCE 250.00 100.00 2014-05-08 13:26:41.790 BILLY BOB
30 BOB SMITH OWNER BOB SMITH BILLY BOB 2014-05-08 13:27:20.377 BILLY BOB
30 BOB SMITH RSN CODE RESOLVED 2014-05-08 13:59:53.503 BILLY BOB

ravenhawk08
Starting Member

8 Posts

Posted - 2014-05-09 : 11:54:03

Sorry for the formatting of the example.. It looked fine before I clicked 'Submit Reply'

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-05-09 : 12:26:48

Here is how we do it:

IF UPDATE ([ACCT BALANCE]) 
BEGIN 
    INSERT INTO MyDatabase.dbo.TriggerLog(
        CreateDate, UserId, ServerName, ProcessName, Workstation, DatabaseName, TableName,
        FieldName, OldVal, NewVal, Key1Name, Key1Val)
    SELECT 
        GETDATE(), SYSTEM_USER, @@ServerName, APP_NAME(), HOST_NAME(), 'MyDatabase', 'MyTable', 
        '[ACCT BALANCE]', D.[ACCT BALANCE], I.[ACCT BALANCE], 'RefNum', D.RefNum
    FROM Inserted I 
    INNER JOIN Deleted D 
        ON I.RefNum = D.RefNum
            AND ISNULL(I.[ACCT BALANCE], -999) <> ISNULL(D.[ACCT BALANCE], -999)
END --IF UPDATE([ACCT BALANCE]) 

----------------------------------------------------------------------------------------------------------
IF UPDATE(MyDate) 
BEGIN 
    INSERT INTO MyDatabase.dbo.TriggerLog(
        CreateDate, UserId, ServerName, ProcessName, Workstation, DatabaseName, TableName,
        FieldName, OldVal, NewVal, Key1Name, Key1Val)
    SELECT 
        GETDATE(), SYSTEM_USER, @@ServerName, APP_NAME(), HOST_NAME(), 'MyDatabase', 'MyTable', 
        'MyDate', 
        CONVERT(VARCHAR(10), D.MyDate, 101), 
        CONVERT(VARCHAR(10), I.MyDate, 101), 
        'RefNum', D.RefNum
    FROM Inserted I 
    INNER JOIN Deleted D 
        ON I.RefNum = D.RefNum
            AND ((I.MyDate  <> D.MyDate)
                OR (I.MyDate IS NULL AND D.MyDate IS NOT NULL)
                OR (I.MyDate IS NOT NULL AND D.MyDate IS NULL))
END --IF UPDATE(MyDate)

djj

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-09 : 13:35:30

quote:
Originally posted by ravenhawk08

Sorry for the formatting of the example.. It looked fine before I clicked 'Submit Reply'

enclose your formatted text with code tags to keep the formatting.

[code]
<formatted code>
[/code]

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-09 : 13:39:42

if you have an update procedure that takes all parameters so your update statement always includes all columns even if the value didn't change then UPDATE(<col>) won't work because it will always return true.

One way would be to cross join to a table with 6 rows so that potentially each row updated by the user could result in 6 audit rows. I can provide an example in few minutes.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-09 : 13:56:39

perhaps this (untested)


ALTER TRIGGER [dbo].[trg_Audit] ON [dbo].[Working_Table] AFTER UPDATE AS
BEGIN
       SET NOCOUNT ON;

       INSERT INTO OPS.dbo.Working_Tracking (
       ROW_NUM
       , OWNER
       , SubjectColumn
       , BEFORE
       , AFTER
       , DateModified
       , MODIFIED_BY)
       SELECT i.ROW_NUM
              , i.[OWNER]
              , cols.subjectColumn
              , case
                     when cols.subjectColumn = 'status' then d.[STATUS]
                     when cols.subjectColumn = 'amount' then d.amount
                     when cols.subjectColumn = 'resource' then d.[resource]
                     when cols.subjectColumn = 'balance' then d.balance
                     when cols.subjectColumn = 'owner' then d.[owner]
                     when cols.subjectColumn = 'rsn' then d.rsn
                     end

              , case
                     when cols.subjectColumn = 'status' then i.[STATUS]
                     when cols.subjectColumn = 'amount' then i.amount
                     when cols.subjectColumn = 'resource' then i.[resource]
                     when cols.subjectColumn = 'balance' then i.balance
                     when cols.subjectColumn = 'owner' then i.[owner]
                     when cols.subjectColumn = 'rsn' then i.rsn
                     end
              , GETDATE ()
              , SUSER_NAME()
       FROM   inserted i
       INNER JOIN deleted d on i.ROW_NUM = d.ROW_NUM
       inner join (
              select 'status' union all
              select 'amount' union all
              select 'resource' union all
              select 'balance' union all
              select 'owner' union all
              select 'rsn'
              ) cols (subjectColumn)
                     on  (cols.subjectColumn = 'status' and i.[status] != d.[status])
                     or (cols.subjectColumn = 'amount' and i.amount != d.amount)
                     or (cols.subjectColumn = 'resource' and i.[resource] != d.[resource])
                     or (cols.subjectColumn = 'balance' and i.balance != d.balance)
                     or (cols.subjectColumn = 'owner' and i.[owner] != d.[owner])
                     or (cols.subjectColumn = 'rsn' and i.rsn != d.rsn)


END

Be One with the Optimizer
TG

ravenhawk08
Starting Member

8 Posts

Posted - 2014-05-09 : 14:41:34

Thanks everyone. I tried the suggestion from djj55 and it worked. Fortunately I only have 6 columns I need to track so creating an IF UPDATE statement for each column wasn't too bad. May have looked for other methods if I needed to track numerous columns.

Thanks again.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-05-09 : 14:56:08

TG, Nice. How does it handle nulls? Say i.[status] = 'Hello' and d.[status] is null?
Thanks

djj

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-09 : 15:11:09

thanks - I forgot to mention that for any nullable columns you would need to compare COALECSEd values.
ie something like these:
coalesce(i.[varcharColumn], 'NULL') = (d.[varcharColumn], 'NULL')
coalesce(i.[IntColumn], -9999) = (d.[IntColumn], -9999)
coalesce(i.[DateColumn], '1900-01-01') = (d.[DateColumn], '1900-01-01')

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources