Trigger only fires once on bulk insert

Trigger only fires once on bulk insert

Post by Kalen Delane » Wed, 01 Dec 1999 04:00:00



Nope, there is no way to change this. For any modification, insert update or
delete, the trigger fires once no matter how many rows are affected. Note
that this even means even if 0 rows are affected, the trigger will fire.


see the number of rows. The virtual table 'inserted' will contain copies of
all the inserted rows.

HTH

--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
     www.TheHungerSite.com


Quote:> Hello,

> We are creating a trigger on a table for insert, but for following
> command, the tigger fires once, rather than per row.

> insert into table1 select * from table2

> is there anyway to set it up, so it fires for each row being insered?

> I would appreciate any help and suggestions.

> Thank you,

> Benjamin

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

Trigger only fires once on bulk insert

Post by BPMargoli » Wed, 01 Dec 1999 04:00:00


Benjamin,

SQL Server triggers fire once for the entire transaction, not on a
row-by-row basis.

You need to be able to write the triggers so that they can handle multirow
inserts, deletes and updates.

You do not indicate which version of SQL Server you are using, but assuming
it is SQL Server 7.0, then check out the section "Multirow Considerations"
in the SQL Server 7.0 Books Online.


Quote:> Hello,

> We are creating a trigger on a table for insert, but for following
> command, the tigger fires once, rather than per row.

> insert into table1 select * from table2

> is there anyway to set it up, so it fires for each row being insered?

> I would appreciate any help and suggestions.

> Thank you,

> Benjamin

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

Trigger only fires once on bulk insert

Post by bberouk.. » Thu, 02 Dec 1999 04:00:00


Hello,

We are creating a trigger on a table for insert, but for following
command, the tigger fires once, rather than per row.

insert into table1 select * from table2

is there anyway to set it up, so it fires for each row being insered?

I would appreciate any help and suggestions.

Thank you,

Benjamin

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Trigger only fires once on bulk insert

Post by Christer Romso » Thu, 02 Dec 1999 04:00:00


BPMargolin

Quote:> SQL Server triggers fire once for the entire transaction, not on a
> row-by-row basis.

They fire once per data modification statement not at the commit
of the transaction.

    Christer Romson

 
 
 

Trigger only fires once on bulk insert

Post by BPMargoli » Thu, 02 Dec 1999 04:00:00


Coutta,

Cursors should be viewed as a last resort. Very few operations can not be
performed faster and better using straight SQL. The section "Multirow
Considerations" in the SQL Server 7.0 Books Online has some very fine
examples of using straight SQL with multirow inserts, deletes and updates.

Perhaps you could post details on what you need accomplished, and either I
or another of the denizens of these newsgroups will almost certainly offer
assistance.


> Is the use of a cursor on inserted the way to go with multirow inserts,
> deletes & updates ?


> SNIP

 
 
 

Trigger only fires once on bulk insert

Post by Coutt » Fri, 03 Dec 1999 04:00:00


Is the use of a cursor on inserted the way to go with multirow inserts,
deletes & updates ?

SNIP

 
 
 

Trigger only fires once on bulk insert

Post by Coutt » Wed, 08 Dec 1999 04:00:00


Sorry I haven't replied for a while.

The situation I was referring to was keeping a log of changes to particular
fields on a table.  I was using a cursor in the update trigger to generate
records in a log table.

Now that I've looked at the situation I cannot work out why I used a cursor
to achieve this result.  It has been replaced with a straight SQL query.


>Coutta,

>Cursors should be viewed as a last resort. Very few operations can not be
>performed faster and better using straight SQL. The section "Multirow
>Considerations" in the SQL Server 7.0 Books Online has some very fine
>examples of using straight SQL with multirow inserts, deletes and updates.

>Perhaps you could post details on what you need accomplished, and either I
>or another of the denizens of these newsgroups will almost certainly offer
>assistance.