Inconsistent behaviour of Sybase SQL anywhere update column trigger

[former_member329524]

Hi, all

I have the following problem in Sybase SQL anywhere (appears to be in all versions)

The definition of the update column trigger in help is this:

//--------------------------

UPDATE OF column-list

Invokes the trigger whenever a row of the associated table is updated such that a column in the column-list is modified.

//-----------------------------
However, there are cases when the triggers are launched even when the columns are not modified (ie the same exact value is in the both OLD and NEW row)

This happens on BEFORE triggers, never saw it happening on AFTER.

Ways to replicate the problem:

1. Define a before trigger, which fires on update of a specific column in any table.

2. Run an sql which says "update table1 set column1 = column1 where ... "

3. You will see that trigger fires, even though it is not supposed to.

Is this an expected behaviour on "BEFORE" triggers?

Arcady

[JasonHinsperger]

Yes, this is expected in a before trigger.  The server really has no idea if you are actually updating to the same value or not because you can change the updated column value to whatever you want in the before trigger.

--Jason

[former_member329524]

So, will @@rowcount = 0 test fix the problem?

Or will there, actually, be a need to test each value for being changed?

[JasonHinsperger]

A before trigger fires before any changes are actually made to the database, so @@rowcount will not help.  You will need to test the value in the trigger if you want to be sure whether or not it is being changed.

[reimer_pods]

Just to state it explicitly: a "before update" trigger fires every time an UPDATE command is issued on the table the trigger is define for, without checking for modified columns.

An "after update" trigger will only be fired if SQLA has really updated some values. As a consequence your "dummy update" (2.) won't call an "after update" trigger.

Cf. the docs http://dcx.sybase.com/index.html#1201/en/dbreference/create-trigger-statement.html