Set based trigger with decision to update or insert

• Shawn Scranton-481665

  SSC Veteran

  Points: 231

  More actions

  February 24, 2011 at 3:24 pm

  #97800

  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

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  February 24, 2011 at 5:47 pm

  #1291336

  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]