Does a trigger fire once for a set statement

Does a trigger fire once for a set statement, or for every row affected? I
presume that using a client like Access and doing a line by line manual
adjustment, would fire a trigger for each transaction, but does a set
statement (like below) fire the trigger once?
eg
Table_A (column_a, column_b) with these values:
aa, 0.00
aa, 0.00
bb, 0.00
bb, 0.00
Trigger for update, insert on Table_A
if update(column_a)
update Table_A
set
column_b = 1
If I then run a sql statement to update Table_A, will the trigger fire after
each affected row, or simply fire once for the set statement below?
update table_A
set
colum_a = cc
where column_a = 'aa'
Would the trigger update the whole table once, or would it update all of
column_b twice, because of the two aa's.?
I've googled, but not very well as I haven't hit the answer yet...Any
knowledge on this?
Many thanks.
Steve.Steve'o wrote:
> Does a trigger fire once for a set statement, or for every row affected?
The trigger is fired once for each statement. This means that if your
statement affects multiple rows it is still only executed one time.
For more information look at the "CREATE TRIGGER" documentation in Books
Online.
http://msdn.microsoft.com/library/e...reate2_7eeq.asp
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Hi
Also, look at the virtual tables INSERTED and DELETED to see which rows are
affected by your update.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:O$vefqgDFHA.3648@.TK2MSFTNGP10.phx.gbl...
> Steve'o wrote:
> The trigger is fired once for each statement. This means that if your
> statement affects multiple rows it is still only executed one time.
> For more information look at the "CREATE TRIGGER" documentation in Books
> Online.
> http://msdn.microsoft.com/library/e...reate2_7eeq.asp
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/|||Many thanks, I think I may need to use a curosr then...
I have a table which stores a alpha+numeric sequence number. When a column
in another table is set to true, it fires a trigger to retrive the next
sequence number, then update the sequences table.
I kept it in a sequence table because there are several different sequences,
and this allows a bit of central control.
When updating a row at a time, this will work fine, but if I do a mass
update with a update table statement which affects many rows, the trigger
will only fire once... Hence why Im assuming a cursor is the way forward,
a
bit more reading to do ;)
Thanks for the link.
PS. Already using inserted and deleted in some triggers, just wasn't sure if
they fired for every line, thanks again to both replies.
"Aaron Weiker" wrote:

> Steve'o wrote:
> The trigger is fired once for each statement. This means that if your
> statement affects multiple rows it is still only executed one time.
> For more information look at the "CREATE TRIGGER" documentation in Books
> Online.
> http://msdn.microsoft.com/library/e...reate2_7eeq.asp
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/
>