Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Formulate a query inside Trigger body USING INSERTED AND DELETED

已答覆 Formulate a query inside Trigger body USING INSERTED AND DELETED

  • Friday, March 22, 2013 12:07 AM
     
     
    Sign In to Vote
    0

    Table Definition

             ID   NAME   FLAG

    Eg:    10    JOHN    NULL

             11   MARK     1

    Assuming there is another Table 'X'  (ID,Type)

    My Requirement is to create a trigger on this table such that

       i) When new row with Flag = 1 is inserted, a row needs to be inserted in another Table 'X'  with the same ID and Type  = 'INSERT'

      ii) When an Existing row with Flag = 1 got deleted, a row needs to be inserted in another Table 'X' with the same ID and Type = 'DELETE'

      iii) When an Existing row with Flag = 1 is updated to Flag = NULL, a row needs to be inserted into another Table 'X' with the same ID and Type = 'DELETE'

      iv) When an Existing row with Flag = NULL is updated to Flag = 1, a row needs to be inserted into another Table 'X' with the same ID and Type = 'INSERT'

    I am using INSERTED and DELETED tables but I am not able to cover (iii) and (iv) scenarios

    Here is my trigger body looks like :

    CREATE TRIGGER tr
    ON ACCOUNTS FOR UPDATE, INSERT, DELETE
    AS BEGIN
        SET NOCOUNT ON

        INSERT INTO X(ID,TYPE)
        SELECT ID,'INSERT'  FROM INSERTED WHERE FLAG != NULL

        INSERT INTO X(ID,TYPE)
        SELECT ID,'DELETE'  FROM INSERTED WHERE FLAG != NULL

    END

    Can any one help me in completing the all four requirements?

    Thanks

    Sindhura



    • Edited by PARTITION Friday, March 22, 2013 12:11 AM
    • Edited by PARTITION Friday, March 22, 2013 12:18 AM
    •  
     

All Replies

  • Friday, March 22, 2013 2:52 AM
     
     Answered Has Code
    Sign In to Vote
    0
     

    CREATE TRIGGER tr
ON ACCOUNTS FOR UPDATE, INSERT, DELETE
AS BEGIN
    SET NOCOUNT ON
    IF EXISTS(SELECT * FROM inserted)
    BEGIN
      IF EXISTS(SELECT * FROM deleted)
      BEGIN
        -- trigger was fired by an UPDATE command
       INSERT INTO X(ID,TYPE)
       SELECT i.ID, CASE WHEN i.FLAG IS NOT NULL THEN 'INSERT' ELSE 'DELETE' END 
         FROM inserted i
         INNER JOIN deleted d ON i.ID = d.ID
         WHERE (i.FLAG IS NULL AND d.FLAG IS NOT NULL)
            OR (i.FLAG IS NOT NULL AND d.FLAG IS NULL)
      END
      ELSE
      BEGIN
        -- trigger was fired by an insert command 
        INSERT INTO X(ID,TYPE)
        SELECT ID,'INSERT'  
        FROM inserted 
        WHERE FLAG IS NOT NULL
      END
    END
    ELSE
    BEGIN
      -- trigger was fired by a delete command
      -- or trigger was fired by a command that did not change any rows
      -- if no rows were changed, deleted will be empty and the following will correctly do nothing
      INSERT INTO X(ID,TYPE)
      SELECT ID,'DELETE'
      FROM deleted 
      WHERE FLAG IS NOT NULL
    END

END

    Note that your description of the problem said to check when FLAG = 1, but your code was checking for FLAG != NULL.  I assumed your code was correct so I checked for whether or not FLAG was NULL.  If you really meant to test for FLAG = 1,  then everywhere in the above where there is FLAG IS NOT NULL, replace it with FLAG = 1.

    Also, note that the correct way to test whether a column or variable is NULL, is to use  IS NULL or IS NOT NULL as I did above.  Do not use = NULL or <> NULL or != NULL for comparison tests.

    Tom