trigger problem - "if update" on text type column not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Keith G Hicks - 30 Dec 2008 14:35 GMT

I created a trigger to post data to a history table if the value of a
particular column has changed in its parent table. In the beginning of the
trigger I have:

IF UPDATE (Notes)
BEGIN
...
...
END

It's an INSTEAD OF UPDATE trigger and the column "Notes" is a TEXT data
type.

This is all running in SQL 2005. I cannot change the Notes column to
VARCHAR(MAX) at this time.

My trigger code after the IF UPDATE seems to run even if the Notes column
has not been modified. Can anyone explain to me why this is happening and
what to do to solve it?

Thanks,

Keith

Reply to this Message

Keith G Hicks - 30 Dec 2008 14:48 GMT

I think I know what's wrong. My Access ADP must be trying to post every
column to the table whether or not that column has changed. I just tried a
manual update of the table in a query window. First I did this:

UPDATE myTable SET Address = '123 Main' WHERE ID = 343

When I ran that, my trigger did what I want it to do. But when I ran this:

UPDATE myTable SET Notes = 'some notes' WHERE ID = 343

The value in Notes was already 'some notes' before I ran the statement
above. So it really was not changed but the code in my trigger ran as if it
had. Is it true that the UPDATE test in a trigger does not actually do a
comparison of the data in the INSERTED and DELTED tables? This does not
really clarify that:
http://msdn.microsoft.com/en-us/library/ms187326(SQL.90).aspx

I have a feeling I'm going to have to put the INSERTED and DELETED tables
into cursors and compare the Notes values row by row to do this properly.

Any ideas on this?

>I created a trigger to post data to a history table if the value of a
>particular column has changed in its parent table. In the beginning of the
[quoted text clipped - 19 lines]
>
> Keith

Reply to this Message

Vern Rabe - 30 Dec 2008 15:29 GMT

Keith:

Your are correct that the IF UPDATE does not compare before and after
values. It evaluates to true if the specified column is included in the
UPDATE statement. The common solution is to us the inserted and deleted
tables to identify the rows for which the value in that column actually
changed, but there is usually no need to use a cursor. A set based solution
would be best.

HTH
Vern Rabe

> I think I know what's wrong. My Access ADP must be trying to post every
> column to the table whether or not that column has changed. I just tried a
[quoted text clipped - 41 lines]
> >
> > Keith

Reply to this Message

Keith G Hicks - 30 Dec 2008 17:58 GMT

I know I should avoid cursors but at first I wasn't sure if I could do it
without so it's in there now. Sometimes I fall back on them too quickly. And
they usually only update one row at a time so the cursor will have only 1
row in it normally. Speed should not be an issue in this case. I'll
definitley work on rewriting it at some point but for now it should be ok.
Thanks very much for the info.

> Keith:
>
[quoted text clipped - 60 lines]
>> >
>> > Keith

Reply to this Message

John Bell - 30 Dec 2008 15:38 GMT

>I think I know what's wrong. My Access ADP must be trying to post every
>column to the table whether or not that column has changed. I just tried a
[quoted text clipped - 41 lines]
>>
>> Keith

Hi Keith

You could use SQL Profiler to see what statements the Access front end is
executing. If the column is updated with the same value it is still an
update. You may also want to check @@ROWCOUNT at the start of the trigger to
see if anything has been updated.

To be able to detect a change to the text column you would need to cast them
to varchar(max) e.g.

CREATE TABLE t1 ( id int not null identity, textval text )

INSERT INTO t1 ( textval )
SELECT 'a'
UNION ALL SELECT 'b'

CREATE TRIGGER t1$insteadupdate ON t1
INSTEAD OF UPDATE
AS
IF @@ROWCOUNT = 0 RETURN

IF EXISTS (
SELECT *
from inserted i
JOIN deleted d on d.id = i.id AND CAST(d.textval AS varchar(max))<>
CAST(i.textval AS varchar(max))
)
SELECT d.id, d.textval, i.textval
from inserted i
JOIN deleted d on d.id = i.id AND CAST(d.textval AS varchar(max))<>
CAST(i.textval AS varchar(max))

UPDATE t1
SET textval = 'z'
WHERE 1 = 0

UPDATE t1
SET textval = 'a'
WHERE id = 1

UPDATE t1
SET textval = 'c'
WHERE id = 1

John

Reply to this Message

Keith G Hicks - 30 Dec 2008 17:55 GMT

I forget to use the profiler sometimes. That's a good suggestion. I'll check
it out.

>>I think I know what's wrong. My Access ADP must be trying to post every
>>column to the table whether or not that column has changed. I just tried a
[quoted text clipped - 88 lines]
>
> John

Reply to this Message

Keith G Hicks - 30 Dec 2008 19:00 GMT

Well this was not expected. The ADP seems to create an update statement only
for the columns that I change in teh front end form. BUT it always tries to
update TEXT column types regardless of whether or not they have changed!

So I'm confused now. I always thought that UPDATE(col1)... was testing for a
change in the value not just whether or not it was inlcuded in the update
statement. If that's the case, what's the point? It sure seems useless
otherwise. For example, I have a Delphi program running right now that has a
form with about 40 text boxes on it. It's unbound so I'm doing the update to
the table manually by passing the values on the form to a stored procedure.
I'm passing all values, not just the ones that have been changed. So in that
situation, if Col1 changed but Col2 did not and I still pass Col2 to the
procedure and there's a trigger that tests to see if Col2 was updated then
it will ALWAYS return true. It would seem that the only way to do this
exactly right would be to create the update statement dynamically depending
on what was changed. Otherwise the UPDATE function is useless. Am I way off
on this? Maybe I'm missing something here.

Keith

>I forget to use the profiler sometimes. That's a good suggestion. I'll
>check it out.
[quoted text clipped - 92 lines]
>>
>> John

Reply to this Message

Hugo Kornelis - 30 Dec 2008 21:42 GMT

>So I'm confused now. I always thought that UPDATE(col1)... was testing for a
>change in the value not just whether or not it was inlcuded in the update
>statement.

Hi Keith,

That would be impossible. Remember that a trigger fires once per
statement execution, not once per row affected. So if a single UPDATE
affects 5 rows, and one of the columns in the SET clause is really
changed for 2 of them and "changed" to its original value for the other
3, what would you expect IF UPDATE(...) to do for that column?

Basically, IF UPDATE(col) tests is a column is used in the SET clause.

> If that's the case, what's the point?

I have built several triggers where large parts of the code can be
skipped if I know that a column is not in the SET clause of the UPDATE
statement. If it is, I have to do various things (sometimes including
testing for "real" changes, sometimes not), but if it isn't I can skip
that becuase I know for sure that there were no changes to that column.

Another use is to test for columns that may never be changed, not even
by users with elevated privileges:
IF UPDATE(ImmutableColumn)
BEGIN;
Slap user;
ROLLBACK TRANSACTION;
RETURN;
END;

Signature

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Reply to this Message

Keith G Hicks - 30 Dec 2008 21:44 GMT

That makes sense. Thanks for the info.

>>So I'm confused now. I always thought that UPDATE(col1)... was testing for
>>a
[quoted text clipped - 27 lines]
> RETURN;
> END;

Reply to this Message