I'm struggling with understanding how a SP ROLLBACK will happen when a trigger fails.
If in the calling SP is wrapped in a transaction and a simple SQL call is made that envokes a trigger - but the trigger fails - will the calling object be aware that the trigger failed or do I have to build logic into the trigger to pass back to the calling SP and catch it to determine if it succeeded or not?
Also; do I have to wrap the Trigger in a BEGIN TRAN and rollback if it fails - or does the calling object take care of that if it rolls back?
I'll try to give a code sample below:
--Sample Calling Object--
Code:
Declare @rowsaffected as int
BEGIN TRAN p_accdt_d
UPDATE accdt
SET accdt_del_date = '2/1/2004'
WHERE accdt_enfact_id in (6220, 6221)
-- there is an UPDATE trigger on the accdt_del_date column
-- the trigger marks some other related records with the current date
-- 1st question is if an invalid date is passed and the trigger fails
-- on some tables, but the last update succeeds, therfore my @rowsaffected
-- will have a value making this transaction think it succeeded
-- 2nd question - The @rowsaffected below seems to return how many rows in
-- the trigger not the UPDATE above
-- return rowcount of records updated.
SELECT @rowsaffected = @@rowcount
Print @rowsaffected
IF @rowsaffected = 0
BEGIN
ROLLBACK TRAN p_accdt_d
END
ELSE
BEGIN
COMMIT TRAN p_accdt_d
END
_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200
Triggers
