SQL Server Trigger not triggered after insert and update

11,912

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
Share:
11,912
F.P
Author by

F.P

There are two hard things in computer science: cache invalidation, naming things, and off-by-one errors.

Updated on June 04, 2022

Comments

  • F.P
    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 on AFTER INSERT and not AFTER UPDATE, but when I change it so it's triggered by UPDATE and INSERT, whenever I update any row, I get an error message from SQL Server

    Cannot 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 use AFTER 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
    Martin Smith about 10 years
    Recursive 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
    F.P about 10 years
    I tried this step by step, but after I set the NOCOUNT ON inside the trigger, it already worked.
  • TToni
    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
    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
    F.P about 10 years
    Yes I still used your recommendation, I just wanted to point out that the actual error was the NOCOUNT.