If I raise an error in an AFTER UPDATE trigger in Sql Server 2005, will that cause the update which caused the trigger to be fired to roll back, even if the statement was not executed within a transaction?
Thanks.
+2
A:
No, you have to rollback transaction by calling ROLLBACK TRAN:
CREATE TRIGGER trg_au_table
ON dbo.table
AFTER UPDATE
AS
BEGIN
ROLLBACK TRAN
END
GO
This example will prevent from updating any record.
This:
CREATE TRIGGER trg_au_table
ON dbo.table
AFTER UPDATE
AS
BEGIN
RAISERROR('This is a test', 16, 1)
END
GO
will only raise the error but the change will be made in the table.
Lukasz Lysik
2009-11-06 17:04:12
So, is there an implicit transaction? If I have not begun a transaction explicitly, will ROLLBACK TRAN cause any issues? Thanks!
Jordan
2009-11-06 18:50:33
Yes, there is an implicit transaction. Update and trigger are closed in one transaction. Trigger is always in one transaction with DML or DDL statement. If you perform an UPDATE you have to wait until the statements in the trigger are finished. RAISERROR does not rollback transaction.
Lukasz Lysik
2009-11-06 19:23:02