Controlling Trigger Execution When Bulk Importing Data

Question

Hi

In bulk insertion only in the last record trigger is executing.

If I bulk insert 10 records the trigger is running only for the 10th record. But I need trigger to run on all 10 records.

Please help me with an example

Answer 1

I assume you're talking SQL Server? This page gives you some explanation. To enable triggers, you would use a syntax like this:

INSERT ... SELECT * FROM OPENROWSET(BULK...)

Answer 2

Trigger is executed per statement, not per row. If you assign some variable from a column in inserted or deleted tables, you will get only single, probably last value. The inserted table will contain as many rows as there are in the bulk insert batch. Here's an example with AdventureWorks database:

declare @AddressLine nvarchar(50)
select count(*) [Address count] from Person.Address
select @AddressLine = AddressLine1 from Person.Address
select @AddressLine --only one, last address line