CODEWITHSUNDEEP
sql-serversql-server-2005triggers
Vishwa
Vishwa

Reputation: 9

Controlling Trigger Execution When Bulk Importing Data

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

Upvotes: 0

Views: 360

Answers (2)

Piotr Rodak
Piotr Rodak

Reputation: 1941

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

Upvotes: 1

David M
David M

Reputation: 72870

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...)

Upvotes: 2

Related Questions