Compare deleted and inserted table in SQL Server 2

Question

I am new to SQL Server 2008 and I need advice from all of you. I want to find out the changed value in inserted and deleted tables of the SQL Server 2008 for a table because I am currently doing the audit trail to keep the old and new value. How can I loop all the column to find out which field's value change from the deleted and inserted table? I had tried the if else statement to compare

For example:

create trigger trg_XXX on dbo.table
after update
as
begin
    declare 
       @oldID varchar(6),
       @newID varchar(6)

    select @oldID = ID from deleted
    select @newID = ID from inserted

    if(@oldID != @newID)
       insert into table (a, b) values (@oldID, @newID)
    else
       print('do nothing')

Is there a way to do using cursor to loop through the deleted and inserted table or any alternative way? Can give me some example?

Answer 1

I think you are looking for what unofficially called magic tables in SQL server.

The tables "INSERTED" and "DELETED" are called magic tables of the SQL Server. We can not see these tables in the data base. But we can access these tables from the "TRIGGER"

When we insert the record into the table, the magic table "INSERTED" will be created In that table the current inserted row will be available. We can access this record in the "TRIGGER".

When we update a record on the table where trigger is created, the magic tables "INSERTED" and "DELETED" both will be created, the Old data of the updating record will be available in "DELETED" table and, the new data will be available in "INSERTED" table, while accessing them inside the trigger.

When we delete the record from the table, the magic table "DELETED" will be created In that table the current deleted row will be available. We can access this record in the "TRIGGER".

Example:

Following code Explains the magic table "INSERTED":

CREATE TRIGGER LogMessage
ON EMP
FOR INSERT
AS
   DECLARE @EMPNAME varchar(50)
   SELECT @EMPNAME= (SELECT EMPNAME FROM INSERTED)
   INSERT INTO LOGTABLE(UserId,Message) values (@EMPNAME,'Record Added')
GO

Following code Explain the magic table "DELETED"

CREATE TRIGGER LogMessage
ON EMP
FOR DELETE
AS
   DECLARE @EMPNAME varchar(50)
   SELECT @EMPNAME= (SELECT EMPNAME FROM DELETED)
   INSERT INTO LOGTABLE(UserId,Message) values (@EMPNAME,'Record Removed')
GO

Sources(All credit to these articles & authors):

http://www.codeproject.com/Questions/285423/what-is-magic-table-different-types-of-magic-table http://www.dotnetspider.com/resources/29332-Magic-tables-SQL-Server.aspx

. .

Alternatively

, you can try: Obtaining Changes by Using the Change Tracking Functions, its MSDN link to explain how to track changes by using in-built functions.

CHANGETABLE(CHANGES …) function

This rowset function is used to query for change information. The function queries the data stored in the internal change tracking tables. The function returns a results set that contains the primary keys of rows that have changed together with other change information such as the operation, columns updated and version for the row.

CHANGE_TRACKING_CURRENT_VERSION() function

Is used to obtain the current version that will be used the next time when querying changes. This version represents the version of the last committed transaction.

CHANGE_TRACKING_MIN_VALID_VERSION()function

Is used to obtain the minimum valid version that a client can have and still obtain valid results from CHANGETABLE(). The client should check the last synchronization version against the value thatis returned by this function. If the last synchronization version is less than the version returned by this function, the client will be unable to obtain valid results from CHANGETABLE() and will have to reinitialize.

Refer syntax & usage at http://technet.microsoft.com/en-us/library/cc280358%28v=sql.105%29.aspx

Hope it helps.

Answer 2

You could make a dynamic "comparer" by converting the inserted and deleted tables into 2 xml, iterating through the properties and comparing the xml value.

Example:

CREATE TRIGGER MY_COMPARER
   ON TABLE_NAME
   AFTER UPDATE
AS
BEGIN
    DECLARE @columnIndex INT = 1;
    DECLARE @maxColumns INT = (select count(*) from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLE_NAME');
    DECLARE @insertedXML XML = (select top 1 * from INSERTED FOR XML PATH('ROOT'), ELEMENTS XSINIL);
    DECLARE @deletedXML XML = (select top 1 * from DELETED FOR XML PATH('ROOT'), ELEMENTS XSINIL);

    WHILE @columnIndex <= @maxColumns BEGIN 
        DECLARE @insertedXMLValue XML = (select @insertedXML.query('/ROOT/*[sql:variable("@columnIndex")]'));
        DECLARE @deletedXMLValue XML = (select @deletedXML.query('/ROOT/*[sql:variable("@columnIndex")]'));

        DECLARE @insertedValue NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @insertedXMLProp);
        DECLARE @deletedValue NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @deletedXMLProp);

        IF (@insertedValue != @deletedValue)
            print('Column: ' + CONVERT(NVARCHAR(MAX), @columnIndex) + ' has changed')

        SET @columnIndex = @columnIndex + 1;
    END
END

Answer 3

First: do NOT use a cursor inside a trigger - EVER!

Second: to identify what fields were included in the update statement you can use: UPDATE() or COLUMNS_UPDATED()

Note: This does NOT list the fields that have had their value changed, just the list of columns included in the SET portion of the UPDATE statement.

Third: There are a variety of methods that you can use to audit changes to your tables (the accepted answer on Most efficient method to detect column change in MS SQL Server has a good list and guidelines on how to use, if you are using SQL Server Enterprise you could also look at using Change Data Capture

Some example audit code I would use (where I want to record a column by column audit table):

INSERT INTO AuditTable (ColumnChanged, OldValue, NewValue) /* I assume there are default columns logging who/when the change was done by? */
    SELECT 'ColumnA' as ColumnChanged, d.ColumnA, i.ColumnA
    FROM inserted i join deleted d ON d.PKID = i.PKID
    WHERE 
        /* both aren't null and the value has changed */
        (d.ColumnA IS NOT NULL AND i.ColumnA IS NOT NULL AND d.ColumnA != i.ColumnA) 
        /* it was null and now it isn't */
        OR (d.ColumnA IS NULL AND i.ColumnA IS NOT NULL) 
        /* it wasn't null and now it is */
        OR (d.ColumnA IS NOT NULL AND i.ColumnA IS NULL)
UNION 
    SELECT 'ColumnB' as ColumnChanged, d.ColumnB, i.ColumnB
    FROM inserted i join deleted d ON d.PKID = i.PKID
    WHERE 
        /* both aren't null and the value has changed */
        (d.ColumnB IS NOT NULL AND i.ColumnB IS NOT NULL AND d.ColumnB != i.ColumnB) 
        /* it was null and now it isn't */
        OR (d.ColumnB IS NULL AND i.ColumnB IS NOT NULL) 
        /* it wasn't null and now it is */
        OR (d.ColumnB IS NOT NULL AND i.ColumnB IS NULL)
....  /* continuing for each column */

It would be simpler (from a sql perspective and faster [due to less writes] to audit by row) ie:

INSERT INTO AuditTable (OldValueA, NewValueA, OldValueB, NewValueB) 
SELECT d.ColumnA, i.ColumnA, d.ColumnB, i.ColumnB
FROM inserted i join deleted d ON d.PKID = i.PKID
WHERE 
/* same check for columnA */
    /* both aren't null and the value has changed */
    (d.ColumnA IS NOT NULL AND i.ColumnA IS NOT NULL AND d.ColumnA != i.ColumnA) 
    /* it was null and now it isn't */
    OR (d.ColumnA IS NULL AND i.ColumnA IS NOT NULL) 
    /* it wasn't null and now it is */
    OR (d.ColumnA IS NOT NULL AND i.ColumnA IS NULL)
/* now check columnB */
    (d.ColumnB IS NOT NULL AND i.ColumnB IS NOT NULL AND d.ColumnB != i.ColumnB) 
    OR (d.ColumnB IS NULL AND i.ColumnB IS NOT NULL) 
    OR (d.ColumnB IS NOT NULL AND i.ColumnB IS NULL)
....  /* continuing for each column */

Answer 4

I'm not quite sure, what your goal ist, i think it might be something like this. Let's say we have a table like this:

CREATE TABLE Product
(
     ID      INT                   PRIMARY KEY,
     Name    NVARCHAR(100)         NOT NULL,
     Price   DECIMAL(10,2)         NOT NULL
);

and some audit table like this:

CREATE TABLE ProductAudit
(
     AuditID      INT                   IDENTITY PRIMARY KEY, 
     ProductID    INT                   NOT NULL
     OldName      NVARCHAR(100)         NULL,
     OldPrice     DECIMAL(10,2)         NULL,
     NewName      NVARCHAR(100)         NULL,
     NewPrice     DECIMAL(10,2)         NULL
);

Then you create a trigger:

CREATE TRIGGER TR_AUDIT
ON Product
FOR INSERT, UPDATE, DELETE
AS
BEGIN
       INSERT INTO ProductAudit (ProductID, OldName, OldPrice, NewName, NewPrice)
       SELECT 
           COALESCE(I.ID, D.ID),
           D.Name,
           D.Price,
           I.Name,
           I.Price
       FROM 
           INSERTED I FULL OUTER JOIN DELETED D ON I.ID = D.ID;
END
GO

There you have it.