1
drop table if exists t1;
2
create table t1 (a varchar(16), b int) engine=innodb;
3
create trigger t1_bi before insert on t1 for each row
5
set new.a := upper(new.a);
6
set new.b := new.b + 3;
8
select trigger_schema, trigger_name, event_object_schema,
9
event_object_table, action_statement from information_schema.triggers
10
where event_object_schema = 'test' and event_object_table = 't1';
11
trigger_schema trigger_name event_object_schema event_object_table action_statement
12
test t1_bi test t1 begin
13
set new.a := upper(new.a);
14
set new.b := new.b + 3;
16
insert into t1 values ('The Lion', 10);
21
Table Op Msg_type Msg_text
22
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
23
test.t1 optimize status OK
24
select trigger_schema, trigger_name, event_object_schema,
25
event_object_table, action_statement from information_schema.triggers
26
where event_object_schema = 'test' and event_object_table = 't1';
27
trigger_schema trigger_name event_object_schema event_object_table action_statement
28
test t1_bi test t1 begin
29
set new.a := upper(new.a);
30
set new.b := new.b + 3;
32
insert into t1 values ('The Unicorn', 20);
37
alter table t1 add column c int default 0;
38
select trigger_schema, trigger_name, event_object_schema,
39
event_object_table, action_statement from information_schema.triggers
40
where event_object_schema = 'test' and event_object_table = 't1';
41
trigger_schema trigger_name event_object_schema event_object_table action_statement
42
test t1_bi test t1 begin
43
set new.a := upper(new.a);
44
set new.b := new.b + 3;
46
insert into t1 values ('Alice', 30, 1);
52
alter table t1 rename to t1;
53
select trigger_schema, trigger_name, event_object_schema,
54
event_object_table, action_statement from information_schema.triggers
55
where event_object_schema = 'test' and event_object_table = 't1';
56
trigger_schema trigger_name event_object_schema event_object_table action_statement
57
test t1_bi test t1 begin
58
set new.a := upper(new.a);
59
set new.b := new.b + 3;
61
insert into t1 values ('The Crown', 40, 1);
68
alter table t1 rename to t1, add column d int default 0;
69
select trigger_schema, trigger_name, event_object_schema,
70
event_object_table, action_statement from information_schema.triggers
71
where event_object_schema = 'test' and event_object_table = 't1';
72
trigger_schema trigger_name event_object_schema event_object_table action_statement
73
test t1_bi test t1 begin
74
set new.a := upper(new.a);
75
set new.b := new.b + 3;
77
insert into t1 values ('The Pie', 50, 1, 1);
87
Bug#26141 mixing table types in trigger causes full
88
table lock on innodb table
90
Ensure we do not open and lock tables for the triggers we do not
93
drop table if exists t1, t2, t3;
94
drop trigger if exists trg_bug26141_au;
95
drop trigger if exists trg_bug26141_ai;
96
create table t1 (c int primary key) engine=innodb;
97
create table t2 (c int) engine=myisam;
98
create table t3 (c int) engine=myisam;
99
insert into t1 (c) values (1);
100
create trigger trg_bug26141_ai after insert on t1
103
insert into t2 (c) values (1);
104
# We need the 'sync' lock to synchronously wait in connection 2 till
105
# the moment when the trigger acquired all the locks.
106
select release_lock("lock_bug26141_sync") into @a;
107
# 1000 is time in seconds of lock wait timeout -- this is a way
108
# to cause a manageable sleep up to 1000 seconds
109
select get_lock("lock_bug26141_wait", 1000) into @a;
111
create trigger trg_bug26141_au after update on t1
114
insert into t3 (c) values (1);
116
select get_lock("lock_bug26141_wait", 0);
117
get_lock("lock_bug26141_wait", 0)
119
select get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0);
120
get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0)
122
insert into t1 (c) values (2);
123
select get_lock("lock_bug26141_sync", 1000);
124
get_lock("lock_bug26141_sync", 1000)
126
update t1 set c=3 where c=1;
127
select release_lock("lock_bug26141_sync");
128
release_lock("lock_bug26141_sync")
130
select release_lock("lock_bug26141_wait");
131
release_lock("lock_bug26141_wait")
143
drop table t1, t2, t3;
144
DROP TABLE IF EXISTS t1;
145
DROP TABLE IF EXISTS t2;
146
CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=innodb;
147
CREATE TABLE t2(b INT, FOREIGN KEY(b) REFERENCES t1(a)) ENGINE=innodb;
148
INSERT INTO t1 VALUES (1);
149
CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW SET @a = 1;
150
CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW SET @b = 1;
157
INSERT INTO t1 VALUES (1);
165
Trigger fired multiple times leads to gaps in auto_increment sequence
166
create table t1 (a int, val char(1)) engine=InnoDB;
167
create table t2 (b int auto_increment primary key,
168
val char(1)) engine=InnoDB;
169
create trigger t1_after_insert after
170
insert on t1 for each row insert into t2 set val=NEW.val;
171
insert into t1 values ( 123, 'a'), ( 123, 'b'), ( 123, 'c'),
172
(123, 'd'), (123, 'e'), (123, 'f'), (123, 'g');
173
insert into t1 values ( 654, 'a'), ( 654, 'b'), ( 654, 'c'),
174
(654, 'd'), (654, 'e'), (654, 'f'), (654, 'g');
175
select * from t2 order by b;
191
drop trigger t1_after_insert;