January 10, 2005 at 6:36 am
I am fixing a trigger (which I did not write, and the person who did is no longer here). In this trigger, I have a condition in which I would like for the trigger to raise and error and exit the delete. This is done before any transactions are started. The RETURN statement seems to just exit that if statement, but will continue to execute the rest of the trigger. Can anyone help me figure out why? I know I could use a GOTO, but I would like to avoid that at all costs.
Below is parts of the trigger.
Thanks,
Michael
CREATE trigger del_on_property on property
FOR DELETE
AS
-- This trigger was generated by _generate_z_triggers on 2002-10-16
/*** Make sure there are deleted rows ***/
IF @@rowcount = 0
RETURN
declare @dbo_bypass bit
exec usp_dbo_bypass_read @dbo_bypass output
-- Forbid deletes on property when holder_report is closed.
IF @dbo_bypass=0 and EXISTS(select *
FROM deleted d, property p, holder_report hr
WHERE p.property_id = d.property_id
AND hr.holder_report_id = p.holder_report_id
AND hr.closed_status_indicator = 1)
BEGIN
RAISERROR('Property may not be deleted when it is on a closed holder report. (del_on_property)', 16, -1)
RETURN
END
-- Forbid deletes on property when involved in a claim (Added by MRE on 1/7/05)
IF @dbo_bypass=0 and EXISTS(select *
FROM deleted d, property p, claim c
WHERE p.property_id = d.property_id
AND c.property_id = p.property_id
AND c.approval >= 1)
BEGIN
RAISERROR('Property may not be deleted when it is involved in a claim. (del_on_property)', 16, -1)
RETURN
END
SET XACT_ABORT ON
BEGIN TRANSACTION
-- insert after images into audit trail
-- Maintain Holder Report Totals
COMMIT TRANSACTION
January 10, 2005 at 7:47 am
A tranaction was created by the initial delete statement and the trigger will be part of that.
BEGIN
RAISERROR('Property may not be deleted when it is on a closed holder report. (del_on_property)', 16, -1)
ROLLBACK TRANSACTION
RETURN
END
Watch out for that Begin tran, commit at the end. If that fails and rolls back, the entire transaction, including the original delete will be rolled back.
not sure why the return isn't returning. I'll do some experimentation in the morning.
HTH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2005 at 10:01 am
Correct, when you get to a trigger, you are always inside a transaction. It may be the single insert/update/delete statement or it may be inside another transaciton, but one always exists.
I'd use the rollback if you want the statement to be stopped. If you just want part of the trigger to not fire, but the delete to occur, wrap it in an IF statement.
January 10, 2005 at 10:51 am
When I do what was suggested, I get the old "You can't rollback a transaction without a begin transaction" error...any more suggestions?
Thanks,
Michael
January 10, 2005 at 2:45 pm
How are you testing this ?
Can you Comment out the Last part ... SET XACT_ABORT ON till the end ?
* Noel
January 11, 2005 at 4:31 pm
I can't. I did not write the trigger originally...I am just modifying it.
January 11, 2005 at 5:34 pm
Instead of having multiple RETURNs in the trigger, why not rewrite the trigger and structure it so that if an error condition is encountered the trigger bypasses the rest of the code and hits a single RETURN at the bottom of the code?
If <Not ErrorCondition1>
Begin
Declare...
Exec...
If <ErrorCondition2>
RaisError
Else
Begin
If <ErrorCondition3>
RaisError
Else
Begin
Set XACT_ABORT_ON
Begin Transaction
Processing Statements
Commit Transaction
End
End
End
Return
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply