locked
AFTER INSERT and AFTER UPDATE triggers on same table RRS feed

  • Question

  •  Hello,

    I have a table "T" and 2 triggers on that table: TR1 and TR2. TR1 is an after insert trigger and TR2 is an after update trigger. The problem is that when TR1 executes an update on column "x" of table "T" TR2 is not fired. Could somebody give me any clues why this is happening and some possible solution?


    Thanks in advance.


    Friday, January 23, 2009 9:42 AM

All replies

  • Is the trigger enabled?

    Can you knock up an example that recreates the problem, or post your create trigger statement for TR2 at the very least?


    George
    Friday, January 23, 2009 9:52 AM
    Answerer
  • It is enabled, I've tried to update that column (so only TR2 is fired) and it works. 

     This is TR1 (I have modified it so it looks clearer, but the trigger's logic is the same)

     

    ALTER TRIGGER dbo.TramitarFaseEnAlta 
       ON  dbo.T 
       AFTER INSERT 
    AS  
        DECLARE @key int 
            DECLARE @SOMEAT BIT 
    BEGIN 
        -- SET NOCOUNT ON added to prevent extra result sets from 
        -- interfering with SELECT statements. 
        SET NOCOUNT ON
         
        SELECT @key = key 
        FROM inserted 
         
        SELECT @SOMEAT = SOMEAT  
        FROM T2 
        WHERE T2.KEY = @KEY 
     
        IF @SOMEAT= 'true' 
        BEGIN 
            UPDATE T 
            SET X= getdate() 
            WHERE key = @key  
        END 
     
    END 
    GO 

     

    This is TR2 (I have modified it so it looks clearer ,but the trigger's logic is the same) .

     

     
    CREATE TRIGGER dbo.AbreSigFase  
       ON  dbo.T 
       AFTER UPDATE 
    AS  
        DECLARE @key int 
    BEGIN 
        -- SET NOCOUNT ON added to prevent extra result sets from 
        -- interfering with SELECT statements. 
        SET NOCOUNT ON
         
        SELECT @key = key 
        FROM inserted 
     
            @key = key + 1 
     
        IF UPDATE(x) 
        BEGIN 
            UPDATE T 
            SET x = getDate() 
            WHERE T.key = @key 
        END 
    END 
    GO 

    Friday, January 23, 2009 10:17 AM
  • Try enabling NESTED TRIGGERS by running the following

    SP_CONFIGURE 'nested_triggers',1
    GO
    RECONFIGURE
    GO


    http://jacobsebastian.blogspot.com/
    Friday, January 23, 2009 10:48 AM
  • Ffirst thing I notice is that these triggers are only programmed to handle single entries / updates, which is not cool!


    George
    Friday, January 23, 2009 10:51 AM
    Answerer
  • NESTED TRIGGERS are already enabled.

    Anyway, any suggestions for coding that kind of trigger combination? To sum up, I need to insert a record on a table, then , if certain condition is satisfied, change an attribute "X" on that record . Also,when an attribute "X" is updated some process has to be carried out.

      Thanks in advance

     

    • Proposed as answer by FriedTyGuy Thursday, June 26, 2014 8:42 PM
    Friday, January 23, 2009 12:49 PM
  • Why wouldn't you just check the condition in the first trigger and execute the update if required?
    Thursday, June 26, 2014 8:42 PM
  • What is the reason using triggers?  Can you apply stored procedures?

    Here is the point: even a single trigger can be challenging. Who is going to maintain double triggers once you leave the company?


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Tuesday, July 8, 2014 11:20 PM