locked
after update ,insert trigger RRS feed

  • Question

  • my sample trigger
    ALTER TRIGGER [dbo].[TESTING]            
    ON            
    [dbo].TEST_TRIGGER            
    FOR UPDATE,INSERT            
    AS  
    BEGIN
    DECLARE @ID INT
    DECLARE @NAME VARCHAR(30)
    SELECT @ID=ID FROM INSERTED
    SELECT @NAME=NAME FROM INSERTED


     IF UPDATE(BIT)
    BEGIN
    UPDATE TRI_TABLE SET DT=GETDATE() WHERE ID=@ID
    SELECT 'UPDATE'
    END
    END

    even if i use insert command updated statement runs in trigger

    i want to run only when i use update statment

    INSERT INTO TEST_TRIGGER(ID,NAME,BIT) VALUES(4,'krishna','89')


              
    Thursday, March 22, 2018 8:20 AM

Answers

  • My trigger is long.That why i posted sample trigger. I want in the single trigger.

    Do you get the irony?

    This is a further reason for using two triggers.. And as I wrote, you need to check the cardinalities.

    ALTER TRIGGER [dbo].[TESTING]
    ON [dbo].TEST_TRIGGER
    FOR UPDATE, INSERT
    AS
    SET NOCOUNT ON;
    
    DECLARE @CountDeleted INT = (   SELECT COUNT(*)
                                    FROM   DELETED );
    
    IF ( @CountDeleted = 0 )
        BEGIN
            -- INSERT	
            INSERT INTO TRI_TABLE
                        SELECT I.ID ,
                               I.[Name]
                        FROM   INSERTED I
                               LEFT JOIN TRI_TABLE T ON T.ID = I.ID
                        WHERE  T.ID IS NULL;
        END;
    ELSE
        BEGIN
            -- UPDATE
            UPDATE T
            SET    T.DT = GETDATE()
            FROM   TRI_TABLE T
                   INNER JOIN INSERTED I ON T.ID = I.ID
                   INNER JOIN DELETED D ON D.ID = I.ID
            WHERE  I.[Bit] != D.[Bit];
        END;

    btw, you are already having code problems. Why insisting on keeping the reasons for them?

    • Marked as answer by Raghunadhan Thursday, March 22, 2018 12:45 PM
    Thursday, March 22, 2018 9:43 AM
  • My query

    UPDATE  tblart  SET             
    art_AA_recd_dt = GETDATE() 
    FROM   tblArticle tblart   INNER JOIN INSERTED I ON tblart.jour_no = I.jour_no and tblart.art_no = I.art_no and tblart.art_serial_no= I.art_serial_no

     INNER JOIN DELETED D ON D.jour_no = I.jour_no and D.art_no = I.art_no and D.art_serial_no= I.art_serial_no      WHERE             
      I.art_AA_production_tag!=D.art_AA_production_tag

                    

    Looks good

    and sets the same timestamp value for all the updated rows

    Hope thats your expected result.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Raghunadhan Thursday, March 22, 2018 12:59 PM
    Thursday, March 22, 2018 12:31 PM

All replies

  • First of all: INSERTED and DELETED are virtual tables. Thus they can return more than one row.

    Then: When you only want the trigger to be executed after UPDATE, then you need to specify only FOR UPDATE. Not FOR UPDATE, INSERT.

    And last, but not least: The UPDATE() function tests whether the column is used in the executed statement. It does not test, whether this value was really changed.

    E.g.

    ALTER TRIGGER [dbo].[TESTING]
    ON [dbo].TEST_TRIGGER
    FOR UPDATE
    AS
    UPDATE T
    SET    T.DT = GETDATE()
    FROM   TRI_TABLE T
           INNER JOIN INSERTED I ON T.ID = I.ID
           INNER JOIN DELETED D ON D.ID = I.ID
    WHERE  I.[Bit] != D.[Bit];
    GO

    When [Bit] - btw, don't use reserved words as object names - is NOT NULL, which every BIT should be. Otherwise you need further NULL handling.

    Thursday, March 22, 2018 8:39 AM
  • SORRY SIR ,ITS MY SAMPLE TIRGGER.

    mY REAL TIME TRIGGER CONTAINS BOTH INSERT AND UPDATE STATEMENT

    so my trigger be like FOR UPDATE, INSERT.

    But even when  i  use insert command .

    the update(columname) functions runs  in the trigger

    IN my FOR UPDATE, INSERT. trigger

    in update(flag )  block

    statements should run when i use only  update statement to update flag column


    Thursday, March 22, 2018 8:52 AM
  • you trigger is written considering only single row inserts 

    ideally you should make it like below for multi row inserts

    ALTER TRIGGER [dbo].[TESTING]            
    ON            
    [dbo].TEST_TRIGGER            
    FOR UPDATE,INSERT            
    AS  
    BEGIN
    
    
     IF UPDATE(BIT)
    BEGIN
    
    UPDATE t
    SET DT=GETDATE() 
    FROM TRI_TABLE  t
    JOIN INSERTED i
    ON i.ID = t.ID
    JOIN DELETED d
    ON d.ID = t.ID
    WHERE i.BIT <> d.BIT
    
    END
    
    END


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 22, 2018 8:55 AM
  • Ok sir,

    ALTER TRIGGER [dbo].[TESTING]            
    ON            
    [dbo].TEST_TRIGGER            
    FOR UPDATE,INSERT            
    AS  
    BEGIN
    DECLARE @ID INT
    DECLARE @NAME VARCHAR(30)
    SELECT @ID=ID FROM INSERTED
    SELECT @NAME=NAME FROM INSERTED

    if exists(select 1 from TRI_TABLE where ID=@ID)
    begin
    insert into TRI_TABLE values(@ID,@NAME,GETDATE())
    end

     IF UPDATE(BIT)
    BEGIN
    UPDATE TRI_TABLE SET DT=GETDATE() WHERE ID=@ID
    SELECT 'UPDATE'
    END
    END

    even i is insert  a row it updates in tri table.

    i want to run update statement inside the trigger if i use update statement.

    Please help me

    Thursday, March 22, 2018 9:04 AM
  • sorry sir 

    this one

    ALTER TRIGGER [dbo].[TESTING]            
    ON            
    [dbo].TEST_TRIGGER            
    FOR UPDATE,INSERT            
    AS  
    BEGIN
    DECLARE @ID INT
    DECLARE @NAME VARCHAR(30)
    SELECT @ID=ID FROM INSERTED
    SELECT @NAME=NAME FROM INSERTED

    if not exists(select 1 from TRI_TABLE where ID=@ID)
    begin
    insert into TRI_TABLE values(@ID,@NAME,GETDATE())
    end

     IF UPDATE(BIT)
    BEGIN
    UPDATE TRI_TABLE SET DT=GETDATE() WHERE ID=@ID
    SELECT 'UPDATE'
    END
    END

    even i is insert  a row it updates in tri table.

    i want to run update statement inside the trigger if i use update statement.

    Please help me

    Thursday, March 22, 2018 9:04 AM
  • can we use column_update function to check whether column is updated or inserted
    Thursday, March 22, 2018 9:07 AM
  • sorry sir 

    this one

    ALTER TRIGGER [dbo].[TESTING]            
    ON            
    [dbo].TEST_TRIGGER            
    FOR UPDATE,INSERT            
    AS  
    BEGIN
    DECLARE @ID INT
    DECLARE @NAME VARCHAR(30)
    SELECT @ID=ID FROM INSERTED
    SELECT @NAME=NAME FROM INSERTED

    if not exists(select 1 from TRI_TABLE where ID=@ID)
    begin
    insert into TRI_TABLE values(@ID,@NAME,GETDATE())
    end

     IF UPDATE(BIT)
    BEGIN
    UPDATE TRI_TABLE SET DT=GETDATE() WHERE ID=@ID
    SELECT 'UPDATE'
    END
    END

    even i is insert  a row it updates in tri table.

    i want to run update statement inside the trigger if i use update statement.

    Please help me

    Again the same issue persists

    this trigger wont work for multiple row insert/update operations


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 22, 2018 9:24 AM
  • can we use column_update function to check whether column is updated or inserted
    you can use column_update function to check if a column was part of an update operation (not necessarily imply value got changed)

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 22, 2018 9:25 AM
  • No, to check this, you need to look at the cardinalities of INSERTED and DELETED:

    INSERTED > 0 & DELETED = 0 => INSERT (statement) occurred

    INSERTED > 0 & DELETED > 0 => UPDATE (statement) occurred

    But: Just write two triggers. Don't overload one with too much logic. Cause it's faster using two triggers. Much cleaner:

    ALTER TRIGGER dbo.tr_TESTING_I
    ON dbo.TEST_TRIGGER
    FOR INSERT
    AS
    SET NOCOUNT ON;
    
    INSERT INTO TRI_TABLE
                SELECT I.ID ,
                       I.[Name]
                FROM   INSERTED I
                       LEFT JOIN TRI_TABLE T ON T.ID = I.ID
                WHERE  T.ID IS NULL;
    GO
    
    ALTER TRIGGER dbo.tr_TESTING_U
    ON dbo.TEST_TRIGGER
    FOR UPDATE
    AS
    SET NOCOUNT ON;
    
    UPDATE T
    SET    T.DT = GETDATE()
    FROM   TRI_TABLE T
           INNER JOIN INSERTED I ON T.ID = I.ID
           INNER JOIN DELETED D ON D.ID = I.ID
    WHERE  I.[Bit] != D.[Bit];
    GO

    Thursday, March 22, 2018 9:26 AM
  • Dear Sir 

    This is not real tirgger .this is my testing trigger for solving this issue.

    My trigger is long.That why i posted sample trigger.

    I want in the single trigger.

    help me sir

    Thursday, March 22, 2018 9:35 AM
  • My trigger is long.That why i posted sample trigger. I want in the single trigger.

    Do you get the irony?

    This is a further reason for using two triggers.. And as I wrote, you need to check the cardinalities.

    ALTER TRIGGER [dbo].[TESTING]
    ON [dbo].TEST_TRIGGER
    FOR UPDATE, INSERT
    AS
    SET NOCOUNT ON;
    
    DECLARE @CountDeleted INT = (   SELECT COUNT(*)
                                    FROM   DELETED );
    
    IF ( @CountDeleted = 0 )
        BEGIN
            -- INSERT	
            INSERT INTO TRI_TABLE
                        SELECT I.ID ,
                               I.[Name]
                        FROM   INSERTED I
                               LEFT JOIN TRI_TABLE T ON T.ID = I.ID
                        WHERE  T.ID IS NULL;
        END;
    ELSE
        BEGIN
            -- UPDATE
            UPDATE T
            SET    T.DT = GETDATE()
            FROM   TRI_TABLE T
                   INNER JOIN INSERTED I ON T.ID = I.ID
                   INNER JOIN DELETED D ON D.ID = I.ID
            WHERE  I.[Bit] != D.[Bit];
        END;

    btw, you are already having code problems. Why insisting on keeping the reasons for them?

    • Marked as answer by Raghunadhan Thursday, March 22, 2018 12:45 PM
    Thursday, March 22, 2018 9:43 AM
  • Dear Sir 

    This is not real tirgger .this is my testing trigger for solving this issue.

    My trigger is long.That why i posted sample trigger.

    I want in the single trigger.

    help me sir

    You're missing the point

    For having both in the single trigger the suggested check by Stefan Hoffmann is the way to go

    You need to understand basics first

    INSERTED table will have entries only for insert and update operations

    DELETED will have entries only for update and delete operations

    hence 

    INSERTED > 0 & DELETED = 0  check will ensure action performed on the row was insert

    and INSERTED > 0 & DELETED > 0 means it was an update with INSERTED containing new (modified) values and DELETED containing old ones


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 22, 2018 9:44 AM
  • thanks sir ,

    In a tblarticle table 

    i want to update art_finish_dt

    when updating art_flag column

    i used select @cnt=count(*) from deleted

    if(@cnt>0)

    begin

    update article set art_finish_dt=getdate() where art_no=@art

    end

    is it correct

    Thursday, March 22, 2018 11:21 AM
  • NO, IT'S NOT.

    DID YOU READ OUR POSTS ABOUT INSERTED AND DELETED BEING TABLES?

    Consider a user running this update on your table:

    UPDATE tblarticle 
    SET price=0
    WHERE ID IN ( 1, 2, 3);
    This will change three rows. But your trigger will only track *ONE* arbitrary row.


    Thursday, March 22, 2018 11:45 AM
  • My query

    UPDATE  tblart  SET             
    art_AA_recd_dt = GETDATE() 
    FROM   tblArticle tblart   INNER JOIN INSERTED I ON tblart.jour_no = I.jour_no and tblart.art_no = I.art_no and tblart.art_serial_no= I.art_serial_no

     INNER JOIN DELETED D ON D.jour_no = I.jour_no and D.art_no = I.art_no and D.art_serial_no= I.art_serial_no      WHERE             
      I.art_AA_production_tag!=D.art_AA_production_tag

                    
    Thursday, March 22, 2018 11:58 AM
  • Did you run any test at all?

    USE tempdb;
    GO
    
    CREATE TABLE dbo.SampleTable
        (
            ID INT IDENTITY PRIMARY KEY ,
            Payload INT NOT NULL ,
            LastChange DATETIME NOT NULL
        );
    
    ALTER TABLE dbo.SampleTable
    ADD CONSTRAINT DF_SampleTable_LastChange
        DEFAULT GETDATE() FOR LastChange;
    GO
    
    CREATE TRIGGER tr_SampleTable_U
    ON dbo.SampleTable
    AFTER UPDATE
    AS
    SET NOCOUNT ON;
    
    DECLARE @ID INT;
    
    SELECT @ID = Inserted.ID
    FROM   INSERTED;
    
    UPDATE ST
    SET    ST.LastChange = GETDATE()
    FROM   dbo.SampleTable ST
    WHERE  ST.ID = @ID;
    GO
    
    INSERT INTO dbo.SampleTable ( Payload )
    VALUES ( 1 ) ,
           ( 2 ) ,
           ( 3 );
    
    SELECT *
    FROM   dbo.SampleTable ST;
    
    WAITFOR DELAY '00:00:02';
    
    UPDATE dbo.SampleTable
    SET    Payload = Payload + 10;
    
    SELECT *
    FROM   dbo.SampleTable ST;
    GO
    
    DROP TABLE dbo.SampleTable;
    GO

    Thursday, March 22, 2018 12:24 PM
  • My query

    UPDATE  tblart  SET             
    art_AA_recd_dt = GETDATE() 
    FROM   tblArticle tblart   INNER JOIN INSERTED I ON tblart.jour_no = I.jour_no and tblart.art_no = I.art_no and tblart.art_serial_no= I.art_serial_no

     INNER JOIN DELETED D ON D.jour_no = I.jour_no and D.art_no = I.art_no and D.art_serial_no= I.art_serial_no      WHERE             
      I.art_AA_production_tag!=D.art_AA_production_tag

                    

    Looks good

    and sets the same timestamp value for all the updated rows

    Hope thats your expected result.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Raghunadhan Thursday, March 22, 2018 12:59 PM
    Thursday, March 22, 2018 12:31 PM
  • What is the primary key of your article table?

    You should use only the columns of the primary key or a candidate key to join the tables in the trigger.

    Thursday, March 22, 2018 12:41 PM
  • Thank you all
    Thursday, March 22, 2018 12:59 PM
  • jour_no ,

    art_no 

    Friday, March 23, 2018 9:54 AM
  • IF UPDATE(art_AA_production_tag)    
     UPDATE  tblart  SET             
    art_AA_recd_dt = GETDATE()--, 
    --  art_AA_due_dt = @aAADueDate 
    FROM    tblArticle tblart   
    INNER JOIN INSERTED I ON tblart.jour_no = I.jour_no and tblart.art_no = I.art_no and tblart.art_serial_no= I.art_serial_no
    INNER JOIN DELETED D ON D.jour_no = I.jour_no and D.art_no = I.art_no and D.art_serial_no= I.art_serial_no                     
    WHERE    
    tblart.jour_no = @jourNo AND             
    tblart.art_no = @artNo AND             
            tblart. art_serial_no = @aSrlNo  and          
            I.art_AA_production_tag!=D.art_AA_production_tag

    Is it correct ?

    Friday, March 23, 2018 10:03 AM
  • I could tell, when you would post the entire code.

    But I guess not, cause your still using scalars in your predicates. This will mean that in the case of a multi-row update only one row is tracked.

    And it would be also wrong, when art_AA_production_tag is nullable.

    btw, samples should never contain dead code and using table alias names is to shorten code:

    IF UPDATE(art_AA_production_tag)
        UPDATE A
        SET    A.art_AA_recd_dt = GETDATE()
        FROM   tblArticle A
               INNER JOIN INSERTED I ON A.jour_no = I.jour_no
                                        AND A.art_no = I.art_no
                                        AND A.art_serial_no = I.art_serial_no
               INNER JOIN DELETED D ON D.jour_no = I.jour_no
                                       AND D.art_no = I.art_no
                                       AND D.art_serial_no = I.art_serial_no
        WHERE  A.jour_no = @jourNo
               AND A.art_no = @artNo
               AND A.art_serial_no = @aSrlNo
               AND I.art_AA_production_tag != D.art_AA_production_tag;

    Friday, March 23, 2018 12:17 PM