The below code is sample code for auditing UPDATE operations to the Log/History table
-- AFTER UPDATE Trigger example
Create table StudentScores (
Name varchar(20),
Score numeric(18,2)
)
GO
Create table StudentScoresHist (
Name varchar(20)
, Score numeric(18,2)
, HistoryDate datetime
)
GO
insert into StudentScores values
('John', 75) ,
('Mary', 75) ,
('Steve', 75) ,
('Sue', 75)
GO
select 'Current' as Origin, Name, Score from StudentScores
union all
select 'History' as Origin, Name, Score from StudentScoresHist
GO
create TRIGGER StudentScoresAfterUpdate
ON StudentScores
AFTER UPDATE
AS
IF ( UPDATE (Name) OR UPDATE (Score) )
BEGIN
INSERT INTO StudentScoresHist (Name, Score, HistoryDate)
select Name, Score, getdate()
from deleted ;
END;
GO
Update StudentScores set Score = 85 where Name = 'John'
GO
select 'Current' as Origin, Name, Score from StudentScores
union all
select 'History' as Origin, Name, Score from StudentScoresHist
GO
DROP TABLE StudentScores;
DROP TABLE StudentScoresHist;