How to get old value and new value using SQL Trigger?

Question

I have a lot of fields in a table and I am trying to audit any insert - update - delete in another table.

I just have a problem of getting the value of the exact field I've Chosen.

I wrote this code and I expect to get the old and new value of an exact field.

DECLARE @ColName NVARCHAR(225) ,
        @FieldID INT;
    DECLARE columnCursor CURSOR
    FOR
        SELECT  COLUMN_NAME
        FROM    INFORMATION_SCHEMA.COLUMNS
        WHERE   TABLE_NAME = 'acc_Account'
                AND TABLE_SCHEMA = 'dbo';

    DECLARE @deleted_colmn INT;

    DECLARE CurDB CURSOR
    FOR
        SELECT  @deleted_colmn
        FROM    #Deleted;


    OPEN columnCursor;

    FETCH NEXT FROM columnCursor INTO @ColName;

    WHILE ( @@FETCH_STATUS <> -1 )
        BEGIN



            --OPEN CurDB;
            --FETCH NEXT FROM CurDB INTO @deleted_colmn;

            SET @FieldID = ( SELECT TOP 1
                                    FieldID
                             FROM   fld_Field
                             WHERE  fld_Field.FieldName = @ColName
                           );


            SET @newvalue = ( SELECT TOP 1
                                        @ColName
                              FROM      Inserted
                              WHERE     @ColName = @ColName
                            );
            SET @oldvalue = ( SELECT TOP 1
                                        @ColName
                              FROM      Deleted
              IF @newvalue <> @oldvalue
                BEGIN

                    INSERT  INTO dbo.utl_Audit_Trail_Field
                            ( AuditTrailID ,
                              FieldID ,
                              OldValue ,
                              NewValue ,
                              CreatedDate ,
                              CreatedUserID ,
                              UpdatedDate ,
                              UpdatedUserID
                            )
                    VALUES  ( @AuditTrailID , -- AuditTrailID - bigint
                              @FieldID , -- FieldID - bigint
                              @oldvalue , -- OldValue - nvarchar(1000)
                              @newvalue , -- NewValue - nvarchar(1000)
                              @CreatedDate , -- CreatedDate - datetime
                              @CreatedUserID , -- CreatedUserID - bigint
                              @CreatedDate , -- UpdatedDate - datetime
                              @CreatedUserID  -- UpdatedUserID - bigint
                            );

                END;  

            FETCH NEXT FROM columnCursor INTO @ColName;

        END;                WHERE     @ColName = @ColName

                            );

first read this documentation about deleted and inserted Hope this helps! — Foo Bar
– Foo Bar, Commented Jun 8, 2017 at 8:47
@BogdanSahlean they don´t stop mentioning me in all tutorials, i´m a pretty good example. ;-) — Foo Bar
– Foo Bar, Commented Jun 8, 2017 at 9:51
Never ever ever (and I mean EVER) use a cursor in a trigger. Ever. Doing so is a fine way to bring an otherwise perfectly healthy RDBMS screaming to it's knees, begging for mercy. — Rachel Ambler
– Rachel Ambler, Commented Jun 8, 2017 at 9:58
If this is what you absolutely have to have then rethink your architecture. Instead of just logging individual field changes, just log the contents of the Deleted table in your audit table. If you then compress the table (preferably using Page compression) you'll not find a major impact on disk space as a result. Triggers need to be very short and sweet and have minimal impact on performance. — Rachel Ambler
– Rachel Ambler, Commented Jun 8, 2017 at 10:00
Then don't be surprised if your trigger kills the database server. Because if you try to implement this on a heavily used server your life will be an ever-lovin' misery. — Rachel Ambler
– Rachel Ambler, Commented Jun 8, 2017 at 10:04

Rachel Ambler · Accepted Answer · 2017-06-08 10:03:37Z

1

Rethink your architecture. Attempting to individually detect changes in a table and store each one separately is a recipe for disaster.

Just store the entire contents of the Deleted table in your audit table and then ensure you compress the table to cut down on the amount of space used by huge numbers of duplicates.

Disk storage space is cheap, CPU power is not.

Edit: Read your comment that you can't change the architecture; I'd say you HAVE to because the alternative is to have a trigger like this that WILL cause major problems if that table is even remotely heavily used. Even doing individual inserts outside of a cursor will bite you so badly you'll wish you'd never been born. For every ms the trigger runs, the risk of lock contention (or even worse, deadlocks) grows exponentially.

answered Jun 8, 2017 at 10:03

Rachel Ambler

1,61413 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

How to get old value and new value using SQL Trigger?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related