|
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. 1
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
1
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):
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
1
As long as it's something new. Refreshing last week's lesson doesn't count...
(22 Sep '11, 18:11)
William Clardy
|
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