SQL Server Trigger not triggered after insert and update
If you run this trigger as an AFTER UPDATE
trigger, it runs recursively, since it always issues another UPDATE statement against the same table, which leads to another execution of the trigger.
To work around this, you either need to make the update trigger an INSTEAD OF UPDATE
trigger or test if the KENNSCHT
column was modified at all. For the latter you can use the UPDATE()
function like this:
ALTER TRIGGER [dbo].[kennscht_copy_to_prodverpt_after_update]
ON [dbo].[Stammdaten]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
IF (UPDATE(KENNSCHT))
BEGIN
UPDATE s
SET s.PRODVERPT = i.KENNSCHT
FROM Stammdaten s
INNER JOIN INSERTED i ON i.SNR = s.SNR
END
END
F.P
There are two hard things in computer science: cache invalidation, naming things, and off-by-one errors.
Updated on June 04, 2022Comments
-
F.P over 1 year
I want to copy the contents of a column into another column in the same table. Therefore, I created this trigger:
ALTER TRIGGER [dbo].[kennscht_copy_to_prodverpt] ON [dbo].[Stammdaten] AFTER INSERT AS UPDATE Stammdaten SET PRODVERPT = (SELECT KENNSCHT FROM INSERTED) WHERE SNR = (SELECT SNR FROM INSERTED);
But when I use an UPDATE on the table to update
KENNSCHT
to a different value,PRODVERPT
is not updated as well. Now, you could argue that is because the trigger is onAFTER INSERT
and notAFTER UPDATE
, but when I change it so it's triggered byUPDATE
andINSERT
, whenever I update any row, I get an error message from SQL ServerCannot update row because it would make the row not unique or update multiple rows (2 rows) [sic]
What is going on here? Either the trigger doesn't do anything, or it's messing up the whole table, making it un-updateable.
Update: I also tried the following trigger:
UPDATE s SET s.PRODVERPT = i.KENNSCHT FROM Stammdaten s INNER JOIN INSERTED i ON i.SNR = s.SNR;
But it has exactly the same behaviour. If I use only
AFTER INSERT
, nothing changes and if I useAFTER INSERT, UPDATE
I get the error message above.Update 2: There are no multiple rows in the table, I already checked that because I thought it might be connected to the issue.
-
Martin Smith about 10 yearsRecursive triggers aren't enabled by default and if that was the issue they would get a recursion limit exceeded error. The error mentioned in the question is one from the client tools when
NOCOUNT ON
isn't on. -
F.P about 10 yearsI tried this step by step, but after I set the
NOCOUNT ON
inside the trigger, it already worked. -
TToni about 10 years@Florian Pescka: Still I think it would be a good idea to use my code. It only issues another update if needed (not always, like the original code), and it is more robust because it still works if recursive triggers get enabled.
-
TToni about 10 years@Martin Smith: Thanks for the heads-up! I'll let the answer stand with this comment, it's already upvoted and I'll add another +1 to it.
-
F.P about 10 yearsYes I still used your recommendation, I just wanted to point out that the actual error was the NOCOUNT.