Modifing the row that invokes a trigger from within that trigger

[nosbtr1]

When a row gets modified and it invokes a trigger, we would like to be
able to update the row that was modified inside the trigger. This is
(basically) how we are doing it now:

CREATE TRIGGER trTBL ON TBL
FOR UPDATE, INSERT, DELETE
as
update TBL
set fld = 'value'
from inserted, TBL
where inserted.id= TBL.id

....

This work fine but it seems like it could be optimized. Clearly we are
having to scan the entire table again to update the row. But shouldn't
the trigger already know which row invoked it. Do we have to scan the
table again for this row or is their some syntax that allows us to
update the row that invoked the trigger. If not, why. It seems like
this would be a fairly common task. Thanks.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The trigger does "know" which rows have been updated, they are in the
inserted recordset.

If you want to eliminate table scans put an index on the "joining"
columns between inserted and the updated table: in your case the [id]
column in the TBL table.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmgkHIechKqOuFEgEQKj0ACg/gThmkK3I5FVs014i96EY1KEqyEAniEQ
TeGhjpWGcoYjj51fomXBxth4
=ilXF
-----END PGP SIGNATURE-----

nosbtr1 wrote:

When a row gets modified and it invokes a trigger, we would like to be
able to update the row that was modified inside the trigger. This is
(basically) how we are doing it now:

CREATE TRIGGER trTBL ON TBL
FOR UPDATE, INSERT, DELETE
as
update TBL
set fld = 'value'
from inserted, TBL
where inserted.id= TBL.id

...

This work fine but it seems like it could be optimized. Clearly we are
having to scan the entire table again to update the row. But shouldn't
the trigger already know which row invoked it. Do we have to scan the
table again for this row or is their some syntax that allows us to
update the row that invoked the trigger. If not, why. It seems like
this would be a fairly common task. Thanks.

[Hugo Kornelis]

On 21 Apr 2005 14:11:20 -0700, nosbtr1 wrote:

When a row gets modified and it invokes a trigger, we would like to be
able to update the row that was modified inside the trigger. This is
(basically) how we are doing it now:

CREATE TRIGGER trTBL ON TBL
FOR UPDATE, INSERT, DELETE
as
update TBL
set fld = 'value'
from inserted, TBL
where inserted.id= TBL.id

...

This work fine but it seems like it could be optimized. Clearly we are
having to scan the entire table again to update the row. But shouldn't
the trigger already know which row invoked it. Do we have to scan the
table again for this row or is their some syntax that allows us to
update the row that invoked the trigger. If not, why. It seems like
this would be a fairly common task. Thanks.

Hi nosbtr1,

1. There is no "special syntax" to find the rowS (note the plural)
affected by the statement that fired the trigger, other than what you
are already using: the inserted and deleted pseudo-tables.

2. Your statement that this will cause a table scan is incorrect - if
you have a PRIMARY KEY constraint, a UNIQUE constraint or an INDEX
defined for the id column in the table, SQL Server can use an index seek
(followed by a bookmark lookup if the index used is nonclustered).

3. Your trigger won't do anything on delete operations. First because
the inserted pseudotable is always empty on a delete, and second becuase
the rows you are attempting to modify are already removed from the
table.

4. Why not rewrite the UPDATE statement above to the ANSI-compliant
alternative:

UPDATE Tbl
SET Fld = 'value'
WHERE EXISTS (SELECT *
FROM inserted
WHERE inserted.id = Tbl.id)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Alex]

You could use an instead of trigger, in this way you actually control
the way the data is updated in the table.

the code is much more complex though

[Erland Sommarskog]

nosbtr1 (no*****@yahoo.com) writes:

When a row gets modified and it invokes a trigger, we would like to be
able to update the row that was modified inside the trigger. This is
(basically) how we are doing it now:

CREATE TRIGGER trTBL ON TBL
FOR UPDATE, INSERT, DELETE
as
update TBL
set fld = 'value'
from inserted, TBL
where inserted.id= TBL.id

...

This work fine but it seems like it could be optimized. Clearly we are
having to scan the entire table again to update the row. But shouldn't
the trigger already know which row invoked it.

A trigger fires once per statement, and thus many rows may be affected.

The main performance thing with triggers is that one should be aware of
that the inserted/deleted tables are fairly slow. If you trigger makes
frequent references to these tables, it's a good idea to insert the data
into the pseudo tables into table variables, and work with these instead.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp