April 27, 2018 at 10:32 am
I have the following trigger and upon update of a column , the values are getting inserted twice in a log table . Why?
Create trigger trigTest1 on test1
After update
as
Begin
set nocount on ;
declare @id int
Declare @ColName varchar(30)
Declare @ColValue varchar(30)
Declare @PrevValue varchar(30)
Declare @username varchar(30)
select @username = suser_sname()
if update(Name)
select @id = inserted.idNumber from inserted
select @ColName = 'Name'
select @ColValue = inserted.Name from inserted
select @PrevValue = deleted.namefrom deleted
end
insert into Logtable (key,ColName,CurrVal,PrevVal, username)
select @id as 'key' ,@ColName as 'ColName' ,@ColValue as 'currVal' ,@PrevValue as 'preval',@username
GO
script I ran
update Test1
set name = 'John'
where IDNumber = 1
Results-
Key,ColName,CurrVal,PrevVal
null,Name,John,John,userA
1,Name,John,Jones,UserA
The second row is correct, but I am not sure why are we getting the first row as well .
April 27, 2018 at 11:05 am
No idea why it's getting in the log table twice (that trigger CANNOT insert two rows, so it must have fired twice), but that trigger is flawed.
A trigger fires once for all rows that are updated, your trigger assumes that there's only one row in the inserted/deleted tables. If you ever update more than one row in a single update, the trigger will put one of the updated rows into the log table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2018 at 11:11 am
Something like this would work better for multiple row updates. It's still not ideal, as you don't know when the change was made, and it will only work if the ID number is not changed in the same update.
CREATE TRIGGER trigTest1
ON test1
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(name)
INSERT INTO Logtable
(
,
ColName,
CurrVal,
PrevVal,
username
)
SELECT inserted.idNumber AS rowkey,
'Name' AS ColName,
deleted.name AS PrevValue,
inserted.Name AS CurrValue,
SUSER_SNAME() AS CurrUser
FROM inserted
INNER JOIN deleted
ON inserted.idnumber = deleted.idnumber;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply