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