none
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




    Sunday, March 04, 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/

    Sunday, March 04, 2012 11:43 AM

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 04, 2012 10:39 AM
  • 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 04, 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/

    Sunday, March 04, 2012 11:43 AM
  • Hi,

    Thanks.... Its working fine :)

    Regards,

    Vidya


    Sunday, March 04, 2012 12:06 PM