CODEWITHSUNDEEP
sql-serverstored-procedurestriggers
mrivanlima
mrivanlima

Reputation: 551

What if my trigger fails after an update inside a stored procedure?

If I have an update inside a stored procedure in SQL Server and the stored procedure runs successfully, however, the trigger for that update fails for whatever reason, will my update be rolled back?

Upvotes: 0

Views: 867

Answers (1)

squillman
squillman

Reputation: 13641

Yes, if a rollback or any runtime error happens in your trigger it will rollback the specific (or implied) transaction above it.

To see this happen on an update such as you are asking about, run this:

DROP TABLE IF EXISTS dbo.MyTable;
DROP TABLE IF EXISTS dbo.OtherTable;
GO

CREATE TABLE dbo.MyTable (ID INT);
GO
CREATE TABLE dbo.OtherTable (ID INT);
GO

CREATE TRIGGER dbo.UpdateTrigger
ON dbo.MyTable
FOR  UPDATE
AS 
BEGIN
      INSERT dbo.OtherTable (ID) VALUES ('Blah');
END;
GO

INSERT dbo.MyTable (ID) VALUES (1);
GO

UPDATE dbo.MyTable SET ID = 2;
GO

SELECT ID FROM dbo.MyTable;
SELECT ID FROM dbo.OtherTable;

You'll see that the value of ID in MyTable is still 1 and OtherTable is still empty because the trigger threw an error during the UPDATE.

Even if you set XACT_ABORT OFF in the trigger and in the update batch the update will still rollback.

DROP TABLE IF EXISTS dbo.MyTable;
DROP TABLE IF EXISTS dbo.OtherTable;
GO

CREATE TABLE dbo.MyTable (ID INT);
GO
CREATE TABLE dbo.OtherTable (ID INT);
GO

CREATE TRIGGER dbo.UpdateTrigger
ON dbo.MyTable
FOR  UPDATE
AS 
BEGIN
      SET XACT_ABORT OFF;
      INSERT dbo.OtherTable (ID) VALUES ('Blah');
END;
GO

INSERT dbo.MyTable (ID) VALUES (1);
GO

SET XACT_ABORT OFF;
UPDATE dbo.MyTable SET ID = 2;
GO

SELECT ID FROM dbo.MyTable;
SELECT ID FROM dbo.OtherTable;

If you want to continue with the UPDATE even if the trigger fails then you should use a TRY/CATCH in your trigger to handle the runtime error and allow the trigger to complete.

Upvotes: 2

Related Questions