how to implement a Multirow insert trigger?

  • I have the following trigger:

    Create trigger [dbo].[new_Location]

    on [dbo].[TBL_LOCATIONS]

    for insert

    as

    insert into dbo.tbl_Events ( Event_Id , Location_Id )

    SELECT NewID(), Inserted.Location_Id

    FROM Inserted

    Which works fine on single-record inserts, however, on multiple row inserts to tbl_Events (batch load) the trigger doesn't fire. How can I modify it to work for multiple record inserts?

  • That trigger will work fine for any number of rows inserted.

    If you're doing bulk inserts (bcp, BULK INSERT, SSIS's OLEDB destination), that doesn't fire triggers by default. Tell us more about how you're inserting the data and someone should be able to figure out what's needed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • It's not working for multiple row inserts. The record source for tbl_locations is ArcGIS. When one "record" is created with ArcGIS, a corresponding record is created in tbl_events just fine. When multiple locations are created in batch, no corresponding records are created in tbl events.

  • The trigger itself will work fine for any number of rows inserted. I can write up a simple test to prove that.

    How are you inserting those multiple rows? You mentioned bulk insert. As I said, by default triggers (of any form) are not fired by a bulk insert statement (or bcp or any other method of bulk loads)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • A software program called "ArcGIS" is used to insert/edit rows in tbl_locations. ArcGIS is a mapping program, and manipulates data in a spatial environment. When a new "location" is create by clicking the mousepointer on the map, a new row is created in tbl_locations, and a corresponding event is created in tbl_events via the correctly working trigger. However, when many locations are created at once, and edits/changes are "committed", no corresponding events are created in tbl_events. How would the existing trigger work with multi-row inserts if all it is capable of reading is only one row from the inserted table?

  • Test to show there's nothing wrong with the trigger as written:

    CREATE TABLE TBL_LOCATIONS (

    Location_Id UNIQUEIDENTIFIER, -- guessing that's what it is

    SomeText VARCHAR(20) -- Since I don't know what the rest of your table is

    )

    GO

    CREATE TABLE tbl_Events (

    Event_Id UNIQUEIDENTIFIER,

    Location_Id UNIQUEIDENTIFIER

    );

    GO

    Create trigger [dbo].[new_Location]

    on [dbo].[TBL_LOCATIONS]

    for insert

    as

    insert into dbo.tbl_Events ( Event_Id , Location_Id )

    SELECT NewID(), Inserted.Location_Id

    FROM INSERTED

    GO

    INSERT INTO TBL_LOCATIONS

    SELECT TOP (10) NEWID(), 'Filler text'

    FROM sys.columns AS c -- just to generate a lot of rows

    GO

    SELECT * FROM tbl_Events

    Event_Id Location_Id

    1CFAFF66-C202-4C0F-BE36-07E25BAF17ABB1720F8D-B31B-4BA7-BF43-8C3243652713

    F2833D9D-3B1B-4203-9FBB-A96162CBCB2936EC2DC0-7752-4EC0-B788-E6FF50ABD5DD

    4FD5D2D6-5752-48A9-8D57-8A6C6842BE7C05C30D11-39E9-455C-A6F7-294A34DC1432

    1D6577AA-345E-45DA-868C-2033FC3E3CCEF1B1A58E-E488-41DD-A765-7A1642D50DD1

    A5A668B7-23D9-4360-9566-FB297FE7B19777E81F30-2195-43FB-BD34-44E72E7EADEA

    6D5290C8-83DB-4992-B6DC-8A34BBB306207BC1272F-AE25-4CAA-9673-6CE1B78A4564

    E0E28820-18D6-49D8-BA4D-24CE6B598AEF5CAC6AF3-2B98-4B2B-9AA8-0DFA6E903970

    6D80C043-5A60-4AF0-B4D4-AF6266CAB251E8ACBDC7-9225-4F2B-80B6-B5AEAE66D377

    8AB2BB40-836E-49F8-BB5C-154C175ADE5E71399865-AD06-461E-B8D8-FE8620C18592

    D6D8C447-BC27-4AD1-BAE6-2C19FCB312AD3283F0D0-CC95-4FC7-91FB-D348F82778E4

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • tpcolson (7/5/2011)


    How would the existing trigger work with multi-row inserts if all it is capable of reading is only one row from the inserted table?

    The existing trigger reads all rows from inserted, not one. See my test. There is nothing wrong with the trigger.

    Can you trace/profile and see what command the GIS system is running to insert the data. My guess is that it's using some form of bulk insert when multiple locations are selected and bulk inserts do not fire triggers by default.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Note that your trigger is insert only. If you want to capture edits, change it to FOR INSERT, UPDATE

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • I have similar issue, I am using bcp to load multiple rows having more than 30 columns to table 1, written a insert trigger to insert /update the master table with the data from table 1. However, when I do bcp , it is throwing me error regarding not handling the multiple rows. IF it is single row of data , its all working fine. Please Suggest

  • Reddy wrote:

    Please Suggest

    1. Start a new thread for your particular problem.
    2. Post the code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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