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?
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.
answered 22 Sep '11, 15:58