polardbxengine/mysql-test/r/trigger.result

create table t1 (i int);
create trigger trg before insert on t1 for each row set @a:=1;
set @a:=0;
select @a;
@a
0
insert into t1 values (1);
select @a;
@a
1
drop trigger trg;
create trigger trg before insert on t1 for each row set @a:=new.i;
insert into t1 values (123);
select @a;
@a
123
drop trigger trg;
drop table t1;
create table t1 (i int not null, j int);
create trigger trg before insert on t1 for each row 
begin 
if isnull(new.j) then
set new.j:= new.i * 10;
end if;
end|
insert into t1 (i) values (1)|
insert into t1 (i,j) values (2, 3)|
select * from t1|
i	j
1	10
2	3
drop trigger trg|
drop table t1|
create table t1 (i int not null primary key);
create trigger trg after insert on t1 for each row 
set @a:= if(@a,concat(@a, ":", new.i), new.i);
set @a:="";
insert into t1 values (2),(3),(4),(5);
select @a;
@a
2:3:4:5
drop trigger trg;
drop table t1;
create table t1 (aid int not null primary key, balance int not null default 0);
insert into t1 values (1, 1000), (2,3000);
create trigger trg before update on t1 for each row 
begin
declare loc_err varchar(255);
if abs(new.balance - old.balance) > 1000 then
set new.balance:= old.balance;
set loc_err := concat("Too big change for aid = ", new.aid);
set @update_failed:= if(@update_failed, concat(@a, ":", loc_err), loc_err);
end if;
end|
set @update_failed:=""|
update t1 set balance=1500|
select @update_failed;
select * from t1|
@update_failed
Too big change for aid = 2
aid	balance
1	1500
2	3000
drop trigger trg|
drop table t1|
create table t1 (i int);
insert into t1 values (1),(2),(3),(4);
create trigger trg after update on t1 for each row 
set @total_change:=@total_change + new.i - old.i;
set @total_change:=0;
update t1 set i=3;
select @total_change;
@total_change
2
drop trigger trg;
drop table t1;
create table t1 (i int);
insert into t1 values (1),(2),(3),(4);
create trigger trg before delete on t1 for each row 
set @del_sum:= @del_sum + old.i;
set @del_sum:= 0;
delete from t1 where i <= 3;
select @del_sum;
@del_sum
6
drop trigger trg;
drop table t1;
create table t1 (i int);
insert into t1 values (1),(2),(3),(4);
create trigger trg after delete on t1 for each row set @del:= 1;
set @del:= 0;
delete from t1 where i <> 0;
select @del;
@del
1
drop trigger trg;
drop table t1;
create table t1 (i int, j int);
create trigger trg1 before insert on t1 for each row 
begin
if new.j > 10 then
set new.j := 10;
end if;
end|
create trigger trg2 before update on t1 for each row 
begin
if old.i % 2 = 0 then
set new.j := -1;
end if;
end|
create trigger trg3 after update on t1 for each row 
begin
if new.j = -1 then
set @fired:= "Yes";
end if;
end|
set @fired:="";
insert into t1 values (1,2),(2,3),(3,14);
select @fired;
@fired

select * from t1;
i	j
1	2
2	3
3	10
update t1 set j= 20;
select @fired;
@fired
Yes
select * from t1;
i	j
1	20
2	-1
3	20
drop trigger trg1;
drop trigger trg2;
drop trigger trg3;
drop table t1;
create table t1 (id int not null primary key, data int);
create trigger t1_bi before insert on t1 for each row
set @log:= concat(@log, "(BEFORE_INSERT: new=(id=", new.id, ", data=", new.data,"))");
create trigger t1_ai after insert on t1 for each row
set @log:= concat(@log, "(AFTER_INSERT: new=(id=", new.id, ", data=", new.data,"))");
create trigger t1_bu before update on t1 for each row
set @log:= concat(@log, "(BEFORE_UPDATE: old=(id=", old.id, ", data=", old.data,
") new=(id=", new.id, ", data=", new.data,"))");
create trigger t1_au after update on t1 for each row
set @log:= concat(@log, "(AFTER_UPDATE: old=(id=", old.id, ", data=", old.data,
") new=(id=", new.id, ", data=", new.data,"))");
create trigger t1_bd before delete on t1 for each row
set @log:= concat(@log, "(BEFORE_DELETE: old=(id=", old.id, ", data=", old.data,"))");
create trigger t1_ad after delete on t1 for each row
set @log:= concat(@log, "(AFTER_DELETE: old=(id=", old.id, ", data=", old.data,"))");
set @log:= "";
insert into t1 values (1, 1);
select @log;
@log
(BEFORE_INSERT: new=(id=1, data=1))(AFTER_INSERT: new=(id=1, data=1))
set @log:= "";
insert ignore t1 values (1, 2);
Warnings:
Warning	1062	Duplicate entry '1' for key 'PRIMARY'
select @log;
@log
(BEFORE_INSERT: new=(id=1, data=2))
set @log:= "";
insert into t1 (id, data) values (1, 3), (2, 2) on duplicate key update data= data + 1;
select @log;
@log
(BEFORE_INSERT: new=(id=1, data=3))(BEFORE_UPDATE: old=(id=1, data=1) new=(id=1, data=2))(AFTER_UPDATE: old=(id=1, data=1) new=(id=1, data=2))(BEFORE_INSERT: new=(id=2, data=2))(AFTER_INSERT: new=(id=2, data=2))
set @log:= "";
replace t1 values (1, 4), (3, 3);
select @log;
@log
(BEFORE_INSERT: new=(id=1, data=4))(BEFORE_DELETE: old=(id=1, data=2))(AFTER_DELETE: old=(id=1, data=2))(AFTER_INSERT: new=(id=1, data=4))(BEFORE_INSERT: new=(id=3, data=3))(AFTER_INSERT: new=(id=3, data=3))
drop trigger t1_bd;
drop trigger t1_ad;
set @log:= "";
replace t1 values (1, 5);
select @log;
@log
(BEFORE_INSERT: new=(id=1, data=5))(AFTER_INSERT: new=(id=1, data=5))
drop table t1;
create table t1 (id int primary key, data varchar(10), fk int);
create table t2 (event varchar(100));
create table t3 (id int primary key);
create trigger t1_ai after insert on t1 for each row 
insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "'"));
insert into t1 (id, data) values (1, "one"), (2, "two");
select * from t1;
id	data	fk
1	one	NULL
2	two	NULL
select * from t2;
event
INSERT INTO t1 id=1 data='one'
INSERT INTO t1 id=2 data='two'
drop trigger t1_ai;
create trigger t1_bi before insert on t1 for each row
begin
if exists (select id from t3 where id=new.fk) then
insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "' fk=", new.fk));
else
insert into t2 values (concat("INSERT INTO t1 FAILED id=", new.id, " data='", new.data, "' fk=", new.fk));
set new.id= NULL;
end if;
end|
insert into t3 values (1);
insert into t1 values (4, "four", 1), (5, "five", 2);
ERROR 23000: Column 'id' cannot be null
select * from t1;
id	data	fk
1	one	NULL
2	two	NULL
select * from t2;
event
INSERT INTO t1 id=1 data='one'
INSERT INTO t1 id=2 data='two'
drop table t1, t2, t3;
create table t1 (id int primary key, data varchar(10));
create table t2 (seq int);
insert into t2 values (10);
create function f1 () returns int return (select max(seq) from t2);
create trigger t1_bi before insert on t1 for each row
begin
if new.id > f1() then
set new.id:= f1();
end if;
end|
insert into t1 values (1, "first");
insert into t1 values (f1(), "max");
select * from t1;
id	data
1	first
10	max
drop table t1, t2;
drop function f1;
create table t1 (id int primary key, fk_t2 int);
create table t2 (id int primary key, fk_t3 int);
create table t3 (id int primary key);
insert into t1 values (1,1), (2,1), (3,2);
insert into t2 values (1,1), (2,2);
insert into t3 values (1), (2);
create trigger t3_ad after delete on t3 for each row
delete from t2 where fk_t3=old.id;
create trigger t2_ad after delete on t2 for each row
delete from t1 where fk_t2=old.id;
delete from t3 where id = 1;
select * from t1 left join (t2 left join t3 on t2.fk_t3 = t3.id) on t1.fk_t2 = t2.id;
id	fk_t2	id	fk_t3	id
3	2	2	2	2
drop table t1, t2, t3;
create table t1 (id int primary key, copy int);
create table t2 (id int primary key, data int);
insert into t2 values (1,1), (2,2);
create trigger t1_bi before insert on t1 for each row
set new.copy= (select data from t2 where id = new.id);
create trigger t1_bu before update on t1 for each row
set new.copy= (select data from t2 where id = new.id);
insert into t1 values (1,3), (2,4), (3,3);
update t1 set copy= 1 where id = 2;
select * from t1;
id	copy
1	1
2	2
3	NULL
drop table t1, t2;
create table t1 (i int);
create table t3 (i int);
create trigger trg before insert on t1 for each row set @a:= old.i;
ERROR HY000: There is no OLD row in on INSERT trigger
create trigger trg before delete on t1 for each row set @a:= new.i;
ERROR HY000: There is no NEW row in on DELETE trigger
create trigger trg before update on t1 for each row set old.i:=1;
ERROR HY000: Updating of OLD row is not allowed in trigger
create trigger trg before delete on t1 for each row set new.i:=1;
ERROR HY000: There is no NEW row in on DELETE trigger
create trigger trg after update on t1 for each row set new.i:=1;
ERROR HY000: Updating of NEW row is not allowed in after trigger
create trigger trg before update on t1 for each row set new.j:=1;
ERROR 42S22: Unknown column 'j' in 'NEW'
create trigger trg before update on t1 for each row set @a:=old.j;
ERROR 42S22: Unknown column 'j' in 'OLD'
create trigger trg before insert on t2 for each row set @a:=1;
ERROR 42S02: Table 'test.t2' doesn't exist
create trigger trg before insert on t1 for each row set @a:=1;
create trigger trg after insert on t1 for each row set @a:=1;
ERROR HY000: Trigger already exists
create trigger trg before insert on t3 for each row set @a:=1;
ERROR HY000: Trigger already exists
create trigger trg2 before insert on t3 for each row set @a:=1;
drop trigger trg2;
drop trigger trg;
drop trigger trg;
ERROR HY000: Trigger does not exist
create view v1 as select * from t1;
create trigger trg before insert on v1 for each row set @a:=1;
ERROR HY000: 'test.v1' is not BASE TABLE
drop view v1;
drop table t1;
drop table t3;
create temporary table t1 (i int);
create trigger trg before insert on t1 for each row set @a:=1;
ERROR HY000: Trigger's 't1' is view or temporary table
drop table t1;
create table t1 (x1col char);
create trigger tx1 before insert on t1 for each row set new.x1col = 'x';
insert into t1 values ('y');
drop trigger tx1;
drop table t1;
create table t1 (i int);
insert into t1 values (1), (2);
create trigger trg1 before delete on t1 for each row set @del_before:= @del_before + old.i;
create trigger trg2 after delete on t1 for each row set @del_after:= @del_after + old.i;
set @del_before:=0, @del_after:= 0;
delete from t1;
select @del_before, @del_after;
@del_before	@del_after
3	3
drop trigger trg1;
drop trigger trg2;
drop table t1;
create table t1 (a int);
create trigger trg1 before insert on t1 for each row set new.a= 10;
drop table t1;
create table t1 (a int);
insert into t1 values ();
select * from t1;
a
NULL
drop table t1;
create database mysqltest;
use mysqltest;
create table t1 (i int);
create trigger trg1 before insert on t1 for each row set @a:= 1;
drop database mysqltest;
use test;
create database mysqltest;
create table mysqltest.t1 (i int);
create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1;
ERROR HY000: Trigger in wrong schema
use mysqltest;
create trigger test.trg1 before insert on t1 for each row set @a:= 1;
ERROR 42S02: Table 'test.t1' doesn't exist
drop database mysqltest;
use test;
create table t1 (i int, j int default 10, k int not null, key (k));
create table t2 (i int);
insert into t1 (i, k) values (1, 1);
insert into t2 values (1);
create trigger trg1 before update on t1 for each row set @a:= @a + new.j - old.j;
create trigger trg2 after update on t1 for each row set @b:= "Fired";
set @a:= 0, @b:= "";
update t1, t2 set j = j + 10 where t1.i = t2.i;
select @a, @b;
@a	@b
10	Fired
insert into t1 values (2, 13, 2);
insert into t2 values (2);
set @a:= 0, @b:= "";
update t1, t2 set j = j + 15 where t1.i = t2.i and t1.k >= 2;
select @a, @b;
@a	@b
15	Fired
create trigger trg3 before delete on t1 for each row set @c:= @c + old.j;
create trigger trg4 before delete on t2 for each row set @d:= @d + old.i;
create trigger trg5 after delete on t1 for each row set @e:= "After delete t1 fired";
create trigger trg6 after delete on t2 for each row set @f:= "After delete t2 fired";
set @c:= 0, @d:= 0, @e:= "", @f:= "";
delete t1, t2 from t1, t2 where t1.i = t2.i;
select @c, @d, @e, @f;
@c	@d	@e	@f
48	3	After delete t1 fired	After delete t2 fired
drop table t1, t2;
create table t1 (i int, j int default 10)|
create table t2 (i int)|
insert into t2 values (1), (2)|
create trigger trg1 before insert on t1 for each row 
begin
if new.i = 1 then
set new.j := 1;
end if;
end|
create trigger trg2 after insert on t1 for each row set @a:= 1|
set @a:= 0|
insert into t1 (i) select * from t2|
select * from t1|
i	j
1	1
2	10
select @a|
@a
1
drop table t1, t2|
create table t1 (i int, j int, k int);
create trigger trg1 before insert on t1 for each row set new.k = new.i;
create trigger trg2 after insert on t1 for each row set @b:= "Fired";
set @b:="";
load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, i);
select *, @b from t1;
i	j	k	@b
10	NULL	10	Fired
15	NULL	15	Fired
set @b:="";
load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, j);
select *, @b from t1;
i	j	k	@b
10	NULL	10	Fired
15	NULL	15	Fired
1	2	1	Fired
3	4	3	Fired
5	6	5	Fired
drop table t1;
create table t1 (i int, bt int, k int, key(k));
create table t2 (i int);
insert into t1 values (1, 1, 1), (2, 2, 2);
insert into t2 values (1), (2), (3);
create trigger bi before insert on t1 for each row set @a:= new.bt;
create trigger bu before update on t1 for each row set @a:= new.bt;
create trigger bd before delete on t1 for each row set @a:= old.bt;
alter table t1 drop column bt;
insert into t1 values (3, 3);
ERROR 42S22: Unknown column 'bt' in 'NEW'
select * from t1;
i	k
1	1
2	2
update t1 set i = 2;
ERROR 42S22: Unknown column 'bt' in 'NEW'
select * from t1;
i	k
1	1
2	2
delete from t1;
ERROR 42S22: Unknown column 'bt' in 'OLD'
select * from t1;
i	k
1	1
2	2
load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k);
ERROR 42S22: Unknown column 'bt' in 'NEW'
select * from t1;
i	k
1	1
2	2
insert into t1 select 3, 3;
ERROR 42S22: Unknown column 'bt' in 'NEW'
select * from t1;
i	k
1	1
2	2
update t1, t2 set k = k + 10 where t1.i = t2.i;
ERROR 42S22: Unknown column 'bt' in 'NEW'
select * from t1;
i	k
1	1
2	2
update t1, t2 set k = k + 10 where t1.i = t2.i and k < 2;
ERROR 42S22: Unknown column 'bt' in 'NEW'
select * from t1;
i	k
1	1
2	2
delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;
ERROR 42S22: Unknown column 'bt' in 'OLD'
select * from t1;
i	k
1	1
2	2
delete t2, t1 from t2 straight_join t1 where t1.i = t2.i;
ERROR 42S22: Unknown column 'bt' in 'OLD'
select * from t1;
i	k
1	1
2	2
alter table t1 add primary key (i);
drop trigger bi;
insert into t1 values (2, 4) on duplicate key update k= k + 10;
ERROR 42S22: Unknown column 'bt' in 'NEW'
select * from t1;
i	k
1	1
2	2
replace into t1 values (2, 4);
ERROR 42S22: Unknown column 'bt' in 'OLD'
select * from t1;
i	k
1	1
2	2
drop table t1, t2;
create table t1 (col1 int, col2 int);
insert into t1 values (1, 2);
create function bug5893 () returns int return 5;
create trigger t1_bu before update on t1 for each row set new.col1= bug5893();
drop function bug5893;
update t1 set col2 = 4;
ERROR 42000: FUNCTION test.bug5893 does not exist
drop trigger t1_bu;
drop table t1;
set sql_mode='ansi';
create table t1 ("t1 column" int);
create trigger t1_bi before insert on t1 for each row set new."t1 column" = 5;
set sql_mode="";
insert into t1 values (0);
create trigger t1_af after insert on t1 for each row set @a=10;
insert into t1 values (0);
select * from t1;
t1 column
5
5
select @a;
@a
10
show triggers;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
t1_bi	INSERT	t1	set new."t1 column" = 5	BEFORE	#	REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
t1_af	INSERT	t1	set @a=10	AFTER	#		root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
drop table t1;
set sql_mode="traditional";
create table t1 (a date);
insert into t1 values ('2004-01-00');
ERROR 22007: Incorrect date value: '2004-01-00' for column 'a' at row 1
set sql_mode="";
create trigger t1_bi before insert on t1 for each row set new.a = '2004-01-00';
set sql_mode="traditional";
insert into t1 values ('2004-01-01');
select * from t1;
a
2004-01-00
set sql_mode=default;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
show triggers;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
t1_bi	INSERT	t1	set new.a = '2004-01-00'	BEFORE	#		root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
drop table t1;
create table t1 (id int);
create trigger t1_ai after insert on t1 for each row reset master;
ERROR 0A000: RESET is not allowed in stored function or trigger
create trigger t1_ai after insert on t1 for each row reset slave;
ERROR 0A000: RESET is not allowed in stored function or trigger
create trigger t1_ai after insert on t1 for each row flush hosts;
ERROR 0A000: FLUSH is not allowed in stored function or trigger
create trigger t1_ai after insert on t1 for each row flush tables with read lock;
ERROR 0A000: FLUSH is not allowed in stored function or trigger
create trigger t1_ai after insert on t1 for each row flush logs;
ERROR 0A000: FLUSH is not allowed in stored function or trigger
create trigger t1_ai after insert on t1 for each row flush status;
ERROR 0A000: FLUSH is not allowed in stored function or trigger
create trigger t1_ai after insert on t1 for each row flush user_resources;
ERROR 0A000: FLUSH is not allowed in stored function or trigger
create trigger t1_ai after insert on t1 for each row flush tables;
ERROR 0A000: FLUSH is not allowed in stored function or trigger
create trigger t1_ai after insert on t1 for each row flush privileges;
ERROR 0A000: FLUSH is not allowed in stored function or trigger
create trigger t1_ai after insert on t1 for each row call p1();
create procedure p1() flush tables;
insert into t1 values (0);
ERROR 0A000: FLUSH is not allowed in stored function or trigger
drop procedure p1;
create procedure p1() reset master;
insert into t1 values (0);
ERROR 0A000: RESET is not allowed in stored function or trigger
drop procedure p1;
create procedure p1() reset slave;
insert into t1 values (0);
ERROR 0A000: RESET is not allowed in stored function or trigger
drop procedure p1;
create procedure p1() flush hosts;
insert into t1 values (0);
ERROR 0A000: FLUSH is not allowed in stored function or trigger
drop procedure p1;
create procedure p1() flush privileges;
insert into t1 values (0);
ERROR 0A000: FLUSH is not allowed in stored function or trigger
drop procedure p1;
create procedure p1() flush tables with read lock;
insert into t1 values (0);
ERROR 0A000: FLUSH is not allowed in stored function or trigger
drop procedure p1;
create procedure p1() flush tables;
insert into t1 values (0);
ERROR 0A000: FLUSH is not allowed in stored function or trigger
drop procedure p1;
create procedure p1() flush logs;
insert into t1 values (0);
ERROR 0A000: FLUSH is not allowed in stored function or trigger
drop procedure p1;
create procedure p1() flush status;
insert into t1 values (0);
ERROR 0A000: FLUSH is not allowed in stored function or trigger
drop procedure p1;
create procedure p1() flush user_resources;
insert into t1 values (0);
ERROR 0A000: FLUSH is not allowed in stored function or trigger
drop procedure p1;
drop table t1;
create table t1 (id int, data int, username varchar(16));
insert into t1 (id, data) values (1, 0);
create trigger t1_whoupdated before update on t1 for each row
begin
declare user varchar(32);
declare i int;
select user() into user;
set NEW.username = user;
select count(*) from ((select 1) union (select 2)) as d1 into i;
end|
update t1 set data = 1;
update t1 set data = 2;
drop table t1;
create table t1 (c1 int, c2 datetime);
create trigger tr1 before insert on t1 for each row 
begin 
set new.c2= '2004-04-01';
select 'hello';
end|
ERROR 0A000: Not allowed to return a result set from a trigger
insert into t1 (c1) values (1),(2),(3);
select * from t1;
c1	c2
1	NULL
2	NULL
3	NULL
create procedure bug11587(x char(16))
begin
select "hello";
select "hello again";
end|
create trigger tr1 before insert on t1 for each row 
begin 
call bug11587(new.c2);
set new.c2= '2004-04-02';
end|
insert into t1 (c1) values (4),(5),(6);
ERROR 0A000: Not allowed to return a result set from a trigger
select * from t1;
c1	c2
1	NULL
2	NULL
3	NULL
drop procedure bug11587;
drop table t1;
create table t1 (f1 integer);
create table t2 (f2 integer);
create trigger t1_ai after insert on t1
for each row insert into t2 values (new.f1+1);
create trigger t2_ai after insert on t2
for each row insert into t1 values (new.f2+1);
set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth;
set @@max_sp_recursion_depth=100;
insert into t1 values (1);
ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS;
select * from t1;
f1
select * from t2;
f2
drop trigger t1_ai;
drop trigger t2_ai;
create trigger t1_bu before update on t1
for each row insert into t1 values (2);
insert into t1 values (1);
update t1 set f1= 10;
ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
select * from t1;
f1
1
drop trigger t1_bu;
create trigger t1_bu before update on t1
for each row delete from t1 where f1=new.f1;
update t1 set f1= 10;
ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
select * from t1;
f1
1
drop trigger t1_bu;
create trigger t1_bi before insert on t1
for each row set new.f1=(select sum(f1) from t1);
insert into t1 values (3);
select * from t1;
f1
1
1
drop trigger t1_bi;
drop tables t1, t2;
create table t1 (id int);
create table t2 (id int);
create trigger t1_bi before insert on t1 for each row insert into t2 values (new.id);
prepare stmt1 from "insert into t1 values (10)";
create procedure p1() insert into t1 values (10);
call p1();
drop trigger t1_bi;
execute stmt1;
call p1();
deallocate prepare stmt1;
drop procedure p1;
create table t3 (id int);
create trigger t1_bi after insert on t1 for each row insert into t2 values (new.id);
prepare stmt1 from "insert into t1 values (10)";
create procedure p1() insert into t1 values (10);
call p1();
drop trigger t1_bi;
create trigger t1_bi after insert on t1 for each row insert into t3 values (new.id);
execute stmt1;
call p1();
deallocate prepare stmt1;
drop procedure p1;
drop table t1, t2, t3;
create table t1 (a int);
CREATE PROCEDURE `p1`()
begin
insert into t1 values (1);
end//
create trigger trg before insert on t1 for each row 
begin 
declare done int default 0;
set done= not done;
end//
CALL p1();
drop procedure p1;
drop table t1;
create trigger t1_bi before insert on test.t1 for each row set @a:=0;
ERROR 3D000: No database selected
create trigger test.t1_bi before insert on t1 for each row set @a:=0;
ERROR 42S02: Table 'test.t1' doesn't exist
drop trigger t1_bi;
ERROR 3D000: No database selected
create table t1 (id int);
create trigger t1_bi before insert on t1 for each row set @a:=new.id;
create trigger t1_ai after insert on test.t1 for each row set @b:=new.id;
insert into t1 values (101);
select @a, @b;
@a	@b
101	101
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test';
TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_STATEMENT
test	t1_bi	test	t1	set @a:=new.id
test	t1_ai	test	t1	set @b:=new.id
rename table t1 to t2;
insert into t2 values (102);
select @a, @b;
@a	@b
102	102
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test';
TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_STATEMENT
test	t1_bi	test	t2	set @a:=new.id
test	t1_ai	test	t2	set @b:=new.id
alter table t2 rename to t3;
insert into t3 values (103);
select @a, @b;
@a	@b
103	103
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test';
TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_STATEMENT
test	t1_bi	test	t3	set @a:=new.id
test	t1_ai	test	t3	set @b:=new.id
alter table t3 rename to t4, add column val int default 0;
insert into t4 values (104, 1);
select @a, @b;
@a	@b
104	104
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test';
TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_STATEMENT
test	t1_bi	test	t4	set @a:=new.id
test	t1_ai	test	t4	set @b:=new.id
drop trigger t1_bi;
drop trigger t1_ai;
drop table t4;
create database mysqltest;
use mysqltest;
create table t1 (id int);
create trigger t1_bi before insert on t1 for each row set @a:=new.id;
insert into t1 values (101);
select @a;
@a
101
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' or event_object_schema = 'mysqltest';
TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_STATEMENT
mysqltest	t1_bi	mysqltest	t1	set @a:=new.id
rename table t1 to test.t2;
ERROR HY000: Trigger in wrong schema
insert into t1 values (102);
select @a;
@a
102
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' or event_object_schema = 'mysqltest';
TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_STATEMENT
mysqltest	t1_bi	mysqltest	t1	set @a:=new.id
drop trigger test.t1_bi;
ERROR HY000: Trigger does not exist
alter table t1 rename to test.t1;
ERROR HY000: Trigger in wrong schema
insert into t1 values (103);
select @a;
@a
103
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' or event_object_schema = 'mysqltest';
TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_STATEMENT
mysqltest	t1_bi	mysqltest	t1	set @a:=new.id
drop trigger test.t1_bi;
ERROR HY000: Trigger does not exist
alter table t1 rename to test.t1, add column val int default 0;
ERROR HY000: Trigger in wrong schema
insert into t1 values (104);
select @a;
@a
104
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' or event_object_schema = 'mysqltest';
TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_STATEMENT
mysqltest	t1_bi	mysqltest	t1	set @a:=new.id
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
drop trigger test.t1_bi;
ERROR HY000: Trigger does not exist
drop trigger t1_bi;
drop table t1;
drop database mysqltest;
use test;
create table t1 (i int);
create trigger t1_bi before insert on t1 for each row return 0;
ERROR 42000: RETURN is only allowed in a FUNCTION
insert into t1 values (1);
drop table t1;
create table t1 (a varchar(64), b int);
create table t2 like t1;
create trigger t1_ai after insert on t1 for each row
set @a:= (select max(a) from t1);
insert into t1 (a) values
("Twas"),("brillig"),("and"),("the"),("slithy"),("toves"),
("Did"),("gyre"),("and"),("gimble"),("in"),("the"),("wabe");
create trigger t2_ai after insert on t2 for each row
set @a:= (select max(a) from t2);
insert into t2 select * from t1;
load data infile '../../std_data/words.dat' into table t1 (a);
drop trigger t1_ai;
drop trigger t2_ai;
create function f1() returns int return (select max(b) from t1);
insert into t1 values
("All",f1()),("mimsy",f1()),("were",f1()),("the",f1()),("borogoves",f1()),
("And",f1()),("the",f1()),("mome", f1()),("raths",f1()),("outgrabe",f1());
create function f2() returns int return (select max(b) from t2);
insert into t2 select a, f2() from t1;
load data infile '../../std_data/words.dat' into table t1 (a) set b:= f1();
drop function f1;
drop function f2;
drop table t1, t2;
create table t1(i int not null, j int not null, n numeric(15,2), primary key(i,j));
create table t2(i int not null, n numeric(15,2), primary key(i));
create trigger t1_ai after insert on t1 for each row
begin
declare sn numeric(15,2);
select sum(n) into sn from t1 where i=new.i;
replace into t2 values(new.i, sn);
end|
insert into t1 values
(1,1,10.00),(1,2,10.00),(1,3,10.00),(1,4,10.00),(1,5,10.00),
(1,6,10.00),(1,7,10.00),(1,8,10.00),(1,9,10.00),(1,10,10.00),
(1,11,10.00),(1,12,10.00),(1,13,10.00),(1,14,10.00),(1,15,10.00);
select * from t1;
i	j	n
1	1	10.00
1	2	10.00
1	3	10.00
1	4	10.00
1	5	10.00
1	6	10.00
1	7	10.00
1	8	10.00
1	9	10.00
1	10	10.00
1	11	10.00
1	12	10.00
1	13	10.00
1	14	10.00
1	15	10.00
select * from t2;
i	n
1	150.00
drop tables t1, t2;
CREATE TABLE t1 (
conn_id INT,
trigger_conn_id INT
);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
SET NEW.trigger_conn_id = CONNECTION_ID();
INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1);
INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1);
SELECT * FROM t1 WHERE conn_id != trigger_conn_id;
conn_id	trigger_conn_id
DROP TRIGGER t1_bi;
DROP TABLE t1;
CREATE TABLE t1 (i1 INT);
SET @save_sql_mode=@@sql_mode;
SET SQL_MODE='';
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
SET @x = 5/0;
SET SQL_MODE='traditional';
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
SET @x = 5/0;
SET @x=1;
INSERT INTO t1 VALUES (@x);
SELECT @x;
@x
NULL
SET @x=2;
UPDATE t1 SET i1 = @x;
SELECT @x;
@x
NULL
SET SQL_MODE='';
SET @x=3;
INSERT INTO t1 VALUES (@x);
SELECT @x;
@x
NULL
SET @x=4;
UPDATE t1 SET i1 = @x;
SELECT @x;
@x
NULL
SET @@sql_mode=@save_sql_mode;
DROP TRIGGER t1_ai;
DROP TRIGGER t1_au;
DROP TABLE t1;
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
CREATE TABLE t1 (i1 INT);
INSERT INTO t1 VALUES (3);
CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET i1 = 5;
CREATE PROCEDURE p2(INOUT i1 INT) DETERMINISTIC NO SQL SET i1 = i1 * 7;
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
BEGIN
CALL p1(NEW.i1);
CALL p2(NEW.i1);
END//
UPDATE t1 SET i1 = 11 WHERE i1 = 3;
DROP TRIGGER t1_bu;
DROP PROCEDURE p2;
DROP PROCEDURE p1;
INSERT INTO t1 VALUES (13);
CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 17;
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
CALL p1(OLD.i1);
UPDATE t1 SET i1 = 19 WHERE i1 = 13;
ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
DROP TRIGGER t1_bu;
DROP PROCEDURE p1;
INSERT INTO t1 VALUES (23);
CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 29;
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
CALL p1(OLD.i1);
UPDATE t1 SET i1 = 31 WHERE i1 = 23;
ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
DROP TRIGGER t1_bu;
DROP PROCEDURE p1;
INSERT INTO t1 VALUES (37);
CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 41;
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
CALL p1(NEW.i1);
UPDATE t1 SET i1 = 43 WHERE i1 = 37;
ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
DROP TRIGGER t1_au;
DROP PROCEDURE p1;
INSERT INTO t1 VALUES (47);
CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 49;
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
CALL p1(NEW.i1);
UPDATE t1 SET i1 = 51 WHERE i1 = 47;
ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
DROP TRIGGER t1_au;
DROP PROCEDURE p1;
SELECT * FROM t1;
i1
35
13
23
37
47
DROP TABLE t1;
create trigger wont_work after update on mysql.user for each row
begin
set @a:= 1;
end|
ERROR HY000: Triggers can not be created on system tables
use mysql|
create trigger wont_work after update on event for each row
begin
set @a:= 1;
end|
ERROR HY000: Triggers can not be created on system tables
use test|
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1(c INT);
CREATE TABLE t2(c INT);
CREATE DEFINER=1234567890abcdefGHIKL1234567890abcdefGHIKL@localhost
TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1;
ERROR HY000: String '1234567890abcdefGHIKL1234567890abcdefGHIKL' is too long for user name (should be no longer than 32)
CREATE DEFINER=some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890X
TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW SET @a = 2;
ERROR HY000: String 'host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij12345' is too long for host name (should be no longer than 255)
DROP TABLE t1;
DROP TABLE t2;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
SET @save_sql_mode=@@sql_mode;
SET sql_mode='TRADITIONAL'|
create table t1 (id int(10) not null primary key, v int(10) )|
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
create table t2 (id int(10) not null primary key, v int(10) )|
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
create table t3 (id int(10) not null primary key, v int(10) )|
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
create table t4 (c int)|
create trigger t4_bi before insert on t4 for each row set @t4_bi_called:=1|
create trigger t4_bu before update on t4 for each row set @t4_bu_called:=1|
insert into t1 values(10, 10)|
set @a:=1/0|
Warnings:
Warning	1365	Division by 0
select 1/0 from t1|
1/0
NULL
Warnings:
Warning	1365	Division by 0
create trigger t1_bi before insert on t1 for each row set @a:=1/0|
insert into t1 values(20, 20)|
drop trigger t1_bi|
create trigger t1_bi before insert on t1 for each row
begin
insert into t2 values (new.id, new.v);
update t2 set v=v+1 where id= new.id;
replace t3 values (new.id, 0);
update t2, t3 set t2.v=new.v, t3.v=new.v where t2.id=t3.id;
create temporary table t5 select * from t1;
delete from t5;
insert into t5 select * from t1;
insert into t4 values (0);
set @check= (select count(*) from t5);
update t4 set c= @check;
drop temporary table t5;
set @a:=1/0;
end|
set @check=0, @t4_bi_called=0, @t4_bu_called=0|
insert into t1 values(30, 30)|
select @check, @t4_bi_called, @t4_bu_called|
@check	@t4_bi_called	@t4_bu_called
2	1	1
SET @@sql_mode=@save_sql_mode;
drop table t1;
drop table t2;
drop table t3;
drop table t4;
create table t1 (i int, j int key);
insert into t1 values (1,1), (2,2), (3,3);
create trigger t1_bu before update on t1 for each row
set new.j = new.j + 10;
update t1 set i= i+ 10 where j > 2;
select * from t1;
i	j
1	1
2	2
13	13
drop table t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE TABLE t2 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW 
INSERT INTO t2 VALUES (OLD.a);
FLUSH STATUS;
TRUNCATE t1;
SHOW STATUS LIKE 'handler_delete';
Variable_name	Value
Handler_delete	3
SELECT COUNT(*) FROM t2;
COUNT(*)
0
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
DELETE FROM t2;
FLUSH STATUS;
DELETE FROM t1;
SHOW STATUS LIKE 'handler_delete';
Variable_name	Value
Handler_delete	8
SELECT COUNT(*) FROM t2;
COUNT(*)
8
DROP TRIGGER trg_t1;
DROP TABLE t1,t2;
drop table if exists t1;
drop function if exists f1;
create table t1 (i int);
create function f1() returns int return 10;
create trigger t1_bi before insert on t1 for each row set @a:= f1() + 10;
insert into t1 values ();
select @a;
@a
20
insert into t1 values ();
select @a;
@a
20
drop table t1;
drop function f1;
create table t1(a int, b varchar(50));
drop trigger not_a_trigger;
ERROR HY000: Trigger does not exist
drop trigger if exists not_a_trigger;
Warnings:
Note	1360	Trigger does not exist
create trigger t1_bi before insert on t1
for each row set NEW.b := "In trigger t1_bi";
insert into t1 values (1, "a");
drop trigger if exists t1_bi;
insert into t1 values (2, "b");
drop trigger if exists t1_bi;
Warnings:
Note	1360	Trigger does not exist
insert into t1 values (3, "c");
select * from t1;
a	b
1	In trigger t1_bi
2	b
3	c
drop table t1;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1 (
id int NOT NULL DEFAULT '0',
a  varchar(10) NOT NULL,
b  varchar(10),
c  varchar(10),
d  timestamp NOT NULL,
PRIMARY KEY (id, a)
);
CREATE TABLE t2 (
fubar_id         int unsigned NOT NULL DEFAULT '0',
last_change_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (fubar_id)
);
CREATE TRIGGER fubar_change
AFTER UPDATE ON t1
FOR EACH ROW
BEGIN
INSERT INTO t2 (fubar_id, last_change_time)
SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time
FROM t1 WHERE (id = NEW.id) AND (OLD.c != NEW.c)
ON DUPLICATE KEY UPDATE
last_change_time =
IF((fubar_id = NEW.id)AND(OLD.c != NEW.c),NOW(),last_change_time);
END
|
INSERT INTO t1 (id,a, b,c,d) VALUES
(1,'a','b','c',now()),(2,'a','b','c',now());
UPDATE t1 SET c='Bang!' WHERE id=1;
SELECT fubar_id FROM t2;
fubar_id
1
DROP TABLE t1,t2;
SET sql_mode = default;
DROP TABLE IF EXISTS bug21825_A;
DROP TABLE IF EXISTS bug21825_B;
CREATE TABLE bug21825_A (id int(10));
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE bug21825_B (id int(10));
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TRIGGER trgA AFTER INSERT ON bug21825_A
FOR EACH ROW
BEGIN
INSERT INTO bug21825_B (id) values (1);
END//
INSERT INTO bug21825_A (id) VALUES (10);
INSERT INTO bug21825_A (id) VALUES (20);
DROP TABLE bug21825_B;
DELETE FROM bug21825_A WHERE id = 20;
DROP TABLE bug21825_A;
DROP TABLE IF EXISTS bug22580_t1;
DROP PROCEDURE IF EXISTS bug22580_proc_1;
DROP PROCEDURE IF EXISTS bug22580_proc_2;
CREATE TABLE bug22580_t1 (a INT, b INT);
CREATE PROCEDURE bug22580_proc_2()
BEGIN
DROP TABLE IF EXISTS bug22580_tmp;
CREATE TEMPORARY TABLE bug22580_tmp (a INT);
DROP TABLE bug22580_tmp;
END||
CREATE PROCEDURE bug22580_proc_1()
BEGIN
CALL bug22580_proc_2();
END||
CREATE TRIGGER t1bu BEFORE UPDATE ON bug22580_t1
FOR EACH ROW 
BEGIN
CALL bug22580_proc_1();
END||
INSERT INTO bug22580_t1 VALUES (1,1);
DROP TABLE bug22580_t1;
DROP PROCEDURE bug22580_proc_1;
DROP PROCEDURE bug22580_proc_2;
DROP TRIGGER IF EXISTS trg27006_a_update;
DROP TRIGGER IF EXISTS trg27006_a_insert;
CREATE TABLE t1 (
`id` int(10) unsigned NOT NULL auto_increment,
`val` varchar(10) NOT NULL,
PRIMARY KEY  (`id`)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE t2 like t1;
CREATE TRIGGER trg27006_a_insert AFTER INSERT ON t1 FOR EACH ROW
BEGIN
insert into t2 values (NULL,new.val);
END |
CREATE TRIGGER trg27006_a_update AFTER UPDATE ON t1 FOR EACH ROW
BEGIN
insert into t2 values (NULL,new.val);
END |
INSERT INTO t1(val) VALUES ('test1'),('test2');
SELECT * FROM t1;
id	val
1	test1
2	test2
SELECT * FROM t2;
id	val
1	test1
2	test2
INSERT INTO t1 VALUES (2,'test2') ON DUPLICATE KEY UPDATE val=VALUES(val);
INSERT INTO t1 VALUES (2,'test3') ON DUPLICATE KEY UPDATE val=VALUES(val);
INSERT INTO t1 VALUES (3,'test4') ON DUPLICATE KEY UPDATE val=VALUES(val);
SELECT * FROM t1;
id	val
1	test1
2	test3
3	test4
SELECT * FROM t2;
id	val
1	test1
2	test2
3	test2
4	test3
5	test4
DROP TRIGGER trg27006_a_insert;
DROP TRIGGER trg27006_a_update;
drop table t1,t2;
create table t1 (i int);
create trigger t1_bi before insert on t1 for each row set new.i = 7;
create trigger t1_ai after insert on t1 for each row set @a := 7;
create table t2 (j int);
insert into t2 values (1), (2);
set @a:="";
insert into t1 select * from t2;
select * from t1;
i
7
7
select @a;
@a
7
drop trigger t1_bi;
drop trigger t1_ai;
create table t3 (isave int);
create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i);
insert into t1 select * from t2;
select * from t1;
i
7
7
1
2
select * from t3;
isave
1
2
drop table t1, t2, t3;

Bug#28502 Triggers that update another innodb table will block
on X lock unnecessarily

Ensure we do not open and lock tables for triggers we do not fire.

drop table if exists t1, t2;
drop trigger if exists trg_bug28502_au;
create table t1 (id int, count int);
create table t2 (id int);
create trigger trg_bug28502_au before update on t2
for each row
begin
if (new.id is not null) then
update t1 set count= count + 1 where id = old.id;
end if;
end|
insert into t1 (id, count) values (1, 0);
lock table t1 write;
insert into t2 set id=1;
unlock tables;
update t2 set id=1 where id=1;
select * from t1;
id	count
1	1
select * from t2;
id
1
drop table t1, t2;

Additionally, provide test coverage for triggers and 
all MySQL data changing commands.

drop table if exists t1, t2, t1_op_log;
drop view if exists v1;
drop trigger if exists trg_bug28502_bi;
drop trigger if exists trg_bug28502_ai;
drop trigger if exists trg_bug28502_bu;
drop trigger if exists trg_bug28502_au;
drop trigger if exists trg_bug28502_bd;
drop trigger if exists trg_bug28502_ad;
create table t1 (id int primary key auto_increment, operation varchar(255));
create table t2 (id int primary key);
create table t1_op_log(operation varchar(255));
create view v1 as select * from t1;
create trigger trg_bug28502_bi before insert on t1
for each row
insert into t1_op_log (operation)
values (concat("Before INSERT, new=", new.operation));
create trigger trg_bug28502_ai after insert on t1
for each row
insert into t1_op_log (operation)
values (concat("After INSERT, new=", new.operation));
create trigger trg_bug28502_bu before update on t1
for each row
insert into t1_op_log (operation)
values (concat("Before UPDATE, new=", new.operation,
", old=", old.operation));
create trigger trg_bug28502_au after update on t1
for each row
insert into t1_op_log (operation)
values (concat("After UPDATE, new=", new.operation,
", old=", old.operation));
create trigger trg_bug28502_bd before delete on t1
for each row
insert into t1_op_log (operation)
values (concat("Before DELETE, old=", old.operation));
create trigger trg_bug28502_ad after delete on t1
for each row
insert into t1_op_log (operation)
values (concat("After DELETE, old=", old.operation));
insert into t1 (operation) values ("INSERT");
set @id=last_insert_id();
select * from t1;
id	operation
1	INSERT
select * from t1_op_log;
operation
Before INSERT, new=INSERT
After INSERT, new=INSERT
truncate t1_op_log;
update t1 set operation="UPDATE" where id=@id;
select * from t1;
id	operation
1	UPDATE
select * from t1_op_log;
operation
Before UPDATE, new=UPDATE, old=INSERT
After UPDATE, new=UPDATE, old=INSERT
truncate t1_op_log;
delete from t1 where id=@id;
select * from t1;
id	operation
select * from t1_op_log;
operation
Before DELETE, old=UPDATE
After DELETE, old=UPDATE
truncate t1;
truncate t1_op_log;
insert into t1 (id, operation) values
(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key")
on duplicate key update id=NULL, operation="Should never happen";
set @id=last_insert_id();
select * from t1;
id	operation
1	INSERT ON DUPLICATE KEY UPDATE, inserting a new key
select * from t1_op_log;
operation
Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
After INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
truncate t1_op_log;
insert into t1 (id, operation) values
(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same")
on duplicate key update id=NULL,
operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate";
select * from t1;
id	operation
0	INSERT ON DUPLICATE KEY UPDATE, updating the duplicate
select * from t1_op_log;
operation
Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, the key value is the same
Before UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
After UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
truncate t1;
truncate t1_op_log;
replace into t1 values (NULL, "REPLACE, inserting a new key");
set @id=last_insert_id();
select * from t1;
id	operation
1	REPLACE, inserting a new key
select * from t1_op_log;
operation
Before INSERT, new=REPLACE, inserting a new key
After INSERT, new=REPLACE, inserting a new key
truncate t1_op_log;
replace into t1 values (@id, "REPLACE, deleting the duplicate");
select * from t1;
id	operation
1	REPLACE, deleting the duplicate
select * from t1_op_log;
operation
Before INSERT, new=REPLACE, deleting the duplicate
Before DELETE, old=REPLACE, inserting a new key
After DELETE, old=REPLACE, inserting a new key
After INSERT, new=REPLACE, deleting the duplicate
truncate t1;
truncate t1_op_log;
insert into t1
select NULL, "CREATE TABLE ... SELECT, inserting a new key";
set @id=last_insert_id();
select * from t1;
id	operation
1	CREATE TABLE ... SELECT, inserting a new key
select * from t1_op_log;
operation
Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key
After INSERT, new=CREATE TABLE ... SELECT, inserting a new key
truncate t1_op_log;
replace into t1
select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
select * from t1;
id	operation
1	CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
select * from t1_op_log;
operation
Before INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
Before DELETE, old=CREATE TABLE ... SELECT, inserting a new key
After DELETE, old=CREATE TABLE ... SELECT, inserting a new key
After INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
truncate t1;
truncate t1_op_log;
insert into t1 (id, operation)
select NULL, "INSERT ... SELECT, inserting a new key";
set @id=last_insert_id();
select * from t1;
id	operation
1	INSERT ... SELECT, inserting a new key
select * from t1_op_log;
operation
Before INSERT, new=INSERT ... SELECT, inserting a new key
After INSERT, new=INSERT ... SELECT, inserting a new key
truncate t1_op_log;
insert into t1 (id, operation)
select @id,
"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"
on duplicate key update id=NULL,
operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate";
select * from t1;
id	operation
0	INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate
select * from t1_op_log;
operation
Before INSERT, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate
Before UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key
After UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key
truncate t1;
truncate t1_op_log;
replace into t1 (id, operation)
select NULL, "REPLACE ... SELECT, inserting a new key";
set @id=last_insert_id();
select * from t1;
id	operation
1	REPLACE ... SELECT, inserting a new key
select * from t1_op_log;
operation
Before INSERT, new=REPLACE ... SELECT, inserting a new key
After INSERT, new=REPLACE ... SELECT, inserting a new key
truncate t1_op_log;
replace into t1 (id, operation)
select @id, "REPLACE ... SELECT, deleting a duplicate";
select * from t1;
id	operation
1	REPLACE ... SELECT, deleting a duplicate
select * from t1_op_log;
operation
Before INSERT, new=REPLACE ... SELECT, deleting a duplicate
Before DELETE, old=REPLACE ... SELECT, inserting a new key
After DELETE, old=REPLACE ... SELECT, inserting a new key
After INSERT, new=REPLACE ... SELECT, deleting a duplicate
truncate t1;
truncate t1_op_log;
insert into t1 (id, operation) values (1, "INSERT for multi-DELETE");
insert into t2 (id) values (1);
delete t1.*, t2.* from t1, t2 where t1.id=1;
select * from t1;
id	operation
select * from t2;
id
select * from t1_op_log;
operation
Before INSERT, new=INSERT for multi-DELETE
After INSERT, new=INSERT for multi-DELETE
Before DELETE, old=INSERT for multi-DELETE
After DELETE, old=INSERT for multi-DELETE
truncate t1;
truncate t2;
truncate t1_op_log;
insert into t1 (id, operation) values (1, "INSERT for multi-UPDATE");
insert into t2 (id) values (1);
update t1, t2 set t1.id=2, operation="multi-UPDATE" where t1.id=1;
update t1, t2
set t2.id=3, operation="multi-UPDATE, SET for t2, but the trigger is fired" where t1.id=2;
select * from t1;
id	operation
2	multi-UPDATE, SET for t2, but the trigger is fired
select * from t2;
id
3
select * from t1_op_log;
operation
Before INSERT, new=INSERT for multi-UPDATE
After INSERT, new=INSERT for multi-UPDATE
Before UPDATE, new=multi-UPDATE, old=INSERT for multi-UPDATE
After UPDATE, new=multi-UPDATE, old=INSERT for multi-UPDATE
Before UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE
After UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE
truncate table t1;
truncate table t2;
truncate table t1_op_log;

Now do the same but use a view instead of the base table.

insert into v1 (operation) values ("INSERT");
set @id=last_insert_id();
select * from t1;
id	operation
1	INSERT
select * from t1_op_log;
operation
Before INSERT, new=INSERT
After INSERT, new=INSERT
truncate t1_op_log;
update v1 set operation="UPDATE" where id=@id;
select * from t1;
id	operation
1	UPDATE
select * from t1_op_log;
operation
Before UPDATE, new=UPDATE, old=INSERT
After UPDATE, new=UPDATE, old=INSERT
truncate t1_op_log;
delete from v1 where id=@id;
select * from t1;
id	operation
select * from t1_op_log;
operation
Before DELETE, old=UPDATE
After DELETE, old=UPDATE
truncate t1;
truncate t1_op_log;
insert into v1 (id, operation) values
(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key")
on duplicate key update id=NULL, operation="Should never happen";
set @id=last_insert_id();
select * from t1;
id	operation
1	INSERT ON DUPLICATE KEY UPDATE, inserting a new key
select * from t1_op_log;
operation
Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
After INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
truncate t1_op_log;
insert into v1 (id, operation) values
(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same")
on duplicate key update id=NULL,
operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate";
select * from t1;
id	operation
0	INSERT ON DUPLICATE KEY UPDATE, updating the duplicate
select * from t1_op_log;
operation
Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, the key value is the same
Before UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
After UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
truncate t1;
truncate t1_op_log;
replace into v1 values (NULL, "REPLACE, inserting a new key");
set @id=last_insert_id();
select * from t1;
id	operation
1	REPLACE, inserting a new key
select * from t1_op_log;
operation
Before INSERT, new=REPLACE, inserting a new key
After INSERT, new=REPLACE, inserting a new key
truncate t1_op_log;
replace into v1 values (@id, "REPLACE, deleting the duplicate");
select * from t1;
id	operation
1	REPLACE, deleting the duplicate
select * from t1_op_log;
operation
Before INSERT, new=REPLACE, deleting the duplicate
Before DELETE, old=REPLACE, inserting a new key
After DELETE, old=REPLACE, inserting a new key
After INSERT, new=REPLACE, deleting the duplicate
truncate t1;
truncate t1_op_log;
insert into v1
select NULL, "CREATE TABLE ... SELECT, inserting a new key";
set @id=last_insert_id();
select * from t1;
id	operation
1	CREATE TABLE ... SELECT, inserting a new key
select * from t1_op_log;
operation
Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key
After INSERT, new=CREATE TABLE ... SELECT, inserting a new key
truncate t1_op_log;
replace into v1
select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
select * from t1;
id	operation
1	CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
select * from t1_op_log;
operation
Before INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
Before DELETE, old=CREATE TABLE ... SELECT, inserting a new key
After DELETE, old=CREATE TABLE ... SELECT, inserting a new key
After INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
truncate t1;
truncate t1_op_log;
insert into v1 (id, operation)
select NULL, "INSERT ... SELECT, inserting a new key";
set @id=last_insert_id();
select * from t1;
id	operation
1	INSERT ... SELECT, inserting a new key
select * from t1_op_log;
operation
Before INSERT, new=INSERT ... SELECT, inserting a new key
After INSERT, new=INSERT ... SELECT, inserting a new key
truncate t1_op_log;
insert into v1 (id, operation)
select @id,
"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"
on duplicate key update id=NULL,
operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate";
select * from t1;
id	operation
0	INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate
select * from t1_op_log;
operation
Before INSERT, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate
Before UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key
After UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key
truncate t1;
truncate t1_op_log;
replace into v1 (id, operation)
select NULL, "REPLACE ... SELECT, inserting a new key";
set @id=last_insert_id();
select * from t1;
id	operation
1	REPLACE ... SELECT, inserting a new key
select * from t1_op_log;
operation
Before INSERT, new=REPLACE ... SELECT, inserting a new key
After INSERT, new=REPLACE ... SELECT, inserting a new key
truncate t1_op_log;
replace into v1 (id, operation)
select @id, "REPLACE ... SELECT, deleting a duplicate";
select * from t1;
id	operation
1	REPLACE ... SELECT, deleting a duplicate
select * from t1_op_log;
operation
Before INSERT, new=REPLACE ... SELECT, deleting a duplicate
Before DELETE, old=REPLACE ... SELECT, inserting a new key
After DELETE, old=REPLACE ... SELECT, inserting a new key
After INSERT, new=REPLACE ... SELECT, deleting a duplicate
truncate t1;
truncate t1_op_log;
insert into v1 (id, operation) values (1, "INSERT for multi-DELETE");
insert into t2 (id) values (1);
delete v1.*, t2.* from v1, t2 where v1.id=1;
select * from t1;
id	operation
select * from t2;
id
select * from t1_op_log;
operation
Before INSERT, new=INSERT for multi-DELETE
After INSERT, new=INSERT for multi-DELETE
Before DELETE, old=INSERT for multi-DELETE
After DELETE, old=INSERT for multi-DELETE
truncate t1;
truncate t2;
truncate t1_op_log;
insert into v1 (id, operation) values (1, "INSERT for multi-UPDATE");
insert into t2 (id) values (1);
update v1, t2 set v1.id=2, operation="multi-UPDATE" where v1.id=1;
update v1, t2
set t2.id=3, operation="multi-UPDATE, SET for t2, but the trigger is fired" where v1.id=2;
select * from t1;
id	operation
2	multi-UPDATE, SET for t2, but the trigger is fired
select * from t2;
id
3
select * from t1_op_log;
operation
Before INSERT, new=INSERT for multi-UPDATE
After INSERT, new=INSERT for multi-UPDATE
Before UPDATE, new=multi-UPDATE, old=INSERT for multi-UPDATE
After UPDATE, new=multi-UPDATE, old=INSERT for multi-UPDATE
Before UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE
After UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE
drop view v1;
drop table t1, t2, t1_op_log;

Bug#27248 Triggers: error if insert affects temporary table

The bug was fixed by the fix for Bug#26141

drop table if exists t1;
drop temporary table if exists t2;
create table t1 (s1 int);
create temporary table t2 (s1 int);
create trigger t1_bi before insert on t1 for each row insert into t2 values (0);
create trigger t1_bd before delete on t1 for each row delete from t2;
insert into t1 values (0);
insert into t1 values (0);
select * from t1;
s1
0
0
select * from t2;
s1
0
0
delete from t1;
select * from t1;
s1
select * from t2;
s1
drop table t1;
drop temporary table t2;
#------------------------------------------------------------------------
# Bug#39953 Triggers are not working properly with multi table updates
#------------------------------------------------------------------------
DROP TABLE IF EXISTS t1;
DROP TRIGGER IF EXISTS t_insert;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (a int, date_insert timestamp, PRIMARY KEY (a));
INSERT INTO t1 (a) VALUES (2),(5);
CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET
date_insert=NOW() WHERE t1.a=t2.b AND t2.a=NEW.a; END |
INSERT INTO t2 (a,b) VALUES (1,2);
DROP TRIGGER t_insert;
CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET
date_insert=NOW(),b=b+1 WHERE t1.a=t2.b AND t2.a=NEW.a; END |
INSERT INTO t2 (a,b) VALUES (3,5);
ERROR HY000: Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
DROP TABLE t1;
DROP TRIGGER t_insert;
DROP TABLE t2;
End of 5.0 tests
drop table if exists table_25411_a;
drop table if exists table_25411_b;
create table table_25411_a(a int);
create table table_25411_b(b int);
create trigger trg_25411a_ai after insert on table_25411_a
for each row
insert into table_25411_b select new.*;
select * from table_25411_a;
a
insert into table_25411_a values (1);
ERROR 42S02: Unknown table 'new'
select * from table_25411_a;
a
drop table table_25411_a;
drop table table_25411_b;
SHOW CREATE TRIGGER trg;
ERROR HY000: Trigger does not exist
create table t1 (i int, j int);
create trigger t1_bi before insert on t1 for each row begin end;
create trigger t1_bi before insert on t1 for each row begin end;
ERROR HY000: Trigger already exists
drop trigger t1_bi;
drop trigger t1_bi;
ERROR HY000: Trigger does not exist
lock tables t1 read;
create trigger t1_bi before insert on t1 for each row begin end;
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
create trigger t1_bi before insert on t1 for each row begin end;
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
drop trigger t1_bi;
ERROR HY000: Trigger does not exist
unlock tables;
create trigger t1_bi before insert on t1 for each row begin end;
lock tables t1 read;
create trigger t1_bi before insert on t1 for each row begin end;
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
drop trigger t1_bi;
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
unlock tables;
drop trigger t1_bi;
lock tables t1 write;
create trigger b1_bi before insert on t1 for each row set new.i = new.i + 10;
insert into t1 values (10, 10);
drop trigger b1_bi;
insert into t1 values (10, 10);
select * from t1;
i	j
20	10
10	10
unlock tables;
drop table t1;
drop table if exists t1, t2;
drop trigger if exists trg1;
drop trigger if exists trg2;
create table t1 (a int);
create table t2 (b int);
create trigger trg1 after update on t1 for each row set @a= @a+1;
create trigger trg2 after update on t2 for each row set @b= @b+1;
insert into t1 values (1), (2), (3);
insert into t2 values (1), (2), (3);
set @a= 0;
set @b= 0;
update t1, t2 set t1.a= t1.a, t2.b= t2.b;
select @a, @b;
@a	@b
3	3
update t1, t2 set t1.a= t2.b, t2.b= t1.a;
select @a, @b;
@a	@b
6	6
update t1 set a= a;
select @a, @b;
@a	@b
9	6
update t2 set b= b;
select @a, @b;
@a	@b
9	9
update t1 set a= 1;
select @a, @b;
@a	@b
12	9
update t2 set b= 1;
select @a, @b;
@a	@b
12	12
drop trigger trg1;
drop trigger trg2;
drop table t1, t2;
CREATE TABLE t1 ( a INT, b INT );
CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY, b INT );
INSERT INTO t1 (a) VALUES (1);
CREATE TRIGGER tr1
BEFORE INSERT ON t2
FOR EACH ROW 
BEGIN 
UPDATE a_nonextisting_table SET a = 1;
END//
CREATE TABLE IF NOT EXISTS t2 ( a INT, b INT ) SELECT a, b FROM t1;
Warnings:
Note	1050	Table 't2' already exists
SELECT * FROM t2;
a	b
DROP TABLE t1, t2;
#
# Bug#51650 crash with user variables and triggers
#
DROP TRIGGER IF EXISTS trg1;
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (b VARCHAR(50) NOT NULL);
CREATE TABLE t2 (a VARCHAR(10) NOT NULL DEFAULT '');
CREATE TRIGGER trg1 AFTER INSERT ON t2
FOR EACH ROW BEGIN
SELECT 1 FROM t1 c WHERE
(@bug51650 IS NULL OR @bug51650 != c.b) AND c.b = NEW.a LIMIT 1 INTO @foo;
END//
SET @bug51650 = 1;
INSERT IGNORE INTO t2 VALUES();
INSERT IGNORE INTO t1 SET b = '777';
INSERT IGNORE INTO t2 SET a = '111';
SET @bug51650 = 1;
INSERT IGNORE INTO t2 SET a = '777';
DROP TRIGGER trg1;
DROP TABLE t1, t2;
#
# Bug#50755: Crash if stored routine def contains version comments
#
# With WL#9494, the SHOW TRIGGERS no more parses
# the underlaying trigger, and hence it would not
# report parse error. This means that the code
# causing this issue is removed now with WL#9494.
# This test case is kept for reference.
#
DROP DATABASE IF EXISTS db1;
DROP TRIGGER IF EXISTS trg1;
DROP TABLE IF EXISTS t1, t2;
CREATE DATABASE db1;
USE db1;
CREATE TABLE t1 (b INT);
CREATE TABLE t2 (a INT);
CREATE TRIGGER trg1 BEFORE INSERT ON t2 FOR EACH ROW INSERT/*!INTO*/t1 VALUES (1);
SHOW TRIGGERS IN db1;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
trg1	INSERT	t2	INSERTINTO t1 VALUES (1)	BEFORE	#	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
INSERT INTO t2 VALUES (1);
ERROR 42000: Trigger 'trg1' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1 VALUES (1)' at line 1'
SELECT * FROM t1;
b
DROP DATABASE db1;
USE test;
End of 5.1 tests.
#
# Bug#34453 Can't change size of file (Errcode: 1224)
#
DROP TRIGGER IF EXISTS t1_bi;
DROP TRIGGER IF EXISTS t1_bd;
DROP TABLE IF EXISTS t1;
DROP TEMPORARY TABLE IF EXISTS t2;
CREATE TABLE t1 (s1 INT);
CREATE TEMPORARY TABLE t2 (s1 INT);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (0);
CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t2;
INSERT INTO t1 VALUES (0);
INSERT INTO t1 VALUES (0);
SELECT * FROM t1;
s1
0
0
SELECT * FROM t2;
s1
0
0
# Reported to give ERROR 14 (HY000): 
# Can't change size of file (Errcode: 1224)
# on Windows 
DELETE FROM t1;
DROP TABLE t1;
DROP TEMPORARY TABLE t2;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
DROP TRIGGER IF EXISTS trg1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT);
CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
DECLARE a CHAR;
SELECT 'ab' INTO a;
SELECT 'ab' INTO a;
SELECT 'a' INTO a;
END|
INSERT INTO t1 VALUES (1);
DROP TRIGGER trg1;
DROP TABLE t1;
DROP TRIGGER IF EXISTS trg1;
DROP TRIGGER IF EXISTS trg2;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT);
CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
DECLARE trg1 CHAR;
SELECT 'ab' INTO trg1;
END|
CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW
BEGIN
DECLARE trg2 CHAR;
SELECT 'ab' INTO trg2;
END|
INSERT INTO t1 VALUES (0);
SELECT * FROM t1;
a
0
SHOW WARNINGS;
Level	Code	Message
INSERT INTO t1 VALUES (1),(2);
DROP TRIGGER trg1;
DROP TRIGGER trg2;
DROP TABLE t1;
SET sql_mode = default;
#
# Bug #46747 "Crash in MDL_ticket::upgrade_shared_lock_to_exclusive
#             on TRIGGER + TEMP table".
#
drop trigger if exists t1_bi;
drop temporary table if exists t1;
drop table if exists t1;
create table t1 (i int);
create trigger t1_bi before insert on t1 for each row set @a:=1;
# Create temporary table which shadows base table with trigger.
create temporary table t1 (j int);
# Dropping of trigger should succeed.
drop trigger t1_bi;
select trigger_name from information_schema.triggers
where event_object_schema = 'test' and event_object_table = 't1';
TRIGGER_NAME
# Clean-up.
drop temporary table t1;
drop table t1;

#
# Bug #12362125: SP INOUT HANDLING IS BROKEN FOR TEXT TYPE.
#
CREATE TABLE t1(c TEXT);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
DECLARE v TEXT;
SET v = 'aaa';
SET NEW.c = v;
END|
INSERT INTO t1 VALUES('qazwsxedc');
SELECT c FROM t1;
c
aaa
DROP TABLE t1;

End of 5.5 tests.

#
# Bug#34432 Wrong lock type passed to the engine if pre-locking + 
#           multi-update in a trigger
#
DROP TABLE IF EXISTS t1, t2, t3;
DROP TRIGGER IF EXISTS t2_ai;
CREATE TABLE t2 
(
value CHAR(30),
domain_id INT,
mailaccount_id INT, 
program CHAR(30),
keey CHAR(30),
PRIMARY KEY(domain_id)
);
CREATE TABLE t3 
(
value CHAR(30),
domain_id INT,
mailaccount_id INT,
program CHAR(30),
keey CHAR(30),
PRIMARY KEY(domain_id)
);
CREATE TABLE t1 (id INT,domain CHAR(30),PRIMARY KEY(id));
CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW 
UPDATE t3 ms, t1 d SET ms.value='No'
  WHERE ms.domain_id = 
(SELECT max(id) FROM t1 WHERE domain='example.com') 
AND ms.mailaccount_id IS NULL 
AND ms.program='spamfilter' 
AND ms.keey='scan_incoming';
|
INSERT INTO t1 VALUES (1, 'example.com'),
(2, 'mysql.com'),
(3, 'earthmotherwear.com'),
(4, 'yahoo.com'),
(5, 'example.com');
INSERT INTO t2 VALUES ('Yes', 1, NULL, 'spamfilter','scan_incoming');
DROP TRIGGER t2_ai;
DROP TABLE t1, t2, t3;
#
# Bug#14493938 - CREATE TEMPORARY TABLE INSIDE TRIGGER -- CRASH (DEBUG ONLY)
#
CREATE TABLE t1 (a INT, b INT DEFAULT 150);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1
FOR EACH ROW 
BEGIN
CREATE TEMPORARY TABLE t2 AS SELECT NEW.a, NEW.b;
INSERT INTO t2(a) VALUES (10);
INSERT INTO t2 VALUES (100, 500);
INSERT INTO t2(a) VALUES (1000);
END
|
INSERT INTO t1 VALUES (1, 2);
SELECT * FROM t2;
a	b
1	2
10	150
100	500
1000	150
DROP TABLE t1;
DROP TEMPORARY TABLE t2;
#
# Bug#15985318 - ASSERTION FAILED: ! thd->in_sub_stmt with certain
#                                    commands in triggers
#
CREATE TABLE t1(a INT);
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW START SLAVE;
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW STOP SLAVE;
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
CREATE SERVER s FOREIGN DATA WRAPPER mysql OPTIONS (USER 'Remote',
HOST '192.168.1.106',
DATABASE 'test');
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
ALTER SERVER s OPTIONS (password '1');
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW
DROP SERVER IF EXISTS s;
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
CREATE DATABASE db1;
CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW
ALTER DATABASE db1 CHARACTER SET latin1;
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
DROP DATABASE db1;
CREATE USER 'u1'@'localhost' IDENTIFIED BY 'pass';
CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW
ALTER USER 'u1'@'localhost' PASSWORD EXPIRE;
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
DROP USER 'u1'@'localhost';
CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW
CHANGE MASTER TO MASTER_SSL = 0;
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
DROP TABLE t1;
#
# Bug#17864349 - CHANGING TRUNCATE TABLE TO DROP TABLE & CREATE TABLE
# MAKES TRIGGER.TEST FAIL
#
# The following tests check for non in-place update (i.e. using temporary table)
# of column with different datatypes
SET @save_sql_mode= @@sql_mode;
SET sql_mode= 'traditional';
# t1 column type name: INT
# t2 column type name: INT
# t2 column value: 3
CREATE TABLE t1(a INT, b INT);
CREATE TABLE t2(a INT, b INT NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 3);
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: TINYINT
# t2 column type name: TINYINT
# t2 column value: 3
CREATE TABLE t1(a INT, b TINYINT);
CREATE TABLE t2(a INT, b TINYINT NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 3);
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: BOOL
# t2 column type name: BOOL
# t2 column value: TRUE
CREATE TABLE t1(a INT, b BOOL);
CREATE TABLE t2(a INT, b BOOL NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, TRUE);
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: SMALLINT
# t2 column type name: SMALLINT
# t2 column value: 3
CREATE TABLE t1(a INT, b SMALLINT);
CREATE TABLE t2(a INT, b SMALLINT NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 3);
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: BIGINT
# t2 column type name: BIGINT
# t2 column value: 3
CREATE TABLE t1(a INT, b BIGINT);
CREATE TABLE t2(a INT, b BIGINT NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 3);
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: DECIMAL
# t2 column type name: DECIMAL
# t2 column value: 3
CREATE TABLE t1(a INT, b DECIMAL);
CREATE TABLE t2(a INT, b DECIMAL NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 3);
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: FLOAT
# t2 column type name: FLOAT
# t2 column value: 3
CREATE TABLE t1(a INT, b FLOAT);
CREATE TABLE t2(a INT, b FLOAT NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 3);
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: DOUBLE
# t2 column type name: DOUBLE
# t2 column value: 3
CREATE TABLE t1(a INT, b DOUBLE);
CREATE TABLE t2(a INT, b DOUBLE NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 3);
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: BIT
# t2 column type name: BIT
# t2 column value: 1
CREATE TABLE t1(a INT, b BIT);
CREATE TABLE t2(a INT, b BIT NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 1);
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: ENUM('a', 'b', 'c')
# t2 column type name: ENUM('a', 'b', 'c')
# t2 column value: 'b'
CREATE TABLE t1(a INT, b ENUM('a', 'b', 'c'));
CREATE TABLE t2(a INT, b ENUM('a', 'b', 'c') NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 'b');
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: SET('a', 'b', 'c')
# t2 column type name: SET('a', 'b', 'c')
# t2 column value: 'b'
CREATE TABLE t1(a INT, b SET('a', 'b', 'c'));
CREATE TABLE t2(a INT, b SET('a', 'b', 'c') NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 'b');
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: VARBINARY(10)
# t2 column type name: VARBINARY(10)
# t2 column value: 'binary'
CREATE TABLE t1(a INT, b VARBINARY(10));
CREATE TABLE t2(a INT, b VARBINARY(10) NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 'binary');
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: BINARY(10)
# t2 column type name: BINARY(10)
# t2 column value: 'binary'
CREATE TABLE t1(a INT, b BINARY(10));
CREATE TABLE t2(a INT, b BINARY(10) NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 'binary');
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: TINYTEXT
# t2 column type name: TINYTEXT
# t2 column value: 'text'
CREATE TABLE t1(a INT, b TINYTEXT);
CREATE TABLE t2(a INT, b TINYTEXT NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 'text');
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: TEXT(10)
# t2 column type name: TEXT(10)
# t2 column value: 'text'
CREATE TABLE t1(a INT, b TEXT(10));
CREATE TABLE t2(a INT, b TEXT(10) NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 'text');
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: BLOB
# t2 column type name: BLOB
# t2 column value: 'binary'
CREATE TABLE t1(a INT, b BLOB);
CREATE TABLE t2(a INT, b BLOB NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 'binary');
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: VARCHAR(5)
# t2 column type name: INT
# t2 column value: 3
CREATE TABLE t1(a INT, b VARCHAR(5));
CREATE TABLE t2(a INT, b INT NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 3);
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: INT
# t2 column type name: VARCHAR(5)
# t2 column value: 'str'
CREATE TABLE t1(a INT, b INT);
CREATE TABLE t2(a INT, b VARCHAR(5) NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 'str');
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: VARCHAR(15)
# t2 column type name: VARCHAR(5)
# t2 column value: 'str'
CREATE TABLE t1(a INT, b VARCHAR(15));
CREATE TABLE t2(a INT, b VARCHAR(5) NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 'str');
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: VARCHAR(15)
# t2 column type name: BLOB
# t2 column value: 'str'
CREATE TABLE t1(a INT, b VARCHAR(15));
CREATE TABLE t2(a INT, b BLOB NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 'str');
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
# t1 column type name: TEXT(20)
# t2 column type name: TEXT(10)
# t2 column value: 'text'
CREATE TABLE t1(a INT, b TEXT(20));
CREATE TABLE t2(a INT, b TEXT(10) NOT NULL);
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t2 VALUES (1, 'text');
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW SET @b = 1;
UPDATE t1, t2 SET t2.b = t1.b;
ERROR 23000: Column 'b' cannot be null
DROP TABLE t1, t2;
SET sql_mode= @save_sql_mode;
#
# Bug#18596756 - FAILED PREPARING OF TRIGGER ON TRUNCATED TABLES CAUSE
#                ERROR 1054.
#
CREATE TABLE t1(id INT);
CREATE TABLE t2(id INT);
CREATE TRIGGER trigger1 BEFORE INSERT ON t1 FOR EACH ROW
SET NEW.id= (SELECT * FROM t2);
INSERT INTO t2 VALUES(0);
INSERT INTO t1 VALUES(0);
TRUNCATE TABLE t2;
INSERT INTO t2 VALUES(0);
INSERT INTO t1 VALUES(0);
DROP TABLE t2;
INSERT INTO t1 VALUES(0);
ERROR 42S02: Table 'test.t2' doesn't exist
DROP TABLE t1;
#
# Bug#16522924 : UPDATE TRIGGER INVOKED WHEN UPDATE IGNORE MEANS
#                      THAT NO UPDATE IS PERFORMED
#
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE TABLE t2 (after_update CHAR(50));
CREATE TABLE t3(b INT PRIMARY KEY);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t3 VALUES (1);
CREATE TRIGGER post_update_t1 AFTER UPDATE ON t1
FOR EACH ROW BEGIN
INSERT INTO t2 VALUES("POST UPDATE TRIGGER FOR UPDATE IGNORE ON t1 FIRED");
END|
UPDATE IGNORE t1 SET a=2 WHERE a=1;
Warnings:
Warning	1062	Duplicate entry '2' for key 'PRIMARY'
SELECT * FROM t2;
after_update
UPDATE IGNORE t1,t3 SET t1.a=2 WHERE t1.a=1;
Warnings:
Warning	1062	Duplicate entry '2' for key 'PRIMARY'
SELECT * FROM t2;
after_update
UPDATE IGNORE t3,t1 SET t1.a=2 WHERE t1.a=1;
Warnings:
Warning	1062	Duplicate entry '2' for key 'PRIMARY'
SELECT * FROM t1;
a
1
2
SELECT * FROM t2;
after_update
DROP TRIGGER post_update_t1;
DROP TABLE t1,t2,t3;
#
# WL#9262: All system tables should support 32 character length user names
#
CREATE USER user_name_robert_golebiowski@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char;
CREATE TABLE test.silly_one (ID INT);
CREATE DEFINER=user_name_robert_golebiowski@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char TRIGGER test.silly_trigger BEFORE INSERT ON test.silly_one FOR EACH ROW SET @x=1;
SHOW TRIGGERS FROM test LIKE 'silly_one';
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
silly_trigger	INSERT	silly_one	SET @x=1	BEFORE	#	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	user_name_robert_golebiowski@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
SELECT DEFINER FROM information_schema.triggers WHERE TRIGGER_NAME='silly_trigger';
DEFINER
user_name_robert_golebiowski@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char
DROP USER user_name_robert_golebiowski@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char;
DROP TRIGGER test.silly_trigger;
DROP TABLE test.silly_one;
#
# Bug #22512899 - DROP TRIGGER AFTER RENAME TABLE RESULTS IN ERROR CODE 1146: TABLE DOESN'T EXIST
#
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW BEGIN END;
RENAME TABLE t1 TO t1Renamed;
DROP TRIGGER t1_bi;
DROP TABLE t1Renamed;
End of 5.7 tests.
#
# Bug #23624693 - USING SHOW CREATE TRIGGER IN A TRANSACTION CRASHES THE SERVER
#
SHOW CREATE TRIGGER non_existence_db.some_trigger;
ERROR 42000: Unknown database 'non_existence_db'
CREATE TRIGGER non_existent_db.trg1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN END;
ERROR 42000: Unknown database 'non_existent_db'
DROP TRIGGER non_existent_db.trg1;
ERROR 42000: Unknown database 'non_existent_db'
#
# Bug#24449174 - DROPPING A PARTITION DROPS THE TRIGGER ON 8.0.0
#
CREATE TABLE t1 (val INT NOT NULL) ENGINE=InnoDB
PARTITION BY LIST(val) (
PARTITION p1 VALUES IN (1,2,3),
PARTITION p2 VALUES IN (4,5)
);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW BEGIN END;
SHOW CREATE TRIGGER t1_bi;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
t1_bi	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER `t1_bi` BEFORE INSERT ON `t1` FOR EACH ROW BEGIN END	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci	#
ALTER TABLE t1 DROP PARTITION p1;
SHOW CREATE TRIGGER t1_bi;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
t1_bi	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER `t1_bi` BEFORE INSERT ON `t1` FOR EACH ROW BEGIN END	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci	#
DROP TABLE t1;
#
# Bug#24482919 -- SHOW TRIGGER OUTPUT INCONSISTENCY ON WINDOWS / LINUX ON 8.0.0
#
CREATE TABLE t1 (a INT);
CREATE TRIGGER trg1a BEFORE INSERT ON t1 FOR EACH ROW BEGIN END;
CREATE TRIGGER trg1b AFTER INSERT ON t1 FOR EACH ROW BEGIN END;
CREATE TRIGGER trg1c BEFORE UPDATE ON t1 FOR EACH ROW BEGIN END;
CREATE TRIGGER trg1d AFTER UPDATE ON t1 FOR EACH ROW BEGIN END;
CREATE TRIGGER trg1e BEFORE DELETE ON t1 FOR EACH ROW BEGIN END;
CREATE TRIGGER trg1f AFTER DELETE ON t1 FOR EACH ROW BEGIN END;
CREATE TRIGGER trg1a2 BEFORE INSERT ON t1 FOR EACH ROW BEGIN END;
CREATE TRIGGER trg1b2 AFTER INSERT ON t1 FOR EACH ROW BEGIN END;
CREATE TRIGGER trg1c2 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN END;
CREATE TRIGGER trg1d2 AFTER UPDATE ON t1 FOR EACH ROW BEGIN END;
CREATE TRIGGER trg1f2 AFTER DELETE ON t1 FOR EACH ROW BEGIN END;
CREATE TRIGGER trg1a0 BEFORE INSERT ON t1 FOR EACH ROW PRECEDES trg1a BEGIN END;
CREATE TRIGGER trg1a3 BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS trg1a2 BEGIN END;
CREATE TRIGGER trg1b0 AFTER INSERT ON t1 FOR EACH ROW PRECEDES trg1b BEGIN END;
CREATE TRIGGER trg1b3 AFTER INSERT ON t1 FOR EACH ROW FOLLOWS trg1b2 BEGIN END;
CREATE TRIGGER trg1c0 BEFORE UPDATE ON t1 FOR EACH ROW PRECEDES trg1c BEGIN END;
CREATE TRIGGER trg1c3 BEFORE UPDATE ON t1 FOR EACH ROW FOLLOWS trg1c2 BEGIN END;
SELECT TRIGGER_NAME FROM information_schema.triggers WHERE TRIGGER_NAME LIKE 'trg1%' ORDER BY TRIGGER_NAME;
TRIGGER_NAME
trg1a
trg1a0
trg1a2
trg1a3
trg1b
trg1b0
trg1b2
trg1b3
trg1c
trg1c0
trg1c2
trg1c3
trg1d
trg1d2
trg1e
trg1f
trg1f2
SHOW TRIGGERS;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
trg1a0	INSERT	t1	BEGIN END	BEFORE	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1a	INSERT	t1	BEGIN END	BEFORE	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1a2	INSERT	t1	BEGIN END	BEFORE	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1a3	INSERT	t1	BEGIN END	BEFORE	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1b0	INSERT	t1	BEGIN END	AFTER	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1b	INSERT	t1	BEGIN END	AFTER	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1b2	INSERT	t1	BEGIN END	AFTER	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1b3	INSERT	t1	BEGIN END	AFTER	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1c0	UPDATE	t1	BEGIN END	BEFORE	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1c	UPDATE	t1	BEGIN END	BEFORE	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1c2	UPDATE	t1	BEGIN END	BEFORE	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1c3	UPDATE	t1	BEGIN END	BEFORE	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1d	UPDATE	t1	BEGIN END	AFTER	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1d2	UPDATE	t1	BEGIN END	AFTER	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1e	DELETE	t1	BEGIN END	BEFORE	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1f	DELETE	t1	BEGIN END	AFTER	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg1f2	DELETE	t1	BEGIN END	AFTER	#	#	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
DROP TABLE t1;
#
# Bug#25581925: 'MDL_CHECKER::IS_READ_LOCKED(M_THD, *OBJECT)' AT
#               DD::CACHE::DICTIONARY_CLIENT:
#
CREATE TABLE t1(a INT);
CREATE SCHEMA s1;
CREATE VIEW s1.v1 AS SELECT * FROM t1;
CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN END;
LOCK TABLE s1.v1 WRITE;
DROP TRIGGER trg1;
UNLOCK TABLES;
DROP VIEW s1.v1;
DROP TABLE t1;
DROP SCHEMA s1;
DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
FLUSH PRIVILEGES;
DROP DATABASE IF EXISTS mysqltest_db1;
CREATE DATABASE mysqltest_db1;
CREATE USER mysqltest_dfn@localhost;
CREATE USER mysqltest_inv@localhost;
GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;

---> connection: wl2818_definer_con
CREATE TABLE t1(num_value INT);
CREATE TABLE t2(user_str TEXT);

---> connection: default
GRANT INSERT, DROP ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
GRANT INSERT, DROP ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;

---> connection: default
GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
Warnings:
Warning	1287	The SUPER privilege identifier is deprecated

---> connection: wl2818_definer_con
CREATE TRIGGER trg1 AFTER INSERT ON t1
FOR EACH ROW
INSERT INTO t2 VALUES(CURRENT_USER());
ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'

---> connection: default
GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;

---> connection: wl2818_definer_con
CREATE TRIGGER trg1 AFTER INSERT ON t1
FOR EACH ROW
INSERT INTO t2 VALUES(CURRENT_USER());

---> connection: default
REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;

---> connection: wl2818_definer_con
DROP TRIGGER trg1;
ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'

---> connection: wl2818_definer_con
INSERT INTO t1 VALUES(0);
ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'

---> connection: default
GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;

---> connection: wl2818_definer_con
INSERT INTO t1 VALUES(0);
DROP TRIGGER trg1;
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;

---> connection: default
REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost;
Warnings:
Warning	1287	The SUPER privilege identifier is deprecated

---> connection: wl2818_definer_con
CREATE TRIGGER trg1 AFTER INSERT ON t1
FOR EACH ROW
INSERT INTO t2 VALUES(CURRENT_USER());

---> connection: default
GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
GRANT ALL PRIVILEGES ON mysqltest_db1.t1
TO 'mysqltest_inv'@localhost;
GRANT SELECT ON mysqltest_db1.t2
TO 'mysqltest_inv'@localhost;

---> connection: wl2818_definer_con
use mysqltest_db1;
INSERT INTO t1 VALUES(1);
SELECT * FROM t1;
num_value
1
SELECT * FROM t2;
user_str
mysqltest_dfn@localhost

---> connection: wl2818_invoker_con
use mysqltest_db1;
INSERT INTO t1 VALUES(2);
SELECT * FROM t1;
num_value
1
2
SELECT * FROM t2;
user_str
mysqltest_dfn@localhost
mysqltest_dfn@localhost

---> connection: default
use mysqltest_db1;
REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost;

---> connection: wl2818_invoker_con
use mysqltest_db1;
INSERT INTO t1 VALUES(3);
ERROR 42000: INSERT command denied to user 'mysqltest_dfn'@'localhost' for table 't2'
SELECT * FROM t1;
num_value
1
2
SELECT * FROM t2;
user_str
mysqltest_dfn@localhost
mysqltest_dfn@localhost

---> connection: wl2818_definer_con
use mysqltest_db1;
DROP TRIGGER trg1;
CREATE DEFINER='mysqltest_inv'@'localhost'
  TRIGGER trg1 BEFORE INSERT ON t1
FOR EACH ROW
SET @new_sum = 0;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

---> connection: default
use mysqltest_db1;
GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
Warnings:
Warning	1287	The SUPER privilege identifier is deprecated

---> connection: wl2818_definer_con
CREATE DEFINER='mysqltest_inv'@'localhost'
  TRIGGER trg1 BEFORE INSERT ON t1
FOR EACH ROW
SET @new_sum = 0;
CREATE DEFINER='mysqltest_nonexs'@'localhost'
  TRIGGER trg2 AFTER INSERT ON t1
FOR EACH ROW
SET @new_sum = 0;
Warnings:
Note	1449	The user specified as a definer ('mysqltest_nonexs'@'localhost') does not exist
INSERT INTO t1 VALUES(6);
ERROR HY000: The user specified as a definer ('mysqltest_nonexs'@'localhost') does not exist
SHOW TRIGGERS;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
trg1	INSERT	t1	SET @new_sum = 0	BEFORE	#	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	mysqltest_inv@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
trg2	INSERT	t1	SET @new_sum = 0	AFTER	#	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	mysqltest_nonexs@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
DROP TRIGGER trg1;
DROP TRIGGER trg2;

---> connection: default
DROP USER mysqltest_dfn@localhost;
DROP USER mysqltest_inv@localhost;
DROP DATABASE mysqltest_db1;
DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
FLUSH PRIVILEGES;
DROP DATABASE IF EXISTS mysqltest_db1;
CREATE DATABASE mysqltest_db1;
use mysqltest_db1;
CREATE TABLE t1(col CHAR(20));
CREATE TABLE t2(col CHAR(20));
CREATE TABLE t3(col CHAR(20));
CREATE TABLE t4(col CHAR(20));
CREATE USER mysqltest_u1@localhost;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
GRANT TRIGGER ON mysqltest_db1.* TO mysqltest_u1@localhost;
SET @mysqltest_var = NULL;

---> connection: default
use mysqltest_db1;
GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
SHOW GRANTS FOR mysqltest_u1@localhost;
Grants for mysqltest_u1@localhost
GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost`
GRANT DELETE, TRIGGER ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost`

---> connection: bug15166_u1_con
use mysqltest_db1;
CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1
FOR EACH ROW
SET @mysqltest_var = 'Hello, world!';

---> connection: default
use mysqltest_db1;
GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;

---> connection: bug15166_u1_con
use mysqltest_db1;
CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1
FOR EACH ROW
SET @mysqltest_var = NEW.col;
DROP TRIGGER t1_trg_err_1;
CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1
FOR EACH ROW
SET @mysqltest_var = OLD.col;
DROP TRIGGER t1_trg_err_2;
CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2
FOR EACH ROW
SET NEW.col = 't2_trg_before_insert';
CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3
FOR EACH ROW
SET @mysqltest_var = NEW.col;
DROP TRIGGER t3_trg_err_1;
CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3
FOR EACH ROW
SET @mysqltest_var = OLD.col;
DROP TRIGGER t3_trg_err_2;
CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4
FOR EACH ROW
SET NEW.col = 't4_trg_before_insert';

---> connection: default
use mysqltest_db1;
REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost;
GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost;

---> connection: bug15166_u1_con
use mysqltest_db1;
CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1
FOR EACH ROW
SET @mysqltest_var = NEW.col;
CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1
FOR EACH ROW
SET @mysqltest_var = OLD.col;
CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2
FOR EACH ROW
SET NEW.col = 't2_trg_err_1';
DROP TRIGGER t2_trg_err_1;
CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2
FOR EACH ROW
SET NEW.col = CONCAT(OLD.col, '(updated)');
DROP TRIGGER t2_trg_err_2;
CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3
FOR EACH ROW
SET @mysqltest_var = NEW.col;
CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3
FOR EACH ROW
SET @mysqltest_var = OLD.col;
CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4
FOR EACH ROW
SET NEW.col = 't4_trg_err_1';
DROP TRIGGER t4_trg_err_1;
CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4
FOR EACH ROW
SET NEW.col = CONCAT(OLD.col, '(updated)');
DROP TRIGGER t4_trg_err_2;

---> connection: default
use mysqltest_db1;
REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
INSERT INTO t1 VALUES('line1');
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't1'
SELECT * FROM t1;
col
SELECT @mysqltest_var;
@mysqltest_var
NULL
INSERT INTO t2 VALUES('line2');
SELECT * FROM t2;
col
t2_trg_before_insert
INSERT INTO t3 VALUES('t3_line1');
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't3'
SELECT * FROM t3;
col
SELECT @mysqltest_var;
@mysqltest_var
NULL
INSERT INTO t4 VALUES('t4_line2');
SELECT * FROM t4;
col
t4_trg_before_insert

---> connection: default
use mysqltest_db1;
REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
INSERT INTO t1 VALUES('line3');
SELECT * FROM t1;
col
line3
SELECT @mysqltest_var;
@mysqltest_var
line3
INSERT INTO t2 VALUES('line4');
ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't2'
SELECT * FROM t2;
col
t2_trg_before_insert
INSERT INTO t3 VALUES('t3_line2');
SELECT * FROM t3;
col
t3_line2
SELECT @mysqltest_var;
@mysqltest_var
t3_line2
INSERT INTO t4 VALUES('t4_line2');
ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't4'
SELECT * FROM t4;
col
t4_trg_before_insert
DELETE FROM t1;
SELECT @mysqltest_var;
@mysqltest_var
Hello, world!
DROP USER mysqltest_u1@localhost;
DROP DATABASE mysqltest_db1;
DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
FLUSH PRIVILEGES;
DROP DATABASE IF EXISTS mysqltest_db1;
CREATE DATABASE mysqltest_db1;
USE mysqltest_db1;
CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i1 INT);
CREATE USER mysqltest_dfn@localhost;
CREATE USER mysqltest_inv@localhost;
GRANT EXECUTE, CREATE ROUTINE, TRIGGER ON *.* TO mysqltest_dfn@localhost;
GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost;
CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3;
CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
CALL p2(NEW.i1);
INSERT INTO t1 VALUES (7);
ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
INSERT INTO t2 VALUES (11);
ERROR 42000: SELECT, UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'
DROP TRIGGER t2_bi;
DROP TRIGGER t1_bi;
GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
CALL p2(NEW.i1);
INSERT INTO t1 VALUES (13);
ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
INSERT INTO t2 VALUES (17);
ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'
REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
DROP TRIGGER t2_bi;
DROP TRIGGER t1_bi;
GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
CALL p2(NEW.i1);
INSERT INTO t1 VALUES (19);
INSERT INTO t2 VALUES (23);
ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'
REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
DROP TRIGGER t2_bi;
DROP TRIGGER t1_bi;
GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
CALL p2(NEW.i1);
INSERT INTO t1 VALUES (29);
INSERT INTO t2 VALUES (31);
REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
DROP TRIGGER t2_bi;
DROP TRIGGER t1_bi;
DROP PROCEDURE p2;
DROP PROCEDURE p1;
GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37;
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
CALL p1(NEW.i1);
INSERT INTO t1 VALUES (41);
DROP PROCEDURE p1;
CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43;
INSERT INTO t1 VALUES (47);
ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
DROP PROCEDURE p1;
CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51;
INSERT INTO t1 VALUES (53);
ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
DROP PROCEDURE p1;
REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
DROP TRIGGER t1_bi;
DROP USER mysqltest_inv@localhost;
DROP USER mysqltest_dfn@localhost;
DROP TABLE t2;
DROP TABLE t1;
DROP DATABASE mysqltest_db1;
USE test;
End of 5.0 tests.
drop table if exists t1;
create table t1 (i int);
connection: default
lock tables t1 write;
connection: flush
flush tables with read lock;;
connection: default
create trigger t1_bi before insert on t1 for each row begin end;
unlock tables;
connection: flush
unlock tables;
select * from t1;
i
drop table t1;
CREATE DATABASE db1;
CREATE TABLE db1.t1 (a char(30)) ENGINE=MEMORY;
CREATE TRIGGER db1.trg AFTER INSERT ON db1.t1 FOR EACH ROW
INSERT INTO db1.t1 VALUES('Some very sensitive data goes here');
CREATE USER 'no_rights'@'localhost';
REVOKE ALL ON *.* FROM 'no_rights'@'localhost';
FLUSH PRIVILEGES;
SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS
WHERE trigger_schema = 'db1';
TRIGGER_NAME
SHOW CREATE TRIGGER db1.trg;
ERROR 42000: Access denied; you need (at least one of) the TRIGGER privilege(s) for this operation
DROP USER 'no_rights'@'localhost';
DROP DATABASE db1;
DROP DATABASE IF EXISTS mysqltest_db1;
CREATE DATABASE mysqltest_db1;
USE mysqltest_db1;
CREATE USER mysqltest_u1@localhost;
GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost;
CREATE TABLE t1 (
a1 int,
a2 int
);
INSERT INTO t1 VALUES (1, 20);
CREATE TRIGGER mysqltest_db1.upd_t1
BEFORE UPDATE ON t1 FOR EACH ROW SET new.a2 = 200;
CREATE TABLE t2 (
a1 int
);
INSERT INTO t2 VALUES (2);
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
UPDATE IGNORE t1, t2 SET t1.a1 = 2, t2.a1 = 3 WHERE t1.a1 = 1 AND t2.a1 = 2;
ERROR 42000: TRIGGER command denied to user 'mysqltest_u1'@'localhost' for table 't1'
DROP DATABASE mysqltest_db1;
DROP USER mysqltest_u1@localhost;
USE test;
End of 5.1 tests.
#
# WL#2284: Increase the length of a user name
#
CREATE DATABASE test1;
CREATE TABLE test1.t1 (
int_field INTEGER UNSIGNED NOT NULL,
char_field CHAR(10),
INDEX(`int_field`)
);
use test;
CREATE USER user_name_len_22_01234@localhost;
CREATE USER user_name_len_32_012345678901234@localhost;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_name_len_22_01234@localhost;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_name_len_32_012345678901234@localhost;
# Check that user_name_len_22_01234 has no SELECT permission ON t1
SELECT * FROM test1.t1;
ERROR 42000: SELECT command denied to user 'user_name_len_22_01234'@'localhost' for table 't1'
# Check that user_name_len_32_012345678901234 has no SELECT permission ON t1
SELECT * FROM test1.t1;
ERROR 42000: SELECT command denied to user 'user_name_len_32_012345678901234'@'localhost' for table 't1'
GRANT ALL ON test1.* TO user_name_len_32_012345678901234@localhost;
CREATE DEFINER=user_name_len_32_012345678901234@localhost
TRIGGER test1.t1_ai AFTER INSERT ON test1.t1 FOR EACH ROW SET @a = (SELECT COUNT(*) FROM test1.t1);
GRANT INSERT ON test1.t1 TO user_name_len_22_01234@localhost;
# Now user_name_len_22_01234 should be able to "SELECT" COUNT(*) FROM
# test1.t1 by using the trigger
set @a:=0;
INSERT INTO test1.t1 VALUES (1,'haha');
SELECT @a;
@a
1
CREATE DEFINER=user_name_len_33_0123456789012345@localhost
TRIGGER test1.t1_bi BEFORE INSERT ON test1.t1 FOR EACH ROW SET @a = (SELECT COUNT(*) FROM test1.t1);
ERROR HY000: String 'user_name_len_33_0123456789012345' is too long for user name (should be no longer than 32)
DROP DATABASE test1;
DROP USER user_name_len_22_01234@localhost;
DROP USER user_name_len_32_012345678901234@localhost;
#
# Bug#25209512:  CURRENT_TIMESTAMP PRODUCES ZEROS IN TRIGGER
#

# Case 1: With BEFORE INSERT triggers
# Scenario 1.1: Normal INSERT
SET TIMESTAMP= UNIX_TIMESTAMP("2017-03-30 07:07:07");
CREATE TABLE t1( a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP );
CREATE TRIGGER trigger_for_normal_insert BEFORE INSERT ON t1 FOR EACH ROW
SET @x:= NEW.a;
INSERT INTO t1() VALUES();
SELECT * FROM t1;
a
2017-03-30 07:07:07
# Without the patch, x contained zero timestamp.
SELECT @x;
@x
2017-03-30 07:07:07
DROP TABLE t1;

# Scenario 1.2: INSERT INTO... SELECT
CREATE TABLE t1(a DATETIME NOT NULL DEFAULT NOW(), b INT);
CREATE TRIGGER trigger_for_insert_select BEFORE INSERT ON t1 FOR EACH ROW
SET @x:= NEW.a;
INSERT INTO t1(b) SELECT 1;
SELECT * FROM t1;
a	b
2017-03-30 07:07:07	1
# Without the patch, x contained zero timestamp.
SELECT @x;
@x
2017-03-30 07:07:07
DROP TABLE t1;

# Scenario 1.3: Normal REPLACE
CREATE TABLE t1( a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP );
CREATE TRIGGER trigger_for_normal_replace BEFORE INSERT ON t1 FOR EACH ROW
SET @x:= NEW.a;
REPLACE INTO t1() VALUES();
SELECT * FROM t1;
a
2017-03-30 07:07:07
# Without the patch, x contained zero timestamp.
SELECT @x;
@x
2017-03-30 07:07:07
DROP TABLE t1;

# Scenario 1.4: REPLACE INTO... SELECT
CREATE TABLE t1(a DATETIME NOT NULL DEFAULT NOW(), b INT);
CREATE TRIGGER trigger_for_replace_select BEFORE INSERT ON t1 FOR EACH ROW
SET @x:= NEW.a;
REPLACE INTO t1(b) SELECT 1;
SELECT * FROM t1;
a	b
2017-03-30 07:07:07	1
# Without the patch, x contained zero timestamp.
SELECT @x;
@x
2017-03-30 07:07:07
DROP TABLE t1;

# Case 2: With BEFORE UPDATE triggers
# Scenario 2.1: Normal UPDATE with ON UPDATE NOW() clause for
#               the timestamp field.
SET TIMESTAMP= UNIX_TIMESTAMP("2017-04-11 09:09:09");
CREATE TABLE t1( a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE NOW(), b INT DEFAULT 1 );
CREATE TRIGGER trigger_before_update BEFORE UPDATE ON t1 FOR EACH ROW
SET @x:= NEW.a;
INSERT INTO t1 VALUES();
SELECT * FROM t1;
a	b
2017-04-11 09:09:09	1
SET TIMESTAMP= UNIX_TIMESTAMP("2017-04-12 10:10:10");
UPDATE t1 SET b= 2;
SELECT * FROM t1;
a	b
2017-04-12 10:10:10	2
# Without the patch, x contained the old timestamp.
SELECT @x;
@x
2017-04-12 10:10:10
DROP TABLE t1;

# Scenario 2.2: Normal UPDATE without ON UPDATE NOW() clause for
#               the timestamp field.
SET TIMESTAMP= UNIX_TIMESTAMP("2017-04-13 08:08:08");
CREATE TABLE t1( a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
b INT DEFAULT 1 );
CREATE TRIGGER trigger_before_update BEFORE UPDATE ON t1 FOR EACH ROW
SET @x:= NEW.a;
INSERT INTO t1 VALUES();
SELECT * FROM t1;
a	b
2017-04-13 08:08:08	1
SET TIMESTAMP= UNIX_TIMESTAMP("2017-05-04 05:05:05");
UPDATE t1 SET b= 2;
SELECT * FROM t1;
a	b
2017-04-13 08:08:08	2
# x contains the old timestamp because of the absence of
# ON UPDATE clause for the timestamp field.
SELECT @x;
@x
2017-04-13 08:08:08
DROP TABLE t1;

# Scenario 2.3: UPDATE with join
SET TIMESTAMP= UNIX_TIMESTAMP("2017-04-25 11:11:11");
CREATE TABLE t1( a DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP, b INT);
CREATE TABLE t2( d INT);
INSERT INTO t1(b) VALUES(1);
INSERT INTO t2 VALUES(2);
SELECT * FROM t1;
a	b
2017-04-25 11:11:11	1
SELECT * FROM t2;
d
2
CREATE TRIGGER trigger_before_update_with_join BEFORE UPDATE ON t1 FOR EACH ROW
SET @x:= NEW.a;
SET TIMESTAMP= UNIX_TIMESTAMP("2017-04-25 01:01:01");
UPDATE t1, t2 SET t1.b= t2.d;
SELECT * FROM t1;
a	b
2017-04-25 01:01:01	2
# Without the patch, x contained old timestamp
SELECT @x;
@x
2017-04-25 01:01:01
DROP TABLE t1, t2;

# Case 3: LOAD DATA INFILE... with BEFORE INSERT triggers
SET TIMESTAMP= UNIX_TIMESTAMP("2017-04-17 06:06:06");
CREATE TABLE t1( a TIMESTAMP NOT NULL DEFAULT NOW(), b INT );
CREATE TRIGGER trigger_for_load_infile BEFORE INSERT ON t1 FOR EACH ROW
SET @x:= NEW.a;
SELECT 1 INTO OUTFILE 't1.dat' FROM dual;
LOAD DATA INFILE 't1.dat' INTO TABLE t1(b);
SELECT * FROM t1;
a	b
2017-04-17 06:06:06	1
# Without the patch, x contained zero timestamp
SELECT @x;
@x
2017-04-17 06:06:06
DROP TABLE t1;
SET TIMESTAMP= DEFAULT;
#
# BUG 26626277: BUG IN "INSERT... ON DUPLICATE KEY UPDATE" QUERY
#
# Setup.
CREATE TABLE t1 (fld1 VARCHAR(64) NOT NULL,
fld2 INT DEFAULT 0, PRIMARY KEY (fld1));
CREATE TABLE t2 (fld1 VARCHAR(64) NOT NULL,
fld2 INT(11) DEFAULT NULL, PRIMARY KEY (fld1));
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1(fld1) VALUES (1100);
INSERT INTO t2 VALUES (1100, 40);
CREATE TRIGGER update_after_update
AFTER UPDATE ON t2 FOR EACH ROW
BEGIN
UPDATE t1 SET t1.fld2 = t1.fld2 + 1
WHERE t1.fld1 = NEW.fld1;
END$$
SELECT * FROM t1;
fld1	fld2
1100	0
SELECT * FROM t2;
fld1	fld2
1100	40
# Without patch, the after update trigger is not invoked.
INSERT INTO t2 (fld1) values (1100) ON DUPLICATE KEY UPDATE
fld2= 50;
SELECT * FROM t1;
fld1	fld2
1100	1
SELECT * FROM t2;
fld1	fld2
1100	50
# Without patch, the after update trigger is not invoked.
INSERT INTO t2 (fld1) values (1100) ON DUPLICATE KEY UPDATE
fld2= 50;
SELECT * FROM t1;
fld1	fld2
1100	2
SELECT * FROM t2;
fld1	fld2
1100	50
# Test added for coverage.
INSERT INTO t2 (fld1) values (1100) ON DUPLICATE KEY UPDATE
fld2= 60;
SELECT * FROM t1;
fld1	fld2
1100	3
SELECT * FROM t2;
fld1	fld2
1100	60
# Tests covering before update trigger.
# Setup.
TRUNCATE TABLE t1;
DROP TRIGGER update_after_update;
INSERT INTO t1(fld1) VALUES (1100);
CREATE TRIGGER update_before_update
BEFORE UPDATE ON t2 FOR EACH ROW
BEGIN
UPDATE t1 SET t1.fld2 = t1.fld2 + 1
WHERE t1.fld1 = NEW.fld1;
END$$
SELECT * FROM t1;
fld1	fld2
1100	0
SELECT * FROM t2;
fld1	fld2
1100	60
INSERT INTO t2 (fld1) values (1100) ON DUPLICATE KEY UPDATE
fld2= 50;
SELECT * FROM t1;
fld1	fld2
1100	1
SELECT * FROM t2;
fld1	fld2
1100	50
INSERT INTO t2 (fld1) values (1100) ON DUPLICATE KEY UPDATE
fld2= 50;
SELECT * FROM t1;
fld1	fld2
1100	2
SELECT * FROM t2;
fld1	fld2
1100	50
INSERT INTO t2 (fld1) values (1100) ON DUPLICATE KEY UPDATE
fld2= 60;
SELECT * FROM t1;
fld1	fld2
1100	3
SELECT * FROM t2;
fld1	fld2
1100	60
# Cleanup
DROP TRIGGER update_before_update;
DROP TABLE t1, t2;
#
# BUG#27544152 - TRIGGERS ARE FIRED IN INCORRECT ORDE
#
CREATE TABLE t1 (a INT);
CREATE TRIGGER _AI_1 AFTER INSERT ON t1 FOR EACH ROW SET
@t1_var=concat(@t1_var,'_AI_1');
CREATE TRIGGER _AI_2 AFTER INSERT ON t1 FOR EACH ROW SET
@t1_var=concat(@t1_var,'_AI_2');
CREATE TRIGGER _BU_2 BEFORE UPDATE ON t1 FOR EACH ROW SET
@t1_var=concat(@t1_var,'_BU_2');
CREATE TRIGGER _BD_2 BEFORE DELETE ON t1 FOR EACH ROW SET
@t1_var=concat(@t1_var,'_BD_2');
CREATE TRIGGER _AI_0 AFTER INSERT ON t1 FOR EACH ROW PRECEDES _AI_1 SET
@t1_var=concat(@t1_var,'_AI_0');
CREATE TRIGGER _AI_3 AFTER INSERT ON t1 FOR EACH ROW FOLLOWS _AI_2 SET
@t1_var=concat(@t1_var,'_AI_3');
SET @t1_var='Actual Result:   ';
INSERT INTO t1 VALUES (1);
SELECT @t1_var;
@t1_var
Actual Result:   _AI_0_AI_1_AI_2_AI_3
# Expected result : _AI_0_AI_1_AI_2_AI_3
DROP TABLE t1;
#
# Testing bug#28492272
#
# Test case 1: SET sql_mode='time_truncate_fractional';
CREATE TABLE t1 (i INT, j VARCHAR(32));
SET sql_mode='time_truncate_fractional';
# CREATE TRIGGER when sql_mode is set to time_truncate_fractional must
# not assert
CREATE TRIGGER t1_before_insert BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO
t3 VALUES (1, NEW.i, NULL, CONCAT("BI: ", NEW.j));
SET sql_mode=default;
DROP TRIGGER t1_before_insert;
DROP TABLE t1;
# Test case 2 sql_mode=2147483648*2;
# (This is actually equivalent to 'time_truncate_fractional', somehow)
CREATE TABLE t1 (i INT, j VARCHAR(32));
SET sql_mode=2147483648*2;
# CREATE TRIGGER when sql_mode is set to 2147483648*2 must not trigger
# assert
CREATE TRIGGER t1_before_insert BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO
t3 VALUES (1, NEW.i, NULL, CONCAT("BI: ", NEW.j));
SET sql_mode=default;
DROP TRIGGER t1_before_insert;
DROP TABLE t1;
#
# Bug#28122841 - CREATE EVENT/PROCEDURE/FUNCTION CRASHES WITH ACCENT INSENSITIVE NAMES.
#                Even trigger names are case and accent insensitive. Test case to
#                verify the same.
#
SET NAMES utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
CREATE TABLE t1 (f1 INT, f2 INT);
#
# Test case to verify triggers with case and accent insensitive names.
#
CREATE TRIGGER cafe BEFORE INSERT ON t1 FOR EACH ROW SET @sum= @sum + NEW.f1;
# Following statement errors out as trigger with name 'cafe' already
# exists on the table.
CREATE TRIGGER café BEFORE INSERT ON t1 FOR EACH ROW SET @sum= @sum + NEW.f1;
ERROR HY000: Trigger already exists
# Following statement is to verify operation with the upper case name.
CREATE TRIGGER CAFE BEFORE INSERT ON t1 FOR EACH ROW SET @sum= @sum + NEW.f1;
ERROR HY000: Trigger already exists
SHOW CREATE TRIGGER cAfé;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
cafe	#	#	#	#	#	#
DROP TRIGGER CaFé;
# Trigger with NAME_LEN size name.
CREATE TRIGGER очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_e
BEFORE INSERT ON t1 FOR EACH ROW SET @sum= @sum + NEW.f1;
CREATE TRIGGER очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_é
BEFORE INSERT ON t1 FOR EACH ROW SET @sum= @sum + NEW.f1;
ERROR HY000: Trigger already exists
CREATE TRIGGER очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_E
BEFORE INSERT ON t1 FOR EACH ROW SET @sum= @sum + NEW.f1;
ERROR HY000: Trigger already exists
SHOW CREATE TRIGGER очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_é;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_e	#	#	#	#	#	#
DROP TRIGGER очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_é;
DROP TABLE t1;
SET NAMES default;