Why is this trigger firing?

We are using SQL Anywhere v12.01.3436 but have had this issue with version 10 also.

We have this trigger in place (I added the message statements to debug):

CREATE TRIGGER "customer_update_salesperson" after update of salesperson,     
       salesperson_employee_id order 2 on DBA.customer
       referencing old as old_name new as new_name
       for each row
begin
   message 'Trigger customer_update_salesperson fired' type warning to console;
   message 'old_name.customer='+cast(old_name.customer as varchar)+
       'new_name.customer='+cast(new_name.customer as varchar) type warning to console;
   message 'old_name.salesperson='+cast(old_name.salesperson as varchar)+
       'new_name.salesperson='+cast(new_name.salesperson as varchar) type warning to console;
   message 'old_name.salesperson_employee_id='+cast(old_name.salesperson_employee_id as
       varchar)+' new_name.salesperson_employee_id='+cast(new_name.salesperson_employee_id as 
       varchar) type warning to console;
   if old_name.salesperson = new_name.salesperson and old_name.salesperson_employee_id = 
         new_name.salesperson_employee_id then
       return
   end if;
   update in_invoice_header
      set modified_datetime = current timestamp
      where in_invoice_header.company = new_name.company
      and in_invoice_header.invoice_type in( 10,11 ) 
      and in_invoice_header.customer = new_name.customer;
end

The goal is to update records in table with new modified_datetime so that they will be sent to new salesperson via MobiLink.

This trigger is firing when I think it should not be.

Here is sample output from debug logic:

Trigger customer_update_salesperson fired
old_name.customer=58303 new_name.customer=58303
old_name.salesperson=28 new_name.salesperson=28
old_name.salesperson_employee_id=3033 new_name.salesperson_employee_id=3033

The old and the new salesperson, salesperson_employee_id columns are the same, so what would cause the trigger to fire?

Any ideas on this?

trigger

asked 22 Sep '11, 13:52

bgreiman
400●18●19●27
accept rate: 20%

edited 22 Sep '11, 14:21

Graeme Perrow
9.6k●3●79●124

Well, I agree with your expectation that this AFTER UPDATE row level trigger should not to fire here, at least if the old and new values are really identical (e.g. if it were strings, "a" and "A" could be compared as equal in a case-insensitive database, but the value would still be treated as changed, and I guess it would fire the trigger).

What happens when you change it to a general UPDATE of trigger (without a column list) and use a WHEN clause to encapsulate the desired action, e.g. like

for each row
when (old_name.salesperson <> new_name.salesperson
   or old_name.salesperson_employee_id <> 
    new_name.salesperson_employee_id)
begin
   -- your update
end;
...

(22 Sep '11, 14:38) Volker Barth

flat view

One Answer:

active answers oldest answers newest answers popular answers

I think it depends on how the UPDATE statement is written.

Logically, I would expect any invocation of "SET salesperson = n" to be treated as an update of the salesperson column, even if it is updating it to the same value.

permanent link

answered 22 Sep '11, 15:58

William Clardy
61●1●5
accept rate: 0%

Yes, but AFTER UPDATE row-level triggers are documented to only be called (with a few exceptions) when the old and new values differ.

That's different with BEFORE UPDATE triggers: They are always fired.

(22 Sep '11, 16:06) Volker Barth

Does the logic for AFTER UPDATE actually compare the old and new values or does it go by whether or not a value is being written to one of the listed columns?

The docs I have at hand (v.11) just say "the trigger is fired only if the value of any of the columns in column-list is changed by the UPDATE statement", which is slightly ambiguous.

(22 Sep '11, 16:48) William Clardy

Replies hidden

The v12.0.1 docs say the same, methinks. The "only when values differ" behaviour is stated indirectly by telling that BEFORE UPDATE triggers behave differently (yes, still ambiguous):

BEFORE UPDATE triggers fire any time an UPDATE occurs on a row, whether the new value differs from the old value. That is, if a column-list is specified for a BEFORE UPDATE trigger, the trigger fires if any of the columns in column-list appear in the SET clause of the UPDATE statement. If a column-list is specified for an AFTER UPDATE trigger, the trigger is fired only if the value of any of the columns in column-list is changed by the UPDATE statement.

I guess the exact behaviour has been discussed in the general newsgroup several times. IIRC, I took my information from there (or from Breck's great book!).

Another reference: This FAQ. - Though I get the impression the docs should be more precise here. A case for DCX?

(22 Sep '11, 17:00) Volker Barth

Thanks. It's always a good day when I learn something new.

(22 Sep '11, 17:54) William Clardy

...so most (all?) of my days have to be good days:)

(22 Sep '11, 17:57) Volker Barth

As long as it's something new.

Refreshing last week's lesson doesn't count...

(22 Sep '11, 18:11) William Clardy

showing 2 of 6 show all flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

trigger ×78

question asked: 22 Sep '11, 13:52

question was seen: 2,199 times

last updated: 22 Sep '11, 18:11

SAP SQL Anywhere

Why is this trigger firing?

Follow this question

Related questions