Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.0(EOL), 12.1
-
None
Description
In MariaDB 12.0+, it is now possible to create triggers that fire on multiple events (MDEV-10164). However, such multi-event triggers don't always fire in the expected order, if the table also has other triggers for the same event/timing pair.
In the example below, three simple triggers are created for a table, in this order:
- tr1: BEFORE INSERT
- tr2: BEFORE UPDATE
- tr3: BEFORE INSERT OR UPDATE
We would normally expect that on INSERT, first tr1 would fire, followed by tr3; and on UPDATE, first tr2 would fire, followed by tr3. This is because tr3 is created last, and none of the triggers use FOLLOWS or PRECEDES clauses.
But what I found is that while the INSERT follows the correct order, UPDATE does not. And perhaps related, after tr3 is created, the value in information_schema.triggers.action_order incorrectly changes for tr2.
MariaDB [foo]> CREATE TABLE t (id int unsigned not null, winner varchar(80), primary key (id));
|
Query OK, 0 rows affected (0.002 sec)
|
|
|
MariaDB [foo]> CREATE TRIGGER tr1 BEFORE INSERT ON t FOR EACH ROW SET NEW.winner = 'tr1';
|
Query OK, 0 rows affected (0.001 sec)
|
|
|
MariaDB [foo]> CREATE TRIGGER tr2 BEFORE UPDATE ON t FOR EACH ROW SET NEW.winner = 'tr2';
|
Query OK, 0 rows affected (0.002 sec)
|
|
|
MariaDB [foo]> SELECT trigger_name, action_timing, event_manipulation, action_order FROM information_schema.triggers WHERE event_object_table = 't';
|
+--------------+---------------+--------------------+--------------+
|
| trigger_name | action_timing | event_manipulation | action_order |
|
+--------------+---------------+--------------------+--------------+
|
| tr1 | BEFORE | INSERT | 1 |
|
| tr2 | BEFORE | UPDATE | 1 |
|
+--------------+---------------+--------------------+--------------+
|
2 rows in set (0.002 sec)
|
|
|
MariaDB [foo]> CREATE TRIGGER tr3 BEFORE INSERT OR UPDATE ON t FOR EACH ROW SET NEW.winner = 'tr3';
|
Query OK, 0 rows affected (0.001 sec)
|
|
|
MariaDB [foo]> SELECT trigger_name, action_timing, event_manipulation, action_order FROM information_schema.triggers WHERE event_object_table = 't';
|
+--------------+---------------+--------------------+--------------+
|
| trigger_name | action_timing | event_manipulation | action_order |
|
+--------------+---------------+--------------------+--------------+
|
| tr1 | BEFORE | INSERT | 1 |
|
| tr3 | BEFORE | INSERT,UPDATE | 2 |
|
| tr2 | BEFORE | UPDATE | 2 |
|
+--------------+---------------+--------------------+--------------+
|
3 rows in set (0.003 sec)
|
|
|
MariaDB [foo]> INSERT INTO t (id) VALUES (1);
|
Query OK, 1 row affected (0.002 sec)
|
|
|
MariaDB [foo]> SELECT * FROM t;
|
+----+--------+
|
| id | winner |
|
+----+--------+
|
| 1 | tr3 |
|
+----+--------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [foo]> UPDATE t SET winner = 'x';
|
Query OK, 1 row affected (0.001 sec)
|
Rows matched: 1 Changed: 1 Warnings: 0
|
|
|
MariaDB [foo]> SELECT * FROM t;
|
+----+--------+
|
| id | winner |
|
+----+--------+
|
| 1 | tr2 |
|
+----+--------+
|
1 row in set (0.001 sec)
|
I would have expected the action_order for tr2 to remain at 1; and during the UPDATE, I would have expected tr2 to fire before tr3 (resulting in winner being 'tr3', as it should run last and overwrite the value).
Separately, the information_schema representation of multiple-event triggers has a conceptual issue: the action_order column should be relative to each table/timing/event tuple. For example, a multi-event trigger might be 1st for INSERT but 3rd for UPDATE, depending what other triggers exist. The current schema can't represent that.
As a possible fix, the original description of MDEV-10164 suggested to represent multi-event triggers as multiple rows in I_S.triggers (one per event_manipulation, instead of a comma-separated list).
However that all said: if the multiple-event feature mainly exists just for Oracle compatibility, and iirc Oracle chooses an arbitrary order for conflicting triggers anyway (user can't control it like in MariaDB), then perhaps the current ordering behavior isn't a real-world problem. But it would be good to note in the manual if so, since this differs from the ordering behavior of other triggers in MariaDB.
Attachments
Issue Links
- is caused by
-
MDEV-10164 Add support for TRIGGERS that fire on multiple events
-
- Closed
-