Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.2.3
-
Centos7
Description
Problem
The output of "show create trigger" does not include the "follows" keyword defined in the create trigger statement.
Reproduce
create database if not exists test;
|
|
use test
|
|
create table table_a (id int(10) not null,
|
value varchar(20) not null,
|
created datetime(6),
|
updated datetime(6)
|
);
|
|
create table table_a_audit(id int(10) not null,
|
ts datetime(6),
|
old_value varchar(20),
|
new_value varchar(20)
|
);
|
|
delimiter $$
|
create or replace trigger table_a_trig_1
|
before insert on table_a
|
for each row
|
begin
|
set new.created = current_timestamp(6);
|
set new.updated = current_timestamp(6);
|
end$$
|
|
create or replace trigger table_a_trig_2
|
before insert on table_a
|
for each row
|
follows table_a_trig_1
|
begin
|
insert into table_a_audit (id, ts, new_value)
|
value (new.id, current_timestamp(6), new.value);
|
end$$
|
|
delimiter ;
|
|
insert into table_a (id, value) values (1, "foo");
|
commit;
|
|
select * from table_a;
|
|
select * from table_a_audit;
|
|
show create table table_a;
|
|
show create trigger table_a_trig_1;
|
|
show create trigger table_a_trig_2;
|
The output of this last statement is incorrect, in build-11611 on bb-10.2-compatibility branch you get the following output
MariaDB [test]> show create trigger table_a_trig_2;
|
+----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
|
| Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation | Created |
|
+----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
|
| table_a_trig_2 | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` trigger table_a_trig_2
|
before insert on table_a
|
for each row
|
begin
|
insert into table_a_audit (id, ts, new_value)
|
value (new.id, current_timestamp(6), new.value);
|
end | latin1 | latin1_swedish_ci | latin1_swedish_ci | 2016-10-11 17:29:58.31 |
|
+----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
|
1 row in set (0.00 sec)
|
Solution
The :show create trigger" should include the full trigger definition.
Attachments
Issue Links
- relates to
-
MDEV-6112 multiple triggers per table
- Closed
alvinr, are you sure about that? Having FOLLOWS / PRECEDES in the trigger definition will do literally nothing good at all, and the price will be that it will break the dump.
Consider the following simple example.
The dump will look like this:
...
..
So, the order is preserved in the dump and will be restored properly.
But assume we would still have FOLLOWS clause in the definition:
...
..
After restoring the dump, tr2 and tr3 will be swapped.