Inconsistent behaviour of Sybase SQL anywhere update column trigger

[former_member329524]

Hi, all

I am not sure where to post this issue, since I could not find a forum for Sybase SQL anywhere.

The problem is as follows:

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

[CobyKako · Accepted solution]

Hello Arcady,

You could post your SQL Anywhere question here:

Thanks

Jacob

[former_member329524 · Accepted solution]

Thak you

I reposted it there.

[Former Member]

Put the following line at the beginning of your trigger...

if @@rowcount = 0 return

[former_member329524]

Thank you,

I know how to fix it in code, the question is if this behaviour is by design.

I have numerous triggers which are built in this exact way and I need to know if there is something global, which can be done.

[Former Member]

If this were a sql server question, then I could tell you that this is by design.  A trigger will be executed regardless of the number of rows affected (including zero) and regardless of whether any column in any of the affected rows was changed.  I will also point out that PB was designed to work within this design by the inclusion of the [where clause for update/delete] option in the update properties of the datawindow.  The idea is that you avoid actual row updates (in the table) where nothing has actually changed (and it also supports collision avoidance).

Based on the history, I would assume that Sybase works exactly the same.