Inserted & Deleted Tables!

11 Aug 2005 10:47 PM

Arpan

Suppose a trigger gets fired when the following UPDATE query gets
executed:

----------------------------------------
UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
----------------------------------------

Now the Inserted table will have the new record '12345' in the Pwd
column & the Deleted table will have the old record '11111' in the Pwd
column. So will the record 'jack' exist in the UserID column of both
the Inserted table & the Deleted table that the trigger will be making
use of?

Thanks,

Arpan

11 Aug 2005 10:51 PM

Mike Epprecht (SQL MVP)

Hi

Yes, the whole row, as it was before and after are in the respective tables,
not just the column that changed.

If you update the primary key of a table, then comparing the Inserted and
Deleted becomes very difficult.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quote

"Arpan" <arpan***@hotmail.com> wrote in message
news:1123800474.961292.259990@g49g2000cwa.googlegroups.com...
> Suppose a trigger gets fired when the following UPDATE query gets
> executed:
>
> ----------------------------------------
> UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
> ----------------------------------------
>
> Now the Inserted table will have the new record '12345' in the Pwd
> column & the Deleted table will have the old record '11111' in the Pwd
> column. So will the record 'jack' exist in the UserID column of both
> the Inserted table & the Deleted table that the trigger will be making
> use of?
>
> Thanks,
>
> Arpan
>

11 Aug 2005 11:57 PM

Hugo Kornelis

On 11 Aug 2005 15:47:55 -0700, Arpan wrote:

>Suppose a trigger gets fired when the following UPDATE query gets
>executed:
>
>----------------------------------------
>UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
>----------------------------------------
>
>Now the Inserted table will have the new record '12345' in the Pwd
>column & the Deleted table will have the old record '11111' in the Pwd
>column. So will the record 'jack' exist in the UserID column of both
>the Inserted table & the Deleted table that the trigger will be making
>use of?

Hi Arpan,

Almost.

The exact correct way to put this is:

- The deleted table will hold 0, 1, or many rows that all have UserID
'jack' and Pwd '11111'. Impossible to tell what the other columns will
be.
- The inserted table will hold 0, 1, or many rows (but the same number
as the deleted table) that all have UserID 'jack' and Pwd '12345'; the
other columns will be the same as in the corresponding rows in the
deleted table.

Best, Hugo

--

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