Executing UPDATE Triggers with a Specified Column List
The syntax of the UPDATE trigger allows you to specify a list of columns that the trigger must be executed on. The UPDATE statement or the Write_record_ex API function may modify a group of columns at once. The SQL standard does not specify what relation the column list from the trigger definition will have with the group of modified columns.
602SQL executed the trigger when both sets were equal up to version 7. Since version 8.0, this possibility has been maintained, but there is also an alternative method to execute the trigger when both sets have a non-empty intersection (at least one of the columns in the list is modified by the statement). The trigger is executed even when not all columns from the list are modified, or when some other columns are modified.
You can choose the rules for trigger execution in following ways:
- Specify the = character or the ANY word following the UPDATE OF phrase. The = character sets the trigger to be executed only when the two sets are identical. ANY sets the trigger to be executed when any of the listed columns are changed.
- Set the compatibility attribute SQLOPT_COL_LIST_EQUAL (32768) using the Set_sql_option function or globally using the Runtime parameters dialog on the SQL compatibility tab.
Example:
TRIGGER `Actual_order` AFTER UPDATE OF ANY done,cancel_dat,cancelation,refund_date ON `Order_header`
/****************************************************/
// if any column modified, write the actual timestamp to the order
REFERENCING OLD AS `old` NEW AS `new`
FOR EACH ROW
BEGIN
IF (old.done<>new.done) OR (old.cancel_dat<>new.cancel_dat)
OR (old.cancelation<>new.cancelation) OR (old.refund_date<>new.refund_date)
THEN SET new.act_date=CURRENT_TIMESTAMP;
END IF;
END
