Set based trigger with decision to update or insert

  • Hi all,

    I have a tough one (at least for me) here. I am not experienced with writing triggers too much, so be patient. I have a trigger that is executed when and end date is updated on a table. There could be a date there already, but the date can change. My issue is that when this update occurs, I need to either insert or update a record in another table. I need to check if the field is NULL (as the other code used to update this field could set it as NULL) and I need to check a hashed id in the other table. If it finds the hashed Id, it is an update, so I need to update that table and another (a child table). If there is no hashed ID in the remote table, I need to do an insert into both of these tables. The issue here is that since multiple rows could be in the "inserted" table, I cannot just insert or update. I know I could do a loop or cursor, but I really do not want to use these in a trigger. My logic rigth now works for a single insert, but on multiple I of course get errors. My logic is (greatly cut back, just to show my logic):

    If fielda is NOT NULL

    If exists (unique_id=inserted.hashFields)

    Update table 1

    Update table 2

    else

    Insert table 1

    Insert table 2

    Any help is appreciated. I know there has to be a good way, but all resources discussing set based assume you will insert or update all rows, but mine is not that simple.

    Thanks in advance

  • You need to translate your IFs into WHERE clauses...

    update table1

    set col1 = (select col1_source from inserted where hashFields = table1.unique_id)

    where unique_id in (select hashFields from inserted where fielda is not null)

    and something similar for table2

    insert table1(col_list)

    select cols

    from inserted

    where fielda is not null

    and not exists (select 1 from table1 where unique_id = inserted.hashFields)

    and something similar for table2

    Of course the code above is not real and does assume that hashFields is unique within inserted.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Of course, you might also want to only do this if fielda has changed...

    add this to the where clause about fielda not being null :

    ... and not exists ( select 1 from deleted where deleted.hashFields = inserted.hashFields and deleted.fielda = inserted.fielda )

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Thanks for the help. Not sure what I was thinking. This does make a lot more sense.

  • Three things to point out:

    1. In SQL 2008 (I see you posted in 2005, so this is just for FYI purposes), you could use the MERGE statement to do this all in one statement.

    2. You can eliminate the sub-queries from the update statement with this syntax (it's SQL Server specific, not ANSI compliant, and sure to cause Joe to scream, but since we're using SQL Server, let's use everything it makes available to us!):

    UPDATE t

    SET col1 = i.col1_source

    FROM table1 t

    JOIN inserted i

    ON t.unique_id = i.hashFields

    WHERE i.fielda IS NOT NULL

    --only update if data is different

    AND t.col1 <> i.col1_source

    3. Ensure that you do the UPDATE before the INSERT - or else you'll turn around and update everything you just inserted.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Viewing 5 posts - 1 through 5 (of 5 total)

    You must be logged in to reply to this topic. Login to reply