Rollback and Enable Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Rollback and Enable Trigger

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2008-10-27 : 16:17:12

hi

I have an update StoreProc.

ALTER PROCEDURE [dbo].[spA]
AS
BEGIN
SET NOCOUNT ON;
exec spDisableTrg
Update dbo.tblA
set CLDNO = B.IND_CLDNO,
from tblA, tblB B
where tblA.CLDNO = B.IND_CLDNO
if @@error <> 0
exec spenableTrg
else
delete from tblB
exec spenableTrg
END

Is there anyway that i could rollback and at the same time enable the trigger if @@error <> 0? I am not able to have both statement to run at the same time. Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-10-27 : 16:35:31

try using the try catch construct and reenable the trigger at the end of try or in the catch part.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources