on 2011 Sep 22 2:52 PM
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?
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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?
As long as it's something new.
Refreshing last week's lesson doesn't count...
User | Count |
---|---|
52 | |
10 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.