inserted / deleted tables for triggers

Hi i was hoping someone could help me. If i have the following trigger
defined:
CREATE TRIGGER mytrigger ON mytableview
INSTEAD OF UPDATE
AS
UPDATE mytable SET
field1 = ISNULL(inserted.field1, 0),
field2 = ISNULL(inserted.field2, 0),
field3 = ISNULL(inserted.field3, 0)
FROM inserted
WHERE mytable.userid = inserted.userid
Iperform the following:
UPDATE mytableview
SET field1 = 1
WHERE userid = 1234
lets take for example the row pertaining to userid = 1234 within mytable to
be:
userid field1 field2 field3
1234 0 1 2
What is the state of the inserted table when the trigger is fired? Does the
inserted table do the following:
1) copy into itself the row from mytable pertaining to userid = 1234
2) modify this copied row to reflect field1 = 1
so inserted looks like this:
userid field1 field2 field3
1234 1 1 2
OR
1) creates a row within itself with field1 = 1, and all the other fields set
to NULL?
so inserted looks like this:
userid field1 field2 field3
NULL 1 NULL NULL
Ay help most appreciated. I think i am slightly with the state of
the inserted/deleted tables when triggers are invovled.
Cheers,
peterAn UPDATE with a trigger is performed as a DELETE followed by an INSERT. So
the DELETED table will contain the *before* data records and the INSERTED
table will contain the *after* data records.
HTH
Jerry
"PWalker" <pwalker@.nospam.com> wrote in message
news:OlU7ioz0FHA.2428@.tk2msftngp13.phx.gbl...
> Hi i was hoping someone could help me. If i have the following trigger
> defined:
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0)
> FROM inserted
> WHERE mytable.userid = inserted.userid
>
> Iperform the following:
>
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
>
> lets take for example the row pertaining to userid = 1234 within mytable
> to be:
> userid field1 field2 field3
> 1234 0 1 2
> --
> What is the state of the inserted table when the trigger is fired? Does
> the inserted table do the following:
> 1) copy into itself the row from mytable pertaining to userid = 1234
> 2) modify this copied row to reflect field1 = 1
> so inserted looks like this:
> userid field1 field2 field3
> 1234 1 1 2
> OR
> 1) creates a row within itself with field1 = 1, and all the other fields
> set to NULL?
> so inserted looks like this:
> userid field1 field2 field3
> NULL 1 NULL NULL
>
> Ay help most appreciated. I think i am slightly with the state of
> the inserted/deleted tables when triggers are invovled.
> Cheers,
> peter
>|||thanks, so an update removes the relevant row(s) from the trigger table and
sticks them into the deleted table; then inserts the new modified row(s)
into both the trigger table and the inserted table.
thanks for the clarification.
cheers, peter

> An UPDATE with a trigger is performed as a DELETE followed by an INSERT.
> So the DELETED table will contain the *before* data records and the
> INSERTED table will contain the *after* data records.
> HTH
> Jerry
> "PWalker" <pwalker@.nospam.com> wrote in message
> news:OlU7ioz0FHA.2428@.tk2msftngp13.phx.gbl...
>