1 of 2

SQL Server After Insert Trigger do not perform actions

I have a problem with 2 After Triggers and 1 Instead Of Triggers, those After triggers should be perform some actions but that actions are not fired. When the Instead Of Trigger fired their actions the After Triggers works fine.

Let me explain: The Instead of Trigger validated If the ID value is Null is this value is Null then the Trigger perform the insert action in the table(TRADE_APPR) filling the ID value from a SQL Sequence. When the Instead Of trigger perform your actions the After Triggers too perform their actions. The code of my Instead of 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 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 trigger and the record was saved and the After Triggers works fine.

My question is: Why the instead Of trigger affect the insert actions when you send the rights values?

Below is my code in one of my After Triggers Thanks for your advice's

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