Robert (10/14/2008)
Good article. I have a small correction. If the after trigger fails (or raises an exception), it does not fail the transaction. The client must handle it.A well behaved client has code like this:
start transaction
try
update/insert/delete/sp
commit
except
rollback;
other actions like inform the user
end;
More often clients rely on autocommit and since the error condition happens after action, it's committed anyway. You can check this in management studio: put a raiserror in a trigger, execute update without BEGIN TRANSACTION, you have nothing to rollback and updates are in place.
Robert,
Thanks for the comment. I believe you are incorrect in stating that the transaction will not fail on an error with the trigger. An error in a trigger will cause a rollback in my experience. Your are correct in stating that the client application should handle it, and then you can re-submit a corrected transaction, but left to itself the data modification is rolled back either explicitly like in your code, or implicitly. The trigger takes place WITHIN the outer transaction whether explicit or implicit. For example with this trigger in the AdventureWorks database:
[font="Courier New"]ALTER TRIGGER [Person].[uContact] ON [Person].[Contact]
AFTER UPDATE NOT FOR REPLICATION
AS
BEGIN
SET NOCOUNT ON;
UPDATE [Person].[Contact]
SET [Person].[Contact].[ModifiedDate] = GETDATE()
FROM
#inserted -- non-existent table used intentionally
WHERE
inserted.[ContactID] = [Person].[Contact].[ContactID];
END;[/font]
If you run this code with explicit transactions:
[font="Courier New"]-- row before update
SELECT
'Last Name Before Update' AS TYPE,
LastName
FROM
person.contact
WHERE
ContactID = 12
GO
BEGIN Try
BEGIN TRANSACTION
UPDATE [AdventureWorks].[Person].[Contact]
SET [LastName] = 'Changed'
WHERE
ContactID = 12
-- this will never be hit due to error in trigger
COMMIT TRANSACTION
SELECT 'After Commit'
END Try
BEGIN Catch
SELECT 'Error in Trigger' AS error, Error_Message() AS MESSAGE
-- if the explicit rollback is not entered another error is raised
-- and the transcation is rolled back
--RollBack Transaction
END Catch
GO
-- row not updated
SELECT
'Last Name After Failed Update due to Trigger Error' AS TYPE,
LastName
FROM
person.contact
WHERE
ContactID = 12[/font]
or this code with implicit transaction:
[font="Courier New"]-- row before update
SELECT
'Last Name Before Update' AS TYPE,
LastName
FROM
person.contact
WHERE
ContactID = 12
GO
BEGIN Try
UPDATE [AdventureWorks].[Person].[Contact]
SET [LastName] = 'Changed'
WHERE
ContactID = 12
-- this will never be hit due to error in trigger
SELECT 'After Commit'
END Try
BEGIN Catch
SELECT 'Error in Trigger' AS error, Error_Message() AS MESSAGE
END Catch
GO
-- row not updated
SELECT
'Last Name After Failed Update due to Trigger Error' AS TYPE,
LastName
FROM
person.contact
WHERE
ContactID = 12
[/font]
The end result is the same. The LastName for ContactID 12 is not updated.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question