Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Insert and Update with Rollback

Status
Not open for further replies.

Mdavis123

Programmer
Nov 12, 2001
56
US
Is this the proper placement for the Begin/Commit/Rollback for following Insert code snip which also does an update to the same table. In the Update trigger, I have the same Begin/Commit Structure wrapped around the 2 updates it does.
I'm concerned about this second Begin/Commit Block in the Update. Do I need to do separate track transactions to do Rollbacks?

Everything should rollback on the Insert if anything fails.

Thanks In advance Mike Davis

In the code snip below Checkforerrors is really
If @@Error>0
Begin
Set @TransErr=@@Error
RaiseError('ProcName error and description',16,1)
Goto TransError_exit
End



CREATE TRIGGER [InsertSchForGrpCycle] ON [dbo].[FD__GRPCYCLES]
FOR INSERT
AS

Declare @NewApptkey int
Declare @Docid int
-- Get some info from the Inserted Row
Select @Docid =Docid from Inserted

Begin Transaction
Exec GetNewApptKey @Docid , @NewApptkey output
Insert Intotable2....Values(...)
-- CheckforErrors

Insert intoTable3 ...Values(...)
-- CheckforErrors
--Update the original
Update FD__GRPCYCLES Set ApptKey=@NewApptkey
-- CheckforErrors


TransError_exit:
if TransErr=0
Commit Transaction
else
Rollback
Return
--***********************************************
CREATE TRIGGER [InsertSchForGrpCycle] ON [dbo].[FD__GRPCYCLES]
FOR Update
AS
Declare @Apptkey int
Declare @Docid int
-- Get some info from the Inserted Row
Select @Docid =Docid, @Apptkey =Apptkey from Inserted

Begin
Update IntoTable2 Set...
-- CheckforErrors
Update IntoTable3 Set
-- CheckforErrors

TransError_exit:
if TransErr=0
Commit Transaction
else
Rollback
Return
--***********************************************


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top