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
--***********************************************
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
--***********************************************