July 5, 2011 at 1:58 pm
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?
July 5, 2011 at 2:01 pm
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
July 5, 2011 at 2:08 pm
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.
July 5, 2011 at 2:25 pm
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
July 5, 2011 at 2:30 pm
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?
July 5, 2011 at 2:30 pm
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
July 5, 2011 at 2:39 pm
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
July 5, 2011 at 2:40 pm
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.
February 3, 2022 at 10:12 pm
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
February 3, 2022 at 11:57 pm
Please Suggest
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply