update trigger

[AccessSQLUser]

Right now I have a trigger that runs each time a record in Table1 is updated. This trigger basically inserts a row into a log table that I have in a separate database. What I would like to do is only insert into that table the values of the fields that were changed. Any ideas?

My current trigger looks like this:
CREATE TRIGGER [trgForUpdate] ON [dbo].[Table1]
FOR UPDATE
AS
Set NoCount On
DECLARE @MyID int
SELECT @myID = ID FROM inserted

Update Table
Set [LastUpdated] = GetDate(), [LastUpdatedBy] = SUSER_SNAME() WHERE ID = @MyID
INSERT INTO DB2.dbo.Table1_Log SELECT Table1.* FROM Table1 WHERE [ID] = @MyID

 

[tlbroadbent]

Look in SQL BOL under "Create Trigger." Read about IF UPDATE (column) and IF COLUMNS_UPDATED(). Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[kim1]

Hi,

Here is a pseudocode,

1.0 Declare the number of variables as the number of field in table
2.0 Assign for each variable the value of the field with the deleted table
3.0 for each variable
check if it is the same as in the table inserted
if different
write it in table log

next Kim