Post reply

Trigger to insert records

  • 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!!!!

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

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