MariaDB 12 Triggers

There’s new SQL syntax in MariaDB 12. MariaDB’s manual doesn’t document it all, so I will try. First in this series is: the Oracle-style CREATE TRIGGER … event with OR, and the standard-style table information_schema.triggered_update_columns.

event with OR

CREATE TRIGGER trigger_name
BEFORE|AFTER
INSERT|UPDATE [OF column-list]|DELETE
[OR INSERT|UPDATE [OF column-list]|DELETE ...]
ON table_name FOR EACH ROW statement_text;

The OR is new. At the time I’m writing this it’s not yet in the MariaDB manual but the feature request for it is closed, MariaDB 12.0+ supports it.

DB2 and SQL Server and PostgreSQL support something similar, but what I think is important for MariaDB is that Oracle supports it. (The feature request is part of MariaDB’s “Oracle compatibility project”.)

So BEFORE|AFTER INSERT OR UPDATE etc. is a non-standard extension that increases Oracle compatibility although sql_mode=’oracle’ is not required.

Gripe: the feature request has the term “multiple events” but that’s wrong, INSERT OR UPDATE is only one trigger event. I’m calling it “event with OR” but there’s no good standard term.

Examples:

CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON t
                 FOR EACH ROW SET @a = @a + 1;
CREATE TRIGGER t AFTER UPDATE OF s1 OR INSERT ON t
                 FOR EACH ROW SET @a = @a + 1;

The effect is obvious: if the event happens, the trigger statement should happen.

The advantage is obvious: you don’t need to create nearly-duplicate triggers when the table and the statement are the same and the only difference is the event. If there are many triggers, the maintenance — and the understanding of which ones get activated before other ones — could become confusing.

Aside: Oracle takes this reduce-trigger-numbers idea to extremes by also supporting compound triggers.

event with OR, if inserting|updating|deleting

The trigger’s statement may contain the words INSERTING or UPDATING or DELETING. These words are “conditional predicates”, that is, they appear whenever a true|false decision may appear, such as in IF INSERTING, CASE WHEN UPDATING, WHILE DELETING. A conditional predicate is true only for the relevant part of a trigger event, for example if the trigger event is INSERT OR DELETE and the statement is INSERT then INSERTING is true, DELETING is false, UPDATING is illegal syntax.

Example:

CREATE TRIGGER tm
BEFORE INSERT OR UPDATE OF s1 OR DELETE ON t
FOR EACH ROW
BEGIN
  CASE WHEN INSERTING THEN SET @a=0;
  ELSE SET @a = 1; END CASE;
  IF UPDATING OR INSERTING THEN
  SET @a = 2; END IF;
  WHILE NOT DELETING AND @a <> 3 DO
    SET @a = 3;
  END WHILE;
END;

MariaDB does not support the Oracle-style conditional predicate UPDATING(‘column_name’).

MariaDB does not support using a conditional predicate as an ordinary operand. For example, “SET declared_variable_name = INSERTING;” is legal but the result is zero.

Do not assume that UPDATING cannot be legal for an INSERT or DELETE statement, because MariaDB supports INSERT … ON DUPLICATE KEY UPDATE and supports foreign keys with ON DELETE SET NULL.

There is a new error ER_INCOMPATIBLE_EVENT_FLAG which pops up if a conditional predicate doesn’t correspond to anything in the trigger event. For example,

CREATE TRIGGER tx BEFORE UPDATE OR INSERT ON t
                  FOR EACH ROW
                  IF DELETING THEN SET @a = 0; END IF;

causes Error 4211 (HY000) Event flag ‘DELETING’ in the condition expression is not compatible with the trigger event type ‘INSERT,UPDATE’.

Gripe: I have no idea why this is called an event flag, or why treat DELETING as an error rather than treat it as false, or why the term isn’t just “trigger event”, or why the SQLSTATE class is HY for something that’s being treated as a syntax error. I don’t see this restriction mentioned in Oracle’s description of conditional predicates.

event with OR, recommendations

“UPDATE OF column1 OR UPDATE OF column2” is unnecessary — “UPDATE OF column1, column2” does the same thing and is compatible with MariaDB 11.

The clause order doesn’t have to be INSERT before UPDATE before DELETE, but there might as well be some convention. Choose whatever the MariaDB manual uses for an illustration when the MariaDB manual describes this feature.

If you care about compatibility with the standard or with MySQL or with earlier versions of MariaDB, instead of caring about compatibility with Oracle, you should continue to use nearly-duplicate triggers.

MariaDB supports duplication such as INSERT OR INSERT, which it will ignore, so it’s your responsibility to avoid such an error.

INSERTING and UPDATING and DELETING are not reserved words. Therefore if the trigger body contains a declared-variable declaration like

DECLARE INSERTING INT;

then later IF INSERTING will be true or false depending on the variable value, INSERTING is not a conditional predicate in this context. So check that you never declared such variables.

Beware of these bugs:
MDEV-37711 Multiple-event triggers may fire in wrong order
MDEV-38009 Flaws with CREATE TRIGGER with ORed events including UPDATE OF.

event with OR in information_schema

After you say CREATE TRIGGER … event with OR, you can see it in information_schema:

SELECT event_object_table, event_manipulation
FROM information_schema.triggers;

The event_manipulation column can now be a comma-separated list such as INSERT,UPDATE,DELETE — not necessarily in the same order that you used in the CREATE TRIGGER statement.

Gripe: Why comma-delimited? The separator in the CREATE TRIGGER statement was “OR” not “,” and I think this isn’t Oracle-like, the examples I’ve seen for

SELECT triggering_event FROM all_triggers

look like ‘INSERT OR UPDATE’ not ‘INSERT,UPDATE’. e.g. the “Sample result” on this Dataedo page and the “Creating a sample trigger:” results on this stackoverflow page.

Anyway, whether or not you like it, you must change any of your existing SQL code that has anything like

WHERE event_manipulation = 'INSERT'

to

WHERE event_manipulation LIKE '%INSERT%'

This is good enough because even if the event was “UPDATE OF inserted_column” that won’t cause a false positive, for a reason that I’ll explain in the next section.

information_schema.triggered_update_columns

Unlike event with OR, this new feature is documented and standard. The information that’s missing in information_schema.triggers.event_manipulation is: what column? Even if you say UPDATE OF, the column name won’t be there because the standard says the only possible values are ‘INSERT’ | ‘UPDATE’ | ‘DELETE’. Luckily you don’t often need to know, but it could happen.

You can get something by looking at the SHOW of each trigger. For example (here I use an ocelotgui feature to make it look simple) (but it’s not simple):

SELECT `SQL Original Statement`
 FROM (SHOW CREATE TRIGGER t) AS shower
 WHERE `SQL Original Statement`
       regexp '.*\\s*before|after\\s.*update.*of.\\ss2\\s|,*\\son\\.*'
       = 1;
although this fails if there are newlines or the important words are inside comments or strings. Repeat for every trigger.

So, enter triggered_update_columns. The column names are listed in the MariaDB 12.2 manual.

Gripe: Why triggered? The update is “triggering” not “triggered”. I claimed this name is silly until I Peter Gulutzan the standards expert got a polite reminder from Sergei Golubchik the MariaDB expert that that’s the name in the standard. Oops.

So now, if I want to see what columns are in CREATE TRIGGER UPDATE OF clauses, I SELECT event_object_column FROM information_schema.triggered_update_columns.

Big improvement.

The standard says the criterion for triggered_update_columns is: “Identify the columns in this catalog that are identified by the explicit UPDATE trigger event columns of a trigger defined in this catalog that are accessible to a given user or role.” A column is explicit if it’s mentioned in the trigger event. If the trigger was made with

CREATE TRIGGER ... BEFORE|AFTER UPDATE OF s1 ON t ...

then s1 is explicit, so it gets a row in triggered_update_columns. If the trigger was made with

CREATE TRIGGER ... BEFORE|AFTER UPDATE ON t ...

then all columns in t are implicit, so do not get rows in triggered_update_columns although of course any changes to them cause trigger activation.

information_schema.triggered_update_columns example

You can look for either explicit or implicit columns with left joins.

Suppose I want to see all the triggers on table t1 column s2. To make it simple I assume there is only one database. This will do it:

SELECT a.trigger_name,
       a.event_manipulation,
       a.event_object_table,
       b.event_object_table,
       b.event_object_column
 FROM information_schema.triggers a
LEFT JOIN information_schema.triggered_update_columns b
ON a.trigger_name = b.trigger_name
WHERE a.event_manipulation LIKE '%UPDATE%'
AND a.event_object_table = 't1'
AND b.event_object_column = 's2'
    OR b.event_object_column IS NULL;
Suppose I get two rows:

The first row is a match because there’s a TRIGGERED_UPDATE_COLUMNS row with ‘s2’ (obviously I must have earlier said CREATE TRIGGER t1u BEFORE|AFTER UPDATE OF s2 ON t1 …). The second row is a match because there’s no TRIGGERED_UPDATE_COLUMNS row so I see NULL (obviously I must have earlier said CREATE TRIGGER t1t BEFORE|AFTER INSERT OR UPDATE ON t1 …) — thus I know that all columns are affected.

ocelotgui 2.6

Thinking about incompatibility with MariaDB 11 made me think about this: That is, while you’re typing, the GUI shows the possible choices for the next word as a pulldown menu. I suppose every GUI has that. But I don’t think every GUI has a tooltip saying what MySQL or MariaDB version first supported that word in that place. This is a tentative feature, it’s in the source code but not in the executables of the recently-released ocelotgui 2.6. The same is true for other features described above. As always download of the source code or the release is possible from github.