Details
-
New Feature
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
SQL Standard allows to specify the list of columns in the UPDATE triggers, for example:
create trigger trg before update of col1, col2 on t1 for each row |
This of col1, col2 syntax means the trigger will fire only when one of these columns will be updated
Such behavior aligned with the ones used by PostgreSQL and Oracle RDBMS.
Quote from Oracle RDBMS Documentation:
Specify UPDATE if you want the database to fire the trigger whenever an UPDATE statement changes a value in one of the columns specified after OF. If you omit OF, then the database fires the trigger whenever an UPDATE statement changes a value in any column of the table or nested table.
For an UPDATE trigger, you can specify object type, varray, and REF columns after OF to indicate that the trigger should be fired whenever an UPDATE statement changes a value in one of the columns. However, you cannot change the values of these columns in the body of the trigger itself.
Quote from PostgreSQL Documentation
For UPDATE events, it is possible to specify a list of columns using this syntax:
UPDATE OF column_name1 [, column_name2 ... ]
The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command or if one of the listed columns is a generated column that depends on a column that is the target of the UPDATE.
What these descriptions have in common is that a trigger is called when an UPDATE operation is performed on any of columns listed at the clause 'OF'. Handling the clause OF in MariaDB in the same way as it is preformed by PostgreSQL and Oracle seems like to be the right decision.
Just for case, the below is the quote from SQL Standard 2016 regarding handling a trigger:
When a state change SCj arises in SSC, one or more triggers are activated by SCj. A trigger TR is activated by SCj if and only if the subject table of TR is the subject table of SCj, the trigger event of TR is the trigger event of SCj, and the set of column names listed in the trigger column list of TR is equivalent to the set of column names listed in SCj.
Unfortunately, the meaning of the phrase 'is equivalent' is not explained anywhere around the paragraph '4.44 Triggers', so it's right idea to follow the approaches implemented by PostgreSQL and Oracle.