Question about Update() function in triggers

Question

Post reply

Question about Update() function in triggers

jobeth

SSC Enthusiast

Points: 102
More actions
May 21, 2010 at 12:47 pm

#94908

I have a very strange issue with the update() function. I have spent quite a while searching through various SQL Server documentation and have yet to find anything that addresses this -- so I'm appealing to the wisdom and experience of all of you experts who frequent this forum. 🙂
Here's the situation:
I'm rewriting some old triggers on a SQL 2000 server. In one of the update triggers, I have a scenario where I need to use the update() function within a case statement in order to determine the value to which a column will be set. Here's a sample of one of the case statements:
IdentifiedDateTime =
case
when update(DocketNumber) or update(MPINumber)
then CURRENT_TIMESTAMP
else inserted.IdentifiedDateTime
end
I'm able to create the trigger with no errors. However, when a try to run an update which fires this trigger, I get this error:
Syntax error converting the nvarchar value 'DocketNumber' to a column of data type int
I'm not sure why it's trying to convert the column name to an int. I've rewritten similar triggers using this same syntax on a 2008 server and did not run into this problem. So this seems to be an issue with SQL 2000 and 2008 handling this situation differently. In BOL for SQL 2000, the examples all show Update() being used as a condition in an IF statement. Yet, it also says "UPDATE(column) can be used anywhere inside the body of the trigger." Since it returns a boolean, it seems a case statement would be logical place to use it.
Has anyone out there ever run into this problem or have any explanation for what might be going on?
Thanks!
JoBeth

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110209 More actions · Answer 1

Check your error message.

Which line this error happens in?

Is it the same line as the one where UPDATE() is located?

_____________
Code for TallyGenerator

jobeth SSC Enthusiast Points: 102 More actions · Answer 2

Yes, I'm certain that's the line that's causing the error. I did a lot of troubleshooting by commenting out parts of the code and running the trigger just to be sure -- because the error didn't seem to make sense. If I comment out that line ("Update(DocketNumber)"), I then get the same error on the next line, except that in the error message, 'DocketNumber' is replaced by 'MPINumber' -- because the code on the next line reads "Update(MPINumber)". (In the sample code I posted, the two update() functions are on the same line, but in the actual trigger, they're on different lines.) So I feel 100% certain about what's causing the error. I just don't understand why....

Thanks,

JoBeth

Sergiy SSC Guru Points: 110209 More actions · Answer 3

Try to replace DocketNumber in UPDATE() with another column name, not mentioned in the trigger at all.

The error is so odd you must be looking at the wrong place.

Can you copy-paste the whole trigger?

_____________
Code for TallyGenerator

jobeth SSC Enthusiast Points: 102 More actions · Answer 4

I understand why you doubt me. The error certainly is very strange. But the error is definitely being caused by the update function. As a further test, I changed update(DocketNumber) to update(foobar), and as expected, I got this error:

Syntax error converting the nvarchar value 'foobar' to a column of data type int.

It seems that SQL is interpreting what's inside the parentheses as a literal string rather than as a column name. And for some reason, it seems to be expecting an integer value inside the parentheses rather than a string. Yet, as an experiment, I also tried putting an integer there (2), and then I got a syntax error when I tried to create the trigger:

Incorrect syntax near '2'.

Thanks for trying to help solve the mystery!

jobeth SSC Enthusiast Points: 102 More actions · Answer 5

Sorry -- I forgot to say that, unfortunately, I believe my employer's confidentiality policies prohibit me from posting the trigger in its entirety.

Thanks,

JoBeth

Sergiy SSC Guru Points: 110209 More actions · Answer 6

OK, I opened BOL for CREATE TRIGGER.

Syntax
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ {FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [...n ]
}
}

Apparently, there is no command "UPDATE (column)", there is "IF UPDATE(column)".

It may be used anywhere in the body of the trigger, but it must be "IF UPDATE()".

Rewrite the trigger to the correct syntax and it should be fine.

_____________
Code for TallyGenerator

jobeth SSC Enthusiast Points: 102 More actions · Answer 7

Yes, I did notice that. Oddly, in the SQL 2008 BOL, Update() is not even shown in the create trigger syntax. Yet, in 2008, I have used update() in case statements many times with no problems. It's only in 2000 that it gives me a problem. It seems that the allowable syntax for the update() function became more flexible in 2008 (and possibly 2005 -- I haven't tried it there yet) -- but I can't find any documentation that specifies that. BOL for both 2000 and 2008 contains a statement that reads "UPDATE(column) can be used anywhere inside the body of a Transact-SQL trigger. "

I was just hoping that maybe someone could confirm that this change was made and/or point me to some documentation that confirms it. I think maybe I need to address the question to Microsoft.

Thanks for your efforts, Sergiy!

JoBeth

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 8

From the same SQL Server 2000 BOL article but further down...

IF UPDATE (column)
Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations. More than one column can be specified. Because the table name is specified in the ON clause, do not include the table name before the column name in an IF UPDATE clause. To test for an INSERT or UPDATE action for more than one column, specify a separate UPDATE(column) clause following the first one. IF UPDATE will return the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.
Note The IF UPDATE (column) clause functions identically to an IF, IF...ELSE or WHILE statement and can use the BEGIN...END block. For more information, see Control-of-Flow Language.
[font="Arial Black"]UPDATE(column) can be used anywhere inside the body of the trigger.[/font]

I've not actually written a trigger that uses UPDATE(column) in a CASE statement so I can't actually say that I've seen using it in a case statement in 2k actually works, but BOL seems to say it will.

With that in mind, I'd also have to agree that either the fault lies somewhere else or you've found a bug. Which Service Pack is your 2k installation at?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jobeth SSC Enthusiast Points: 102 More actions · Answer 9

Thanks for your input, Jeff. We have SQL Server 2000 SP4 installed on the server in question. Yes, the statement you pointed out in BOL does seem to indicate that it should work. However, I feel absolutely certain about where this error is coming from (because of the results of various testing that I've done.) Therefore, I tend to agree that it must be a bug that Microsoft fixed in later versions. I just wish that I could find some documentation to back that up.

Thanks again!

JoBeth