Search code examples
sql-servert-sqltriggers

How to roll back the changes if trigger failed

I have two table. The Invoices table and the Outputs table. I want to update table Outputs after the Invoices table has been updated. I want to roll back Invoices changes if Outputs update fails.

ALTER TRIGGER [dbo].[Invoices_InputUpdate]
ON [dbo].[Invoices]
AFTER UPDATE
AS
BEGIN
 DECLARE @InvoiceId INT;
 SELECT @InvoiceId = InvoiceId From inserted
 UPDATE Outputs SET InvoiceModified=1 WHERE InvoiceId = @InvoiceId 
END
Solution

  • AFTER triggers take place after data is written to the table and are a distinct and separate set of operations that execute as part of the same transaction that wrote to the table, but after that write occurs. If the trigger fails, the original operation also fails.

    The code will work fine for a single-row update. However, It fail for a multi-row update. As @GuidoG states in the comments, we can avoid this failure by the following code:

    ALTER TRIGGER [dbo].[Invoices_InputUpdate]
ON [dbo].[Invoices]
AFTER UPDATE
AS
BEGIN
 UPDATE o SET InvoiceModified=1 
 FROM Outputs AS o
 INNER JOIN inserted i on o.InvoiceID = i.InvoiceID
END

    As I said, if the trigger fails, the original operation also fails and the transaction will rollback.