Answered by:
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 TRANSACTIONBEGIN TRY
/****** Object: Trigger [dbo].[TRG_Name] Script Date: 03/02/2012 21:22:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER TRIGGER [dbo].[TRG_Name]
ON [dbo].[INV_CONTAINERS]
FOR INSERT
AS
----PERFORM ALL THE ACTIONS HERE
COMMIT TRAN
ENDTRY
BEGINCATCH
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
ENDCATCH
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
- Edited by Vidya Panthalil Sunday, March 4, 2012 10:21 AM
Sunday, March 4, 2012 10:03 AM
Answers
-
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/
- Marked as answer by Vidya Panthalil Sunday, March 4, 2012 12:06 PM
Sunday, March 4, 2012 11:43 AMAnswerer
All replies
-
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/
Sunday, March 4, 2012 10:39 AMAnswerer -
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
Sunday, March 4, 2012 11:11 AM -
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/
- Marked as answer by Vidya Panthalil Sunday, March 4, 2012 12:06 PM
Sunday, March 4, 2012 11:43 AMAnswerer -
Hi,
Thanks.... Its working fine :)
Regards,
Vidya
- Edited by Vidya Panthalil Sunday, March 4, 2012 12:06 PM
Sunday, March 4, 2012 12:06 PM