Compare deleted and inserted table in SQL Server 2008

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'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.

Answer 2

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.