Trigger to insert records

• nghoiming02

  SSC-Addicted

  Points: 418

  More actions

  March 11, 2004 at 6:59 am

  #107231

  Dear everyone,

  I use AFTER INSERT triggers to insert records in one table based on another table.

  When user insert 1 record in SYSTEM_DATA table, the trigger will fire and insert 1 record in BOOK table accordingly.

  But there ar 3 records inserted by triggers in BOOK after 2 records inserted in SYSTEM_DATA. I don't know what error I have made.

  Could anyone point the error to me?? And How can I fix it??

  Trigger code following:

  create  trigger AfterInsertSysData

  on systemDataTest

  after insert

  not for replication

  as

  insert into fac_test (rid, fname, book_date)

  select r.rid, fname, book_date from systemDataTest s, res_test r

  where substring(upper(r.type),1,1) in ('F','M','U','R') and

  r.rid = (select rid from inserted)

   

  Thanks you very very much!!!!

• Brandon Hunt

  SSC Veteran

  Points: 283

  More actions

  March 11, 2004 at 9:24 am

  #498321

  You're using the actual table and not the Inserted virtual table.  This is what's happening:

  Insert one row in System_Data, trigger fires and inserts the number of rows in System_Data into Book.

  You insert the second row into System_Data, trigger fires and now it inserts both rows into Book.

  If you insert a 3rd row into System_data, you should end up with 6 rows in Book.

  Now on to the fix...

  Simply replace systemDataTest in your trigger's select (line 2 of actual T-SQL) with Inserted.  You can read up more on Inserted (and Deleted) in BOL.  Look at "Using the inserted and deleted Tables" as a good starting point

  Hope this helps,

  
  
  -Brandon