drop table if exists t1;
create table t1 (a varchar(16), b int) engine=innodb;
create trigger t1_bi before insert on t1 for each row
begin
set new.a := upper(new.a);
set new.b := new.b + 3;
end|
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' and event_object_table = 't1';
trigger_schema	trigger_name	event_object_schema	event_object_table	action_statement
test	t1_bi	test	t1	begin
set new.a := upper(new.a);
set new.b := new.b + 3;
end
insert into t1 values ('The Lion', 10);
select * from t1;
a	b
THE LION	13
optimize table t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
test.t1	optimize	status	OK
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' and event_object_table = 't1';
trigger_schema	trigger_name	event_object_schema	event_object_table	action_statement
test	t1_bi	test	t1	begin
set new.a := upper(new.a);
set new.b := new.b + 3;
end
insert into t1 values ('The Unicorn', 20);
select * from t1;
a	b
THE LION	13
THE UNICORN	23
alter table t1 add column c int default 0;
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' and event_object_table = 't1';
trigger_schema	trigger_name	event_object_schema	event_object_table	action_statement
test	t1_bi	test	t1	begin
set new.a := upper(new.a);
set new.b := new.b + 3;
end
insert into t1 values ('Alice', 30, 1);
select * from t1;
a	b	c
THE LION	13	0
THE UNICORN	23	0
ALICE	33	1
alter table t1 rename to t1;
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' and event_object_table = 't1';
trigger_schema	trigger_name	event_object_schema	event_object_table	action_statement
test	t1_bi	test	t1	begin
set new.a := upper(new.a);
set new.b := new.b + 3;
end
insert into t1 values ('The Crown', 40, 1);
select * from t1;
a	b	c
THE LION	13	0
THE UNICORN	23	0
ALICE	33	1
THE CROWN	43	1
alter table t1 rename to t1, add column d int default 0;
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' and event_object_table = 't1';
trigger_schema	trigger_name	event_object_schema	event_object_table	action_statement
test	t1_bi	test	t1	begin
set new.a := upper(new.a);
set new.b := new.b + 3;
end
insert into t1 values ('The Pie', 50, 1, 1);
select * from t1;
a	b	c	d
THE LION	13	0	0
THE UNICORN	23	0	0
ALICE	33	1	0
THE CROWN	43	1	0
THE PIE	53	1	1
drop table t1;

Bug#26141 mixing table types in trigger causes full
table lock on innodb table

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

drop table if exists t1, t2, t3;
drop trigger if exists trg_bug26141_au;
drop trigger if exists trg_bug26141_ai;
create table t1 (c int primary key) engine=innodb;
create table t2 (c int) engine=myisam;
create table t3 (c int) engine=myisam;
insert into t1 (c) values (1);
create trigger trg_bug26141_ai after insert on t1
for each row
begin
insert into t2 (c) values (1);
# We need the 'sync' lock to synchronously wait in connection 2 till 
# the moment when the trigger acquired all the locks.
select release_lock("lock_bug26141_sync") into @a;
# 1000 is time in seconds of lock wait timeout -- this is a way
# to cause a manageable sleep up to 1000 seconds
select get_lock("lock_bug26141_wait", 1000) into @a;
end|
create trigger trg_bug26141_au after update on t1
for each row
begin
insert into t3 (c) values (1);
end|
select get_lock("lock_bug26141_wait", 0);
get_lock("lock_bug26141_wait", 0)
1
select get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0);
get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0)
1
insert into t1 (c) values (2);
select get_lock("lock_bug26141_sync", 1000);
get_lock("lock_bug26141_sync", 1000)
1
update t1 set c=3 where c=1;
select release_lock("lock_bug26141_sync");
release_lock("lock_bug26141_sync")
1
select release_lock("lock_bug26141_wait");
release_lock("lock_bug26141_wait")
1
select * from t1;
c
2
3
select * from t2;
c
1
select * from t3;
c
1
drop table t1, t2, t3;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=innodb;
CREATE TABLE t2(b INT, FOREIGN KEY(b) REFERENCES t1(a)) ENGINE=innodb;
INSERT INTO t1 VALUES (1);
CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW SET @a = 1;
CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW SET @b = 1;
SET @a = 0;
SET @b = 0;
TRUNCATE t1;
ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `test`.`t1` (`a`))
SELECT @a, @b;
@a	@b
0	0
DELETE FROM t1;
SELECT @a, @b;
@a	@b
1	1
INSERT INTO t1 VALUES (1);
DELETE FROM t1;
SELECT @a, @b;
@a	@b
1	1
DROP TABLE t2, t1;
End of 5.0 tests
BUG#31612
Trigger fired multiple times leads to gaps in auto_increment sequence
create table t1 (a int, val char(1)) engine=InnoDB;
create table t2 (b int auto_increment primary key,
val char(1)) engine=InnoDB;
create trigger t1_after_insert after
insert on t1 for each row insert into t2 set val=NEW.val;
insert into t1 values ( 123, 'a'), ( 123, 'b'), ( 123, 'c'),
(123, 'd'), (123, 'e'), (123, 'f'), (123, 'g');
insert into t1 values ( 654, 'a'), ( 654, 'b'), ( 654, 'c'),
(654, 'd'), (654, 'e'), (654, 'f'), (654, 'g');
select * from t2 order by b;
b	val
1	a
2	b
3	c
4	d
5	e
6	f
7	g
8	a
9	b
10	c
11	d
12	e
13	f
14	g
drop trigger t1_after_insert;
drop table t1,t2;