How can I nest triggers inside transaction

Question

Hi ,

I want to include alter trigger script inside a transaction block. I tried as given below but I am getting error.

BEGIN TRANSACTION

BEGIN TRY
 /****** Object:  Trigger [dbo].[TRG_Name]    Script Date: 03/02/2012 21:22:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[TRG_Name]
ON [dbo].[INV_CONTAINERS]
FOR INSERT
AS
----PERFORM ALL THE ACTIONS HERE
COMMIT TRAN
 
END

 TRY
 
BEGIN

 CATCH
 
IF @@TRANCOUNT > 0
ROLLBACK TRAN

-- LOG THE EXCEPTION HERE

DECLARE @msg VARCHAR(500)

SELECT @msg = ERROR_MESSAGE()

-- PASS THE EXCEPTION TO THE OUTER METHOD/SP

RAISERROR('Error in trigger! Message: %s', 16,1,@msg)
 
-- you could actually use the same ERROR_NUMBER() AND SEVERTY. I put 16,1 for simplicity
 
END

 CATCH
 

Error

------------------------------------------------------------

Msg 102, Level 15, State 1, Line 13

Incorrect syntax near 'ON'.

Msg 102, Level 15, State 1, Procedure TRG_Name, Line 13

Incorrect syntax near 'TRY'.

Msg 102, Level 15, State 1, Procedure TRG_Name, Line 42

Incorrect syntax near 'CATCH'.

Could you please help me to solve this issue.

Regards,

Vidya

Answer 1

create table t1 (c int)


insert into t1 values (10)


create trigger my_tr on t1 for insert 
as
select * from inserted


begin tran
begin try
exec('alter trigger my_tr on t1 for update
as
select * from deleted')
commit tran
end try
begin catch
select ERROR_NUMBER() AS ER_Num
end catch;

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Hi,

Thanks for your reply.

As per your code the transaction block is the part of alter trigger script. But I need it differently.

I am including some other logic also inside the transaction block. eg:Inserting values to table, altering 2 more triggers etc. If something goes wrong I have to revert all these changes. That is the reason for trying the transaction block outside the alter trigger statement. Is this possible to implement in sql server 2008?

Regards,

Vidya

Answer 3

No problem

begin tran
begin try
exec('alter trigger my_tr on t1 for update
as
select * from deleted')
insert into t1 select 1
delete from t1


commit tran
end try
begin catch
select ERROR_NUMBER() AS ER_Num
end catch;

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

Hi,

Thanks.... Its working fine :)

Regards,

Vidya