2 added 144 characters in body; edited title
source | link

SQL Server After Insert Trigger dotrigger does not perform actions

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

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

Let me explain: The

The Instead of Trigger validated IfOf trigger validates if the IDID value is Null is this value. If it is Null, then the Trigger performtrigger performs the insert action in the table  (TRADE_APPRTRADE_APPR) filling the IDID value from a SQL SequenceServer sequence. When

When the Instead Of trigger perform yourperforms the actions, the After Triggers tooInsert triggers also perform their actions. The

The code of my Instead ofOf 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      @appr_id          int,
@v_id      @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
    BEGIN    INSERT ConfirmMgr.TRADE_APPR
            INSERTSELECT 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

When you send a null value the Triggertrigger works fine and the After Triggerstriggers 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 TriggerInsert trigger don't perform their actions. I

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

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

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

Below is my code in one of my After Triggers ThanksInsert triggers

Thanks for your advice'sadvice

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

SQL Server After Insert trigger does not perform actions

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

1
source | link

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