AFTER UPDATE Trigger checking for specific column update

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 215
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;

Comments or Responses

Login to post response