sql server programming: UPDATE(column) function in triggers -- sql server programming

UPDATE(column) function in triggers

ElitEDooM
11/16/2004 10:02:50 PM

sql server programming:

Hello,

I've found interesting situation with UPDATE(column) function inside
triggers
Here is an example of code:

--
SET NOCOUNT ON

CREATE TABLE [test] (
[id] [int] NOT NULL ,
[col1] [int] NOT NULL ,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TRIGGER [trig_test] ON [dbo].[test]
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT=0 RETURN
SET NOCOUNT ON

DECLARE @msg AS VARCHAR(100)

IF UPDATE(col1) SET @msg='changed' ELSE SET @msg='not changed'

PRINT @msg
GO

INSERT INTO test(id, col1) VALUES (1,1)

DROP TABLE test
--

this example PRINT message (from inside the trigger) when we try to
modify table 'test'.

Let's modify the trigger like that:

--
CREATE TRIGGER [trig_test] ON [dbo].[test]
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT=0 RETURN
SET NOCOUNT ON

DECLARE @msg AS VARCHAR(100)

--IF UPDATE(col1) SET @msg='changed' ELSE SET @msg='not changed'
SET @msg=CASE WHEN UPDATE(col1) THEN 'changed' ELSE 'not changed' END

PRINT @msg
GO
--

now any modification of the table 'test' raises sql server error:
Server: Msg 245, Level 16, State 1, Procedure trig_test, Line 11
Syntax error converting the nvarchar value 'col1' to a column of data
type int.

I've checked google but didn't find similar example of using
UPDATE(column) function not in IF clause but in 'CASE WHEN'. What I'm
doing wrong?
The problem is, that inside the trigger I need to call UDF with several
columns of the 'inserted' table and with flags, indicating if those
columns were changed. For example:

DECLARE @msg VARCHAR(100)

SELECT TOP 1 @msg=dbo.function_name(col1, @is_col1_changed, col2,...)
FROM inserted
WHERE @msg IS NULL

I wish to put CASE WHEN UPDATE(col1) THEN 1 ELSE 0 END into parameter
@is_col1_changed, but this solution produce error because of 'CASE WHEN'
use with UPDATE(col1) :(

To use flag variables for every columns (using IF UPDATE(col) SET
@flag... ) and then passing to UDF seems annoying

Re: UPDATE(column) function in triggers

Steve Kass
11/17/2004 1:30:01 AM

It looks like sloppy parsing to me. UPDATE(column name) is only
documented in IF UPDATE, and it works there and after WHILE (WHILE
UPDATE() isn't too useful). It turns out that if you hide the UPDATE()
function in a CASE expression, not only do you get the odd error you
discovered, in addition, the argument to UPDATE() is not checked to see
if it's a real column when the trigger is created. So long as it's a
valid identifier, the trigger is created successfully. The check that
it's a valid column in the table does happen when you use UPDATE() after
IF or WHILE.

Here's what seems to be going on (see the repro below). When a trigger
is created that uses UPDATE, the column name is supposed to be replaced
internally by the column's colid value from syscolumns. When the
trigger is run, an internal UPDATE function is called, and that function
expects an integer parameter. Someone forgot to do this for CASE WHEN
UPDATE(). Neither the check for a valid column nor the change from
column name to colid takes place, but when the trigger fires, the
internal UPDATE function still gets called, and its parameter is still
the column name.

Here's a repro that shows this happening. The error won't occur if the
column name (existing or not) passed to UPDATE can be converted to an
integer, and the results here are consistent with the explanation above.

set nocount on
go

create table t (
[2] nvarchar(20),
j int
)
go

create trigger trig on t
for update
as
if update([2])
print '[2] updated (if)'
else print '[2] not updated (if)'

print case when update([2])
then '[2] updated (case)'
else '[2] not updated (case)' end

print case when update([1])
then '[1] updated (case)'
else '[1] not updated (case)' end
go

insert into t values (1,1)
update t set j = 2
go

drop table t
go

Steve Kass
Drew University

[quoted text, click to view]

Re: UPDATE(column) function in triggers

Uri Dimant
11/17/2004 8:30:40 AM

Hi
It is invalid syntax.
Why not just insert some value into Audit table and then select ro check
whether or not the column was update or not?

Also take a look at this example that posted a while ago by Anith
USE Northwind
DROP TRIGGER tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1))))
PRINT 'Columns 3, 5 and 9 updated'
GO

UPDATE Customers
SET ContactName=ContactName,
Address=Address,
Country=Country
GO

[quoted text, click to view]

Re: UPDATE(column) function in triggers

ElitEDooM
11/17/2004 10:58:39 AM

Thank you Steve a lot, your description makes me clear now

[quoted text, click to view]

Re: UPDATE(column) function in triggers

ElitEDooM
11/17/2004 11:03:02 AM

Yes, but I'm trying to avoid using of COLUMNS_UPDATED(). The fact, that
it depends of the physical order of the columns in table frightening me ;)

[quoted text, click to view]

RE: UPDATE(column) function in CASE clause in side triggers (work-around)

Wayne Erfling
5/23/2007 8:26:46 AM

For others who find this old thread, the "easy work-around is" to just delcare a variable:

DECLARE @ItWasUpdated bit
IF UPDATE(field)
SET @ItWasUpdated = 1
ELSE
SET @ItWasUpdate = 0

=== SQL statement === CASE @ItWasUpdated = 1 THEN === stuff ===
ELSE === other stuff === END

From http://www.developmentnow.com/g/113_2004_11_0_0_436044/UPDATEcolumn-function-in-triggers.htm

Posted via DevelopmentNow.com Groups

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.