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.