cancel
Showing results for 
Search instead for 
Did you mean: 

Why is this trigger firing?

bgreiman
Participant
2,474

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?

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

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.

VolkerBarth
Contributor

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.

Former Member
0 Kudos

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.

VolkerBarth
Contributor

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?

Former Member
0 Kudos

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

VolkerBarth
Contributor
0 Kudos

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

Former Member

As long as it's something new.

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