none
Create Trigger to run when field changes from NULL to NOT NULL

    Question

  • Hello,

    I need an update trigger that will only run when a field changes from NULL to NOT NULL.  At a fundamental level I’m trying to find a trigger that will only run once.  So, when column A changes from NULL to NOT NULL, it runs code that enters follow up dates in another table.

    Background Info: I am trying to automate a process where follow up dates are automatically entered based on a field.  I need this code to run only once, because when the step is completed I have code to set the follow up date field to NULL.  We run reports based on a field being NULL or NOT NULL.

    I'm running SQLSERVER 2008 R2

    As always, any help is greatly appreciated.

    David92595


    • Edited by David92595 Tuesday, November 19, 2013 12:14 AM Added SQL Version
    Tuesday, November 19, 2013 12:00 AM

Answers

  • Assuming that you want to update another table, when this change happens,  your trigger would look like this outline:

    CREATE TRIGGER tri ON tbl FOR UPDATE AS

    IF UPDATE(col)
    BEGIN
       UPDATE othertbl
       SET    somecol = getdate()
       FROM   othertbl ot
       JOIN   inserted i ON ot.whatcol = i.keycol
       JOIN   deleted d ON i.keycol = d.keyfol
       WHERE  i.col IS NOT NULL
         AND  d.col IS NULL
    END

    The key here is that the table inserted holds the rows as they look after the UPDATE statement, while deleted holds the rows as they looked before the UPDATE statement. The condition IF UPDATE() make sure that we only run the UPDATE if the column is mentioned in the UPDATE statement.

    Note also that the trigger must be able to handle that multiple rows are updated, of which the value changes from NULL to non-NULL for only some rows.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 19, 2013 12:34 AM
  • IF statement is probably not what you want, since different rows could have been updated to different values. If you only want to update rows for Alabama, it's probably a condition in the WHERE clause you are looking for.

    On the other hand, if you want different values for Alabama and Maine, it may be a CASE expression in the SET clause. Then again, that quick goes out of hand, and it may be better to put the values in a table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 19, 2013 2:06 AM

All replies

  • Please look into the below link.

    http://stackoverflow.com/questions/280904/trigger-to-fire-only-if-a-condition-is-met-in-sql-server


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Tuesday, November 19, 2013 12:28 AM
  • Assuming that you want to update another table, when this change happens,  your trigger would look like this outline:

    CREATE TRIGGER tri ON tbl FOR UPDATE AS

    IF UPDATE(col)
    BEGIN
       UPDATE othertbl
       SET    somecol = getdate()
       FROM   othertbl ot
       JOIN   inserted i ON ot.whatcol = i.keycol
       JOIN   deleted d ON i.keycol = d.keyfol
       WHERE  i.col IS NOT NULL
         AND  d.col IS NULL
    END

    The key here is that the table inserted holds the rows as they look after the UPDATE statement, while deleted holds the rows as they looked before the UPDATE statement. The condition IF UPDATE() make sure that we only run the UPDATE if the column is mentioned in the UPDATE statement.

    Note also that the trigger must be able to handle that multiple rows are updated, of which the value changes from NULL to non-NULL for only some rows.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 19, 2013 12:34 AM
  • Thank you both for your responses.

    Erland, I went with your code. However, I'm getting an error "Incorrect syntax near 'END'.  Any chance either of you could figure out what I'm missing? I'm not very good with triggers, esp when they join the inserted and delted tables...

    IF UPDATE(file_recd)
    BEGIN
    Update Activity_Tracking_AZ
    SET [SCRA_Referral_Completed_F] = Case 
    WHEN (DatePart(dw, File_Recd) <= 5) Then DateADD (day,1, File_Recd) 
    WHEN (DatePart(dw,File_Recd) = 6) Then DateADD (day,3, File_Recd) 
    WHEN (DatePart(dw,File_Recd) = 7) Then DateADD (day,2, File_Recd) End
    FROM Activity_Tracking_AZ as ATAZ JOIN inserted as I ON ATAZ.TS = I.TS
    JOIN deleted as D ON I.TS = D.TS
    WHERE I.File_Recd IS NOT NULL AND D.File_Recd IS NULL
    END

    Tuesday, November 19, 2013 12:55 AM
  • Nevermind, found it.

    I had one to many Begin's

    Thank you both

    Tuesday, November 19, 2013 1:07 AM
  • I'd like to expand on this trigger a bit more.

    Is there a way to put in an IF statement, so different Update is dependent on what the State is (State is actual U.S. State)?

    The State would have been recently entered and can be pulled from the inserted table, if need be, or against the table.

    David92595


    • Edited by David92595 Tuesday, November 19, 2013 1:30 AM
    Tuesday, November 19, 2013 1:27 AM
  • IF statement is probably not what you want, since different rows could have been updated to different values. If you only want to update rows for Alabama, it's probably a condition in the WHERE clause you are looking for.

    On the other hand, if you want different values for Alabama and Maine, it may be a CASE expression in the SET clause. Then again, that quick goes out of hand, and it may be better to put the values in a table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 19, 2013 2:06 AM
  • That make more sense.  Thank you

    David92595

    Tuesday, November 19, 2013 2:07 AM