SQL Server After Insert trigger does not perform actions

Question

I have a problem with 2 After Insert triggers and 1 Instead Of Insert trigger. The After Insert triggers should perform some actions but that actions are not fired.

When the Instead Of trigger fires, their actions the After Triggers works fine.

Let me explain:

The Instead Of trigger validates if the ID value is Null. If it is, then the trigger performs the insert action in the table (TRADE_APPR) filling the ID value from a SQL Server sequence.

When the Instead Of trigger performs the actions, the After Insert triggers also perform their actions.

The code of my Instead Of Insert trigger is this:

ALTER TRIGGER [ConfirmMgr].[TG_TRADE_APPR_BER_I] 
ON [ConfirmMgr].TRADE_APPR] 
INSTEAD OF INSERT
AS
   DECLARE
      @user_name            varchar(50),
      @appr_username        varchar(50),
      @appr_id          int,
      @v_id             int,
      @appr_timestamp       datetime
BEGIN 
    SELECT @appr_username = APPR_BY_USERNAME FROM inserted

    IF (@appr_username) IS NULL
    BEGIN
            SELECT @user_name = SUSER_NAME()
            UPDATE tbl SET APPR_BY_USERNAME = UPPER(@user_name)
            FROM ConfirmMgr.TRADE_APPR tbl
            INNER JOIN inserted i ON tbl.ID = i.ID
    END

    SELECT @appr_id = i.ID FROM inserted i

    IF (@appr_id) IS NULL
    BEGIN
        INSERT ConfirmMgr.TRADE_APPR
            SELECT 
                NEXT VALUE FOR ConfirmMgr.SEQ_TRADE_APPR,
                i.TRADE_ID,
                i.APPR_FLAG,
                ISNULL(i.APPR_TIMESTAMP_GMT,GETDATE()),
                ISNULL(i.APPR_BY_USERNAME, UPPER(SUSER_NAME()))
            FROM inserted i         
        END
    RETURN          
END

As you can check in my code this instead of trigger validated If the user_name or ID are Null perform the actions.

When you send a null value the trigger works fine and the After triggers too.

The point is when you send the right values included the ID, the insert action do not save the record, because this the record is not inserted and the After Insert trigger don't perform their actions.

I understand that you send the right values, the triggers like this Instead of should not perform or block something.

I did a test putting disable the Instead Of Insert trigger and the record was saved and the After Insert triggers work fine.

My question is: why does the Instead Of Insert trigger affect the insert actions when you send the rights values?

Below is my code in one of my After Insert triggers

Thanks for your advice

ALTER TRIGGER ConfirmMgr.TG_TRADE_APPR_AER_I
ON ConfirmMgr.TRADE_APPR
AFTER INSERT
AS
/******************************************************************************
*
* AUTHOR:       JAVIER MONTERO - 08/19/2015
* DB:           SQL SERVER 2012 OR HIGHER
* VERSION:      1.0
* DESCRIPTION:  TRIGGER FOR UPDATE THE FIELD FINAL_APPROVAL_FLAG, 
                FINAL_APPROVAL_TIMESTAMP_GMT AND TRANSACTION_SEQ
                IN TABLE TRADE_SUMMARY WHEN A INSERT ACTION IS FIRED IN
                TRADE_APPR TABLE 
* DEPENDECIES:  TABLE TRADE_SUMMARY AND SEQUENCE SEQ_TRADE_SUMMARY_TRANSACTION
*
*******************************************************************************/
DECLARE 
@v_id       int

BEGIN
        /*The process collect the trade_id that is coming in the inserted values*/
        SELECT @v_id = ts.TRADE_ID
        FROM ConfirmMgr.TRADE_SUMMARY ts
        JOIN inserted i
        ON ts.TRADE_ID = i.TRADE_ID
        print cast(@v_id as varchar)
        /*The trade_id saved in the local variable @v_id is validated is different to Null*/
        IF (@v_id) IS NOT NULL 
        BEGIN
            /*Finally we update the fields FINAL_APPROVAL_FLAG, FINAL_APPROVAL_TIMESTAMP_GMT and TRANSACTION_SEQ 
            in the table TRADE_SUMMARY also it use a SQL Sequence to set the TRANSACTION SEQUENCE value in the table */
            UPDATE ts
            SET ts.FINAL_APPROVAL_FLAG = i.APPR_FLAG,
            ts.FINAL_APPROVAL_TIMESTAMP_GMT = i.APPR_TIMESTAMP_GMT,
            ts.TRANSACTION_SEQ = NEXT VALUE FOR ConfirmMgr.SEQ_TRADE_SUMMARY_TRANSACTION
            FROM ConfirmMgr.TRADE_SUMMARY ts
            INNER JOIN inserted i
            ON ts.TRADE_ID = i.TRADE_ID
        END
END

Answer 1

An INSTEAD OF INSERT trigger performs actions instead of what the original INSERT would have done.

In your code, if either @appr_username or @appr_id is NULL, some sort of change is made to the base table (an insert or an update).

Otherwise, nothing is done by the trigger (no rows affected), so the AFTER triggers are skipped. After all, SQL Server thinks, if the INSTEAD OF trigger did nothing, there can't be anything for an AFTER trigger to do either, right?

Note you can also get some very confusing behaviours if the server configuration option nested triggers (shown as Allow Triggers to Fire Others in the SSMS Advanced Server Properties Tab) is set to 1 (the default).

From that link:

The nested triggers option controls whether an AFTER trigger can cascade. That is, perform an action that initiates another trigger, which initiates another trigger, and so on. When nested triggers is set to 0, AFTER triggers cannot cascade. When nested triggers is set to 1 (the default), AFTER triggers can cascade to as many as 32 levels. INSTEAD OF triggers can be nested regardless of the setting of this option.