Questions on triggers and when they fire...

I have a stored proc that does something like 'Update table1 set a = 'b'
where c = 'd''
That works fine, no probs and updates multiple records in table1.
The problem I have is with the Update trigger. It only seems to fire
ONCE
(right at the end), regardless of how many rows the stored procedure
updated.
The only way around it that I can see is to only update one row at a
time in
the table and then the update trigger will work OK - which seems a bit
cumbersome
to me.
How can I get the Update trigger to fire for each row that is updated?
Thanks
Chris"How can I get the Update trigger to fire for each row that is updated?
"
-Short answer, you can=B4t. Triggers will fire on a statement basis NOT
on a row basis. Therefore all affected rows by the statement are
available in the inserted and deleted tables. You have to implement
setbased solution to handle all rows. If you execute a stored procedure
for each row you have to loop through the resultset (with using a temp
table or a (but not recommandable) cursor.
CREATE TRIGGER SomeTrigger
ON SomeTable
FOR UPDATE
AS
BEGIN
UPDATE SomeOtherTable
SET SomeColumn =3D INSERTED.SomeColumn
FROm SomeOtherTable
INNER JOIN
INSERTED.IDColumntoJoin
ON WSomeOtherTable.IDColumntoJoin =3D INSERTED.IDColumntoJoin
END
HTH, jens Suessmeyer.|||On 13 Jan 2006 03:03:23 -0800, Jens wrote:
>"How can I get the Update trigger to fire for each row that is updated?
>"
>-Short answer, you can´t. Triggers will fire on a statement basis NOT
>on a row basis. Therefore all affected rows by the statement are
>available in the inserted and deleted tables. You have to implement
>setbased solution to handle all rows. If you execute a stored procedure
>for each row you have to loop through the resultset (with using a temp
>table or a (but not recommandable) cursor.
Hi Jens,
Much better suggestions for such a case are:
(Preferred) Rewrite the stored procedure's logic to set-based query and
insert that query in the trigger code.
(Or, in rare conditions) Rewrite the stored procedure to a set-based
procedure; have the trigger call this procedure after copying data from
inserted and deleted into temp tables.
--
Hugo Kornelis, SQL Server MVP