After Trigger Help

[Patrick Jackman]

In an after trigger what would be the fastest and cleanest way to check if
one of 11 of 81 fields in a single row have been Updated?

For example if ElseIF were available I might do the following (but it's not)

IF Not Update(Field1)
ElseIF Not Update(Field2)
ElseIF Not Update(Field3)
...
ElseIF Not Update(Field11)
Else
-- none of 11 were updated

If one of these 11 fields are updated I need to update another table.

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-
Patrick Jackman
Vancouver, BC

[Tom Cooper]

If Update(Field1) Or Update(Field2) Or Update(Field3) Or ... Update(Field11)
Begin
-- do whatever, because at least one column was updated
End
Else
Begin
-- none of those columns was updated
End

However, please note that the Update function is true if the column was
referenced in the SET clause of the UPDATE that fired the trigger. Which
means it can be true even if the actual value was not changed. So, for
example, if field1 already contained the value 7 for a row with Key = 5 and
you do the update statement

Update <your table>
Set Field1 = 7
Where Key = 5;

then the trigger will fire and Update(Field1) will be true even though the
value of Field1 was not changed.

Also, as with all triggers, make sure your trigger either correctly handles
the case where one UPDATE statement updates multiple rows or your trigger
checks for and disallows multiple row updates.

Tom

"Patrick Jackman" <pjackman at wimsey no spam com> wrote in message
news:e0$B$QaWKH...@TK2MSFTNGP06.phx.gbl...

[Patrick Jackman]

Thanks Tom.

The SET issue is a problem then.

What about comparing the INSERTED And DELETED values?

If IsNull(Inserted.Field1, 0) <> IsNull(Deleted.Field1, 0) Or
IsNull(Inserted.Field2, 0) <> IsNull(Deleted.Field2, 0) etc...

Thanks for the multiple row heads up.

Patrick.

"Tom Cooper" <tomc...@comcast.net> wrote in message
news:Odl6LhaW...@TK2MSFTNGP04.phx.gbl...

[Erland Sommarskog]

Patrick Jackman (pjackman at wimsey no spam com) writes:
> What about comparing the INSERTED And DELETED values?
>
> If IsNull(Inserted.Field1, 0) <> IsNull(Deleted.Field1, 0) Or
> IsNull(Inserted.Field2, 0) <> IsNull(Deleted.Field2, 0) etc...
>
> Thanks for the multiple row heads up.

You seem to need another heads-up. It would not be IF, but a WHERE
clause:

FROM inserted i
JOIN deleted d ON i.keycol1 = d.keycol1
AND i.keycol2 = d.keycol2
...
WHERE (i.field1 <> d.field OR
i.field1 IS NOT NULL AND d.field1 IS NULL OR
i.field1 IS NULL AND d.field1 IS NOT NULL) OR
...

I've changed your isnull here, because I don't know what is in those
columns. It may be OK to equate NULL with 0, but it can also be flat
wrong.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Russell Fields]

Patrick,

The IF ... OR ... IF logic will not work for you. It needs to be part of
the filter criteria. E.g. assuming that ISNULL is valid for your data set:

WHERE IsNull(Inserted.Field1, 0) <> IsNull(Deleted.Field1, 0)
OR IsNull(Inserted.Field2, 0) <> IsNull(Deleted.Field2, 0) ... etc.

But, as Tom said, you need to able to handle many rows or explicitly reject
multi-row updates. Handling a multi-row update means that you need to have
a unchanging primary key, so that you can join the two tables. Like:

FROM inserted JOIN deleted
ON inserted.PK_col1 = deleted.PK_col1
AND inserted.PK_col2 = deleted.PC_col2

If the PK (or the unique key you join on) can change it values, you have no
way to match an inserted row with a deleted row. Of course, you can do
something to prevent multi-row updates, then the one row in inserted will
match the one row in deleted. Such as:

-- Reject a multi-row update
IF (SELECT COUNT(*) FROM inserted) <> 1
BEGIN
ROLLBACK TRANSACTION
RETURN
END
ELSE
-- Go ahead and do the compares.

Be sure to handle the transaction state in the code that did the UPDATE.

RLF

"Patrick Jackman" <pjackman at wimsey no spam com> wrote in message

news:eNws1%23aWKH...@TK2MSFTNGP05.phx.gbl...

> Thanks Tom.
>
> The SET issue is a problem then.
>
> What about comparing the INSERTED And DELETED values?
>

> If etc...

[Patrick Jackman]

Wow! That's really cool stuff Erland. I have so much to learn. Thanks again.

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CB4F3881...@127.0.0.1...

[Patrick Jackman]

Thank you Russell. You've given me some great stuff to work through. I
really appreciate that you've taken the time to share this information with
me.

"Russell Fields" <russel...@nomail.com> wrote in message
news:eEP5tXbW...@TK2MSFTNGP05.phx.gbl...