SQL Server 2008 : update multiple columns in trigger

Question

Maybe my original post was little messy, so I didn't get much help. I updated my trigger with the AuditTest table.... Please see if you can help.

I am very new to triggers and trying to put together an audit table where it tracks the value changes on certain columns.

I have a lot of columns like: Qty, UnitSell, Discount, ProductName ...etc. This code below seems to work, which detects the Qty or UnitSell value changes, then do the INSERT INTO the audit table (now I just named it as TEST1)

If I would keep repeating the IF UPDATE(FieldName) statement for other columns, I think it will work, but it is too cumbersome in which keep repeating the same codes.

Is that way to optimize this, so I don't have to repeating the same IF UPDATE (Fieldname) statement?

alter TRIGGER trigger_Test_AfterUpdate 
ON [dbo].ERP_QuoteDetail 
FOR UPDATE
AS
    declare @QuoteDetailID int; 
    select @QuoteDetailID = i.QuoteDetailID from inserted i;  

    --- Updating QTY if old/new value change
    DECLARE @iQty int; SELECT @iQty = i.Qty from inserted i; 
    DECLARE @dQty int; SELECT @dQty = d.Qty from deleted d; 

    if update(QTY) and exists (select * from deleted d WHERE Qty <> @iQty) 
    BEGIN
        -- Insert into the audit table
        insert into AuditTest (
                [Type]
               ,[TableName]
               ,[PKCol]
               ,[PK]
               ,[FieldName]
               ,[OldValue]
               ,[NewValue]
               ,[UpdateDate]
               ,[DBUsername]
               ,[UserID]
               ) 
        values('u'
                , 'Table_QuoteDetail'
               , 'QuoteDetail'
               , @QuoteDetailID
               , 'QTY'
               , @dQty
               , @iQty
               , GETDATE()
               , '123'
               , '456'             
                );

        PRINT 'AFTER UPDATE Trigger fired.'
    END

--- Updating QTY if old/new value change
DECLARE @iUnitSell int; SELECT @iUnitSell = i.UnitSell from inserted i;
DECLARE @dUnitSell int; SELECT @dUnitSell = d.Qty from deleted d; 
if update(UnitSell) and exists (select * from deleted d
         WHERE UnitSell <> @iUnitSell ) 
    BEGIN

        -- Insert into the audit table
        insert into AuditTest (
                [Type]
               ,[TableName]
               ,[PKCol]
               ,[PK]
               ,[FieldName]
               ,[OldValue]
               ,[NewValue]
               ,[UpdateDate]
               ,[DBUsername]
               ,[UserID]
               ) 
        values('u'
                , 'Table_QuoteDetail'
               , 'QuoteDetail'
               , @QuoteDetailID
               , 'UnitSell'
               , @dUnitSell
               , @iUnitSell
               , GETDATE()
               , '123'
               , '456'             
                );

        PRINT 'AFTER UPDATE Trigger fired.'
    END
GO

In its current state your trigger works under assumption that only one row is being updated at a time, which most likely won't be always the case. SQL Server triggers are statement not row based, therefore you have to accommodate for that your trigger. — peterm
– peterm, Commented Dec 12, 2013 at 0:25
Can you explain how would you like to see your change log table? — peterm
– peterm, Commented Dec 12, 2013 at 0:29
@peterm, I have updated the log table looks like as your request. thanks! — Milacay
– Milacay, Commented Dec 12, 2013 at 0:40
Used Columns_Updated() -- Please see this link msdn.microsoft.com/en-us/library/ms186329.aspx it might help you — Jade
– Jade, Commented Dec 12, 2013 at 1:55

shree.pat18 · Accepted Answer · 2013-12-12 02:20:24Z

1

You can consider using the Change Data Capture feature of SQL Server for auditing. CDC stores a record of all inserted, updated and deleted rows for whichever tables have CDC enabled. This will effectively replace the need for triggers in your case.

Details on how to enable and configure CDC can be found here.

answered Dec 12, 2013 at 2:20

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Milacay Over a year ago

Thanks, I will look into this. One question, can CDC take passing parameters sending from the page? like stored procedure?

shree.pat18 Over a year ago

AFAIK, it is not possible to pass parameters to CDC. CDC makes use of the Transaction Log to identify that a change has been made to table, and accordingly updates the change tracking table with appropriate data and metadata.

Milacay Over a year ago

Thanks for response, this might not be a solution for me because I need to capture the Session("UserID") From the application.

shree.pat18 Over a year ago

No worries. Perhaps you can get a better answer from other posters here :)

marc_s · Accepted Answer · 2013-12-12 06:15:35Z

0

Your fundamental flaw is that you seem to expect the trigger to be fired once per row - this is NOT the case in SQL Server. Instead, the trigger fires once per statement, and the pseudo table Deleted might contain multiple rows.

Given that that table might contain multiple rows - which one do you expect will be selected here??

select @QuoteDetailID = i.QuoteDetailID from inserted i;

It's undefined - you might get the values from arbitrary rows in Inserted.

You need to rewrite your entire trigger with the knowledge that the Inserted and Deleted pseudo tables WILL contain multiple rows. You need to work with set-based operations - don't expect just a single row in Deleted or Inserted !

answered Dec 12, 2013 at 6:15

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

8 Comments

Milacay Over a year ago

Thanks @marc_s, I will research more about inserted and deleted pseudo tables. One quick question, is there a way to display all the data looks like in the inserted and deleted table after an event like update/delete? I tried "select * from inserted" and getting 'invalid object' error.

marc_s Over a year ago

@Milacay: those pseudo tables are only valid inside the trigger

Milacay Over a year ago

i see. the single update statement run fast with the trigger, but 50 update statements cause pretty slow response. any suggestion?

marc_s Over a year ago

@Milacay: not sure what you mean; if you've updated 50 rows, you need to do 50 updates - one way or another..... no?

Milacay Over a year ago

I mean, with the trigger I posted running, when I have one update statement like "Update Table SET Qty = 1 where ID = 123", it runs fast. But if I have 50 update statements with different ID, it seems very slow. However, if I disable the trigger, that 50 updates run much fast. So, trigger makes the update procress slow significantly. thanks.

|

Collectives™ on Stack Overflow

SQL Server 2008 : update multiple columns in trigger

2 Answers 2

4 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related