insert trigger and if update(x)

I've not done a lot of triggers, so perhaps this is a dumb question,
but here goes.
BOL says "if update(x)" is for both insert and update triggers, but I
don't understand what role it plays in insert triggers. My little
experiments suggest that in an insert trigger, "if update(x)" fires if
x has a value, if x is null, or if x is omitted from the insert list.
So, is that right, will an "if update(x)" ALWAYS fire inside of an
insert trigger?
(I ask because I'm looking at some legacy code that does just that)
Thanks!
JoshIf an insert only inserts values into some of the tables columns, (Say the
missing ones are nullable) then the if update(x)"on one of the nulleable
columns that was not affected will be false... But I'm wondering what will
happen if the missing column is NOT nulleable and has a default value... I'l
l
run a test...
"JRStern" wrote:

> I've not done a lot of triggers, so perhaps this is a dumb question,
> but here goes.
> BOL says "if update(x)" is for both insert and update triggers, but I
> don't understand what role it plays in insert triggers. My little
> experiments suggest that in an insert trigger, "if update(x)" fires if
> x has a value, if x is null, or if x is omitted from the insert list.
> So, is that right, will an "if update(x)" ALWAYS fire inside of an
> insert trigger?
> (I ask because I'm looking at some legacy code that does just that)
> Thanks!
>
> Josh
>|||See "CREATE TRIGGER" in BOL, it is explained there.
AMB
"JRStern" wrote:

> I've not done a lot of triggers, so perhaps this is a dumb question,
> but here goes.
> BOL says "if update(x)" is for both insert and update triggers, but I
> don't understand what role it plays in insert triggers. My little
> experiments suggest that in an insert trigger, "if update(x)" fires if
> x has a value, if x is null, or if x is omitted from the insert list.
> So, is that right, will an "if update(x)" ALWAYS fire inside of an
> insert trigger?
> (I ask because I'm looking at some legacy code that does just that)
> Thanks!
>
> Josh
>|||Did a test, I was wrong, all columns show as updated in an insert, even
nulleable columns without default values that are not mentioned in the
insert.
"CBretana" wrote:
> If an insert only inserts values into some of the tables columns, (Say the
> missing ones are nullable) then the if update(x)"on one of the nulleable
> columns that was not affected will be false... But I'm wondering what will
> happen if the missing column is NOT nulleable and has a default value... I
'll
> run a test...
> "JRStern" wrote:
>|||The BOL specifically says:
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
The IF UPDATE (column_name) clause in the definition of a trigger can be
used to determine if an INSERT or UPDATE statement affected a specific colum
n
in the table. The clause evaluates to TRUE whenever the column is assigned a
value.
which pretty strongly implies that only columns affected by the insert will
trigger the If Update() test, but the test I just ran
Create Table Test
(A Int Not Null,
B Int Null,
C Int Null Default 0)
-- --
CREATE TRIGGER Test_Trigger1
ON dbo.Test
FOR INSERT, UPDATE
AS
If UPDATE (A) Print 'A was updated'
If UPDATE (B) Print ' B was updated'
If UPDATE (C) Print' C was updated'
GO
-- ---
Insert Test(a) Values(23)
Pretty much shows that not the case...
"CBretana" wrote:
> If an insert only inserts values into some of the tables columns, (Say the
> missing ones are nullable) then the if update(x)"on one of the nulleable
> columns that was not affected will be false... But I'm wondering what will
> happen if the missing column is NOT nulleable and has a default value... I
'll
> run a test...
> "JRStern" wrote:
>|||On Wed, 9 Mar 2005 16:25:04 -0800, "Alejandro Mesa"
<AlejandroMesa@.discussions.microsoft.com> wrote:
>See "CREATE TRIGGER" in BOL, it is explained there.
I *guess* that says it always fires in an insert trigger, which is
consistent with what I and CBretana are seeing.
Various "why ..." questions come to mind ...
Thanks for the replies.
J.
>AMB
>"JRStern" wrote:
>|||On Wed, 9 Mar 2005 16:41:02 -0800, "CBretana"
<cbretana@.areteIndNOSPAM.com> wrote:
>The BOL specifically says:
><<<<<<<<<<<<<<<<<<<<<<<<<<<<<
>The IF UPDATE (column_name) clause in the definition of a trigger can be
>used to determine if an INSERT or UPDATE statement affected a specific colu
mn
>in the table. The clause evaluates to TRUE whenever the column is assigned
a
>value.
Read on:
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
IF UPDATE will return the TRUE value in INSERT actions because the
columns have either explicit values or implicit (NULL) values
inserted.
Also includes explicit NULL values, it seems.
I was going to ask if this is ANSI or something, but I don't think
there is any ANSI standard at all on triggers.
Anyone know what Oracle does?
Just asking crazy questions at this point, it seems clear enough what
SQLServer is doing, though the "why" is still vague.
J.|||JR, I found the reference That Alex mentioned, and it is clear, and says
exactly what we experienced, but other references imply what we <wrongly>
thought would happen...
"JRStern" wrote:

> On Wed, 9 Mar 2005 16:25:04 -0800, "Alejandro Mesa"
> <AlejandroMesa@.discussions.microsoft.com> wrote:
> I *guess* that says it always fires in an insert trigger, which is
> consistent with what I and CBretana are seeing.
> Various "why ..." questions come to mind ...
> Thanks for the replies.
> J.
>
>|||JRStern wrote:
> On Wed, 9 Mar 2005 16:25:04 -0800, "Alejandro Mesa"
> <AlejandroMesa@.discussions.microsoft.com> wrote:
> I *guess* that says it always fires in an insert trigger, which is
> consistent with what I and CBretana are seeing.
> Various "why ..." questions come to mind ...
> Thanks for the replies.
>
Not sure myself. Tried even with an AFTER TRIGGER, but had the same
results. All I can think of is that it covers the case where a user
wants an insert/update trigger and needs to use the function (or
COLUMNS_UPDATED() which has the same behavior).
I agree with the intended behavior. All columns are affected on an
insert operation. The BOL examples should probably use the function on
an update trigger for clarity.
David Gugick
Imceda Software
www.imceda.com