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 't1.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;
Warnings:
Warning	1287	'RESET SLAVE' is deprecated and will be removed in a future release. Please use RESET REPLICA instead
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_ai	test	t1	set @b:=new.id
test	t1_bi	test	t1	set @a:=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_ai	test	t2	set @b:=new.id
test	t1_bi	test	t2	set @a:=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_ai	test	t3	set @b:=new.id
test	t1_bi	test	t3	set @a:=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_ai	test	t4	set @b:=new.id
test	t1_bi	test	t4	set @a:=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 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);
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
INSERT INTO t1 VALUES (2,'test3') ON DUPLICATE KEY UPDATE val=VALUES(val);
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
INSERT INTO t1 VALUES (3,'test4') ON DUPLICATE KEY UPDATE val=VALUES(val);
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
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 REPLICATION SOURCE TO SOURCE_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 't1.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 't1.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 't1.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;
Warnings:
Warning	4005	User 'user_name_robert_golebiowski'@'oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char' is referenced as a definer account in a trigger.
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;
Warnings:
Warning	4005	User 'mysqltest_u1'@'localhost' is referenced as a definer account in a trigger.
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;
#
# Bug#37337527 >= 8.0.27 Error 1048 when updating row with empty_sql mode
#
CREATE TABLE t1 (c1 INT, c2 INT NOT NULL);
CREATE TRIGGER t_t1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN END;
SET SESSION sql_mode = '';
INSERT INTO t1(c1) VALUES (1);
Warnings:
Warning	1364	Field 'c2' doesn't have a default value
SELECT * FROM t1;
c1	c2
1	0
UPDATE t1 SET c2=3 WHERE c1=1;
DROP TABLE t1;
SET sql_mode=default;