Post reply

AFTER INSERT trigger does not work

  • May 3, 2006 at 5:27 am

    #190474

    I have an existing table where I added an AFTER iNSERT trigger. Within the trigger code, I fetch data from the inserted row (by using INSERTED) and process the fetched data.

    It works fine whenever I'm running a single INSERT call, eg

    INSERT INTO tTable (Field1, Field2, Field3) VALUES (Val1, Val2, Val3)

    But when I do a batch insert, i.e. something like

    INSERT INTO tTable (Field1, Field2, Field3)

    SELECT OtherFld1, OtherFld2, OtherFld FROM tOtherTable

    WHERE tOtherTable.SomeField = SomeValue

    In this case, the trigger runs only once, though I might have 3 rows inserted. Also, even for the only time it runs, all field values of INSERTED return NULL.

    Is this a bug? Any workaround? I have thousands of stored procs which do batch inserts on this table. So I cannot possibly change the code everywhere.

  • May 4, 2006 at 8:11 am

    #635941

    No, this is not a bug. It's the standard behaviour, that the trigger is called only once after the action. In case of your bulk insert you have to use a cursor and scroll through "inserted".

    bye, Helmut

  • May 4, 2006 at 11:52 pm

    #636142

    Thanks Helmut... yeah, I found that out and have rewritten the trigger to loop through the cursor and do the needful.

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

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