triggers for UPDATE

I can't quite work this out: SQL server trigger for update, and I
specify a single column e.g. IF UPDATE ( statusCode ), does it return
true if the old value and the new value are the same?

Specifically, I use execute method of ADODB.Connection object, and issue
an update table statement, but i always update all (seven) columns of
the table, even if user has only changed one in the front end web form,
does the IF UPDATE() statement return true for all the columns?

Do i need to also check if inserted.statusCode <> deleted.statusCode?

Ben

Re: triggers for UPDATE

ben h wrote:
> I can't quite work this out: SQL server trigger for update, and I
> specify a single column e.g. IF UPDATE ( statusCode ), does it return
> true if the old value and the new value are the same?
>
> Specifically, I use execute method of ADODB.Connection object, and
> issue an update table statement, but i always update all (seven)
> columns of the table, even if user has only changed one in the front
> end web form, does the IF UPDATE() statement return true for all the
> columns?
> Do i need to also check if inserted.statusCode <> deleted.statusCode?
>
Yes. This is fairly easy for you to test. Run this script in Query Analyzer:

CREATE TABLE dbo.a
(
apk int NOT NULL,
somevalue int NOT NULL,
CONSTRAINT PK_a
PRIMARY KEY CLUSTERED (apk)
)
go
CREATE TRIGGER dbo.TR_a
ON dbo.a
AFTER UPDATE AS
IF UPDATE(somevalue)
PRINT 'somevalue was updated'
else
PRINT 'somevalue was not updated'
go
INSERT INTO a ( apk, somevalue )
VALUES ( 1, 1 )
go
INSERT INTO a ( apk, somevalue )
VALUES ( 2, 2 )
go
INSERT INTO a ( apk, somevalue )
VALUES ( 3, 3 )
go
update a set somevalue=somevalue where apk=3


Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"