1
drop table if exists t1, t2, t3, t4;
2
drop view if exists v1;
3
drop database if exists mysqltest;
4
drop function if exists f1;
5
drop function if exists f2;
6
drop procedure if exists p1;
7
create table t1 (i int);
8
create trigger trg before insert on t1 for each row set @a:=1;
13
insert into t1 values (1);
18
create trigger trg before insert on t1 for each row set @a:=new.i;
19
insert into t1 values (123);
25
create table t1 (i int not null, j int);
26
create trigger trg before insert on t1 for each row
29
set new.j:= new.i * 10;
32
insert into t1 (i) values (1)|
33
insert into t1 (i,j) values (2, 3)|
40
create table t1 (i int not null primary key);
41
create trigger trg after insert on t1 for each row
42
set @a:= if(@a,concat(@a, ":", new.i), new.i);
44
insert into t1 values (2),(3),(4),(5);
50
create table t1 (aid int not null primary key, balance int not null default 0);
51
insert into t1 values (1, 1000), (2,3000);
52
create trigger trg before update on t1 for each row
54
declare loc_err varchar(255);
55
if abs(new.balance - old.balance) > 1000 then
56
set new.balance:= old.balance;
57
set loc_err := concat("Too big change for aid = ", new.aid);
58
set @update_failed:= if(@update_failed, concat(@a, ":", loc_err), loc_err);
61
set @update_failed:=""|
62
update t1 set balance=1500|
63
select @update_failed;
66
Too big change for aid = 2
72
create table t1 (i int);
73
insert into t1 values (1),(2),(3),(4);
74
create trigger trg after update on t1 for each row
75
set @total_change:=@total_change + new.i - old.i;
83
create table t1 (i int);
84
insert into t1 values (1),(2),(3),(4);
85
create trigger trg before delete on t1 for each row
86
set @del_sum:= @del_sum + old.i;
88
delete from t1 where i <= 3;
94
create table t1 (i int);
95
insert into t1 values (1),(2),(3),(4);
96
create trigger trg after delete on t1 for each row set @del:= 1;
98
delete from t1 where i <> 0;
104
create table t1 (i int, j int);
105
create trigger trg1 before insert on t1 for each row
111
create trigger trg2 before update on t1 for each row
113
if old.i % 2 = 0 then
117
create trigger trg3 after update on t1 for each row
124
insert into t1 values (1,2),(2,3),(3,14);
146
create table t1 (id int not null primary key, data int);
147
create trigger t1_bi before insert on t1 for each row
148
set @log:= concat(@log, "(BEFORE_INSERT: new=(id=", new.id, ", data=", new.data,"))");
149
create trigger t1_ai after insert on t1 for each row
150
set @log:= concat(@log, "(AFTER_INSERT: new=(id=", new.id, ", data=", new.data,"))");
151
create trigger t1_bu before update on t1 for each row
152
set @log:= concat(@log, "(BEFORE_UPDATE: old=(id=", old.id, ", data=", old.data,
153
") new=(id=", new.id, ", data=", new.data,"))");
154
create trigger t1_au after update on t1 for each row
155
set @log:= concat(@log, "(AFTER_UPDATE: old=(id=", old.id, ", data=", old.data,
156
") new=(id=", new.id, ", data=", new.data,"))");
157
create trigger t1_bd before delete on t1 for each row
158
set @log:= concat(@log, "(BEFORE_DELETE: old=(id=", old.id, ", data=", old.data,"))");
159
create trigger t1_ad after delete on t1 for each row
160
set @log:= concat(@log, "(AFTER_DELETE: old=(id=", old.id, ", data=", old.data,"))");
162
insert into t1 values (1, 1);
165
(BEFORE_INSERT: new=(id=1, data=1))(AFTER_INSERT: new=(id=1, data=1))
167
insert ignore t1 values (1, 2);
170
(BEFORE_INSERT: new=(id=1, data=2))
172
insert into t1 (id, data) values (1, 3), (2, 2) on duplicate key update data= data + 1;
175
(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))
177
replace t1 values (1, 4), (3, 3);
180
(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))
184
replace t1 values (1, 5);
187
(BEFORE_INSERT: new=(id=1, data=5))(AFTER_INSERT: new=(id=1, data=5))
189
create table t1 (id int primary key, data varchar(10), fk int);
190
create table t2 (event varchar(100));
191
create table t3 (id int primary key);
192
create trigger t1_ai after insert on t1 for each row
193
insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "'"));
194
insert into t1 (id, data) values (1, "one"), (2, "two");
201
INSERT INTO t1 id=1 data='one'
202
INSERT INTO t1 id=2 data='two'
204
create trigger t1_bi before insert on t1 for each row
206
if exists (select id from t3 where id=new.fk) then
207
insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "' fk=", new.fk));
209
insert into t2 values (concat("INSERT INTO t1 FAILED id=", new.id, " data='", new.data, "' fk=", new.fk));
213
insert into t3 values (1);
214
insert into t1 values (4, "four", 1), (5, "five", 2);
215
ERROR 23000: Column 'id' cannot be null
223
INSERT INTO t1 id=1 data='one'
224
INSERT INTO t1 id=2 data='two'
225
INSERT INTO t1 id=4 data='four' fk=1
226
INSERT INTO t1 FAILED id=5 data='five' fk=2
227
drop table t1, t2, t3;
228
create table t1 (id int primary key, data varchar(10));
229
create table t2 (seq int);
230
insert into t2 values (10);
231
create function f1 () returns int return (select max(seq) from t2);
232
create trigger t1_bi before insert on t1 for each row
234
if new.id > f1() then
238
insert into t1 values (1, "first");
239
insert into t1 values (f1(), "max");
246
create table t1 (id int primary key, fk_t2 int);
247
create table t2 (id int primary key, fk_t3 int);
248
create table t3 (id int primary key);
249
insert into t1 values (1,1), (2,1), (3,2);
250
insert into t2 values (1,1), (2,2);
251
insert into t3 values (1), (2);
252
create trigger t3_ad after delete on t3 for each row
253
delete from t2 where fk_t3=old.id;
254
create trigger t2_ad after delete on t2 for each row
255
delete from t1 where fk_t2=old.id;
256
delete from t3 where id = 1;
257
select * from t1 left join (t2 left join t3 on t2.fk_t3 = t3.id) on t1.fk_t2 = t2.id;
260
drop table t1, t2, t3;
261
create table t1 (id int primary key, copy int);
262
create table t2 (id int primary key, data int);
263
insert into t2 values (1,1), (2,2);
264
create trigger t1_bi before insert on t1 for each row
265
set new.copy= (select data from t2 where id = new.id);
266
create trigger t1_bu before update on t1 for each row
267
set new.copy= (select data from t2 where id = new.id);
268
insert into t1 values (1,3), (2,4), (3,3);
269
update t1 set copy= 1 where id = 2;
276
create table t1 (i int);
277
create table t3 (i int);
278
create trigger trg before insert on t1 for each row set @a:= old.i;
279
ERROR HY000: There is no OLD row in on INSERT trigger
280
create trigger trg before delete on t1 for each row set @a:= new.i;
281
ERROR HY000: There is no NEW row in on DELETE trigger
282
create trigger trg before update on t1 for each row set old.i:=1;
283
ERROR HY000: Updating of OLD row is not allowed in trigger
284
create trigger trg before delete on t1 for each row set new.i:=1;
285
ERROR HY000: There is no NEW row in on DELETE trigger
286
create trigger trg after update on t1 for each row set new.i:=1;
287
ERROR HY000: Updating of NEW row is not allowed in after trigger
288
create trigger trg before update on t1 for each row set new.j:=1;
289
ERROR 42S22: Unknown column 'j' in 'NEW'
290
create trigger trg before update on t1 for each row set @a:=old.j;
291
ERROR 42S22: Unknown column 'j' in 'OLD'
292
create trigger trg before insert on t2 for each row set @a:=1;
293
ERROR 42S02: Table 'test.t2' doesn't exist
294
create trigger trg before insert on t1 for each row set @a:=1;
295
create trigger trg after insert on t1 for each row set @a:=1;
296
ERROR HY000: Trigger already exists
297
create trigger trg2 before insert on t1 for each row set @a:=1;
298
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
299
create trigger trg before insert on t3 for each row set @a:=1;
300
ERROR HY000: Trigger already exists
301
create trigger trg2 before insert on t3 for each row set @a:=1;
305
ERROR HY000: Trigger does not exist
306
create view v1 as select * from t1;
307
create trigger trg before insert on v1 for each row set @a:=1;
308
ERROR HY000: 'test.v1' is not BASE TABLE
312
create temporary table t1 (i int);
313
create trigger trg before insert on t1 for each row set @a:=1;
314
ERROR HY000: Trigger's 't1' is view or temporary table
316
create table t1 (x1col char);
317
create trigger tx1 before insert on t1 for each row set new.x1col = 'x';
318
insert into t1 values ('y');
321
create table t1 (i int) engine=myisam;
322
insert into t1 values (1), (2);
323
create trigger trg1 before delete on t1 for each row set @del_before:= @del_before + old.i;
324
create trigger trg2 after delete on t1 for each row set @del_after:= @del_after + old.i;
325
set @del_before:=0, @del_after:= 0;
327
select @del_before, @del_after;
328
@del_before @del_after
333
create table t1 (a int);
334
create trigger trg1 before insert on t1 for each row set new.a= 10;
336
create table t1 (a int);
337
insert into t1 values ();
342
create database mysqltest;
344
create table t1 (i int);
345
create trigger trg1 before insert on t1 for each row set @a:= 1;
346
drop database mysqltest;
348
create database mysqltest;
349
create table mysqltest.t1 (i int);
350
create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1;
351
ERROR HY000: Trigger in wrong schema
353
create trigger test.trg1 before insert on t1 for each row set @a:= 1;
354
ERROR HY000: Trigger in wrong schema
355
drop database mysqltest;
357
create table t1 (i int, j int default 10, k int not null, key (k));
358
create table t2 (i int);
359
insert into t1 (i, k) values (1, 1);
360
insert into t2 values (1);
361
create trigger trg1 before update on t1 for each row set @a:= @a + new.j - old.j;
362
create trigger trg2 after update on t1 for each row set @b:= "Fired";
364
update t1, t2 set j = j + 10 where t1.i = t2.i;
368
insert into t1 values (2, 13, 2);
369
insert into t2 values (2);
371
update t1, t2 set j = j + 15 where t1.i = t2.i and t1.k >= 2;
375
create trigger trg3 before delete on t1 for each row set @c:= @c + old.j;
376
create trigger trg4 before delete on t2 for each row set @d:= @d + old.i;
377
create trigger trg5 after delete on t1 for each row set @e:= "After delete t1 fired";
378
create trigger trg6 after delete on t2 for each row set @f:= "After delete t2 fired";
379
set @c:= 0, @d:= 0, @e:= "", @f:= "";
380
delete t1, t2 from t1, t2 where t1.i = t2.i;
381
select @c, @d, @e, @f;
383
48 3 After delete t1 fired After delete t2 fired
385
create table t1 (i int, j int default 10)|
386
create table t2 (i int)|
387
insert into t2 values (1), (2)|
388
create trigger trg1 before insert on t1 for each row
394
create trigger trg2 after insert on t1 for each row set @a:= 1|
396
insert into t1 (i) select * from t2|
405
create table t1 (i int, j int, k int);
406
create trigger trg1 before insert on t1 for each row set new.k = new.i;
407
create trigger trg2 after insert on t1 for each row set @b:= "Fired";
409
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@a, i);
410
select *, @b from t1;
415
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, j);
416
select *, @b from t1;
424
create table t1 (i int, at int, k int, key(k)) engine=myisam;
425
create table t2 (i int);
426
insert into t1 values (1, 1, 1);
427
insert into t2 values (1), (2), (3);
428
create trigger ai after insert on t1 for each row set @a:= new.at;
429
create trigger au after update on t1 for each row set @a:= new.at;
430
create trigger ad after delete on t1 for each row set @a:= old.at;
431
alter table t1 drop column at;
435
insert into t1 values (2, 1);
436
ERROR 42S22: Unknown column 'at' in 'NEW'
441
update t1 set k = 2 where i = 2;
442
ERROR 42S22: Unknown column 'at' in 'NEW'
447
delete from t1 where i = 2;
448
ERROR 42S22: Unknown column 'at' in 'OLD'
452
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k);
453
ERROR 42S22: Unknown column 'at' in 'NEW'
458
insert into t1 select 3, 3;
459
ERROR 42S22: Unknown column 'at' in 'NEW'
465
update t1, t2 set k = k + 10 where t1.i = t2.i;
466
ERROR 42S22: Unknown column 'at' in 'NEW'
472
update t1, t2 set k = k + 10 where t1.i = t2.i and k < 3;
473
ERROR 42S22: Unknown column 'at' in 'NEW'
479
delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;
480
ERROR 42S22: Unknown column 'at' in 'OLD'
485
delete t2, t1 from t2 straight_join t1 where t1.i = t2.i;
486
ERROR 42S22: Unknown column 'at' in 'OLD'
490
alter table t1 add primary key (i);
491
insert into t1 values (3, 4) on duplicate key update k= k + 10;
492
ERROR 42S22: Unknown column 'at' in 'NEW'
496
replace into t1 values (3, 3);
497
ERROR 42S22: Unknown column 'at' in 'OLD'
501
create table t1 (i int, bt int, k int, key(k)) engine=myisam;
502
create table t2 (i int);
503
insert into t1 values (1, 1, 1), (2, 2, 2);
504
insert into t2 values (1), (2), (3);
505
create trigger bi before insert on t1 for each row set @a:= new.bt;
506
create trigger bu before update on t1 for each row set @a:= new.bt;
507
create trigger bd before delete on t1 for each row set @a:= old.bt;
508
alter table t1 drop column bt;
509
insert into t1 values (3, 3);
510
ERROR 42S22: Unknown column 'bt' in 'NEW'
516
ERROR 42S22: Unknown column 'bt' in 'NEW'
522
ERROR 42S22: Unknown column 'bt' in 'OLD'
527
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k);
528
ERROR 42S22: Unknown column 'bt' in 'NEW'
533
insert into t1 select 3, 3;
534
ERROR 42S22: Unknown column 'bt' in 'NEW'
539
update t1, t2 set k = k + 10 where t1.i = t2.i;
540
ERROR 42S22: Unknown column 'bt' in 'NEW'
545
update t1, t2 set k = k + 10 where t1.i = t2.i and k < 2;
546
ERROR 42S22: Unknown column 'bt' in 'NEW'
551
delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;
552
ERROR 42S22: Unknown column 'bt' in 'OLD'
557
delete t2, t1 from t2 straight_join t1 where t1.i = t2.i;
558
ERROR 42S22: Unknown column 'bt' in 'OLD'
563
alter table t1 add primary key (i);
565
insert into t1 values (2, 4) on duplicate key update k= k + 10;
566
ERROR 42S22: Unknown column 'bt' in 'NEW'
571
replace into t1 values (2, 4);
572
ERROR 42S22: Unknown column 'bt' in 'OLD'
578
drop function if exists bug5893;
579
create table t1 (col1 int, col2 int);
580
insert into t1 values (1, 2);
581
create function bug5893 () returns int return 5;
582
create trigger t1_bu before update on t1 for each row set new.col1= bug5893();
583
drop function bug5893;
584
update t1 set col2 = 4;
585
ERROR 42000: FUNCTION test.bug5893 does not exist
589
create table t1 ("t1 column" int);
590
create trigger t1_bi before insert on t1 for each row set new."t1 column" = 5;
592
insert into t1 values (0);
593
create trigger t1_af after insert on t1 for each row set @a=10;
594
insert into t1 values (0);
603
Trigger Event Table Statement Timing Created sql_mode Definer
604
t1_bi INSERT t1 set new."t1 column" = 5 BEFORE # REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI root@localhost
605
t1_af INSERT t1 set @a=10 AFTER # root@localhost
607
set sql_mode="traditional";
608
create table t1 (a date);
609
insert into t1 values ('2004-01-00');
610
ERROR 22007: Incorrect date value: '2004-01-00' for column 'a' at row 1
612
create trigger t1_bi before insert on t1 for each row set new.a = '2004-01-00';
613
set sql_mode="traditional";
614
insert into t1 values ('2004-01-01');
618
set sql_mode=default;
619
show create table t1;
621
t1 CREATE TABLE `t1` (
622
`a` date DEFAULT NULL
623
) ENGINE=PBXT DEFAULT CHARSET=latin1
625
Trigger Event Table Statement Timing Created sql_mode Definer
626
t1_bi INSERT t1 set new.a = '2004-01-00' BEFORE # root@localhost
628
create table t1 (id int);
629
create trigger t1_ai after insert on t1 for each row reset query cache;
630
ERROR 0A000: RESET is not allowed in stored function or trigger
631
create trigger t1_ai after insert on t1 for each row reset master;
632
ERROR 0A000: RESET is not allowed in stored function or trigger
633
create trigger t1_ai after insert on t1 for each row reset slave;
634
ERROR 0A000: RESET is not allowed in stored function or trigger
635
create trigger t1_ai after insert on t1 for each row flush hosts;
636
ERROR 0A000: FLUSH is not allowed in stored function or trigger
637
create trigger t1_ai after insert on t1 for each row flush tables with read lock;
638
ERROR 0A000: FLUSH is not allowed in stored function or trigger
639
create trigger t1_ai after insert on t1 for each row flush logs;
640
ERROR 0A000: FLUSH is not allowed in stored function or trigger
641
create trigger t1_ai after insert on t1 for each row flush status;
642
ERROR 0A000: FLUSH is not allowed in stored function or trigger
643
create trigger t1_ai after insert on t1 for each row flush slave;
644
ERROR 0A000: FLUSH is not allowed in stored function or trigger
645
create trigger t1_ai after insert on t1 for each row flush master;
646
ERROR 0A000: FLUSH is not allowed in stored function or trigger
647
create trigger t1_ai after insert on t1 for each row flush des_key_file;
648
ERROR 0A000: FLUSH is not allowed in stored function or trigger
649
create trigger t1_ai after insert on t1 for each row flush user_resources;
650
ERROR 0A000: FLUSH is not allowed in stored function or trigger
651
create trigger t1_ai after insert on t1 for each row flush tables;
652
ERROR 0A000: FLUSH is not allowed in stored function or trigger
653
create trigger t1_ai after insert on t1 for each row flush privileges;
654
ERROR 0A000: FLUSH is not allowed in stored function or trigger
655
drop procedure if exists p1;
656
create trigger t1_ai after insert on t1 for each row call p1();
657
create procedure p1() flush tables;
658
insert into t1 values (0);
659
ERROR 0A000: FLUSH is not allowed in stored function or trigger
661
create procedure p1() reset query cache;
662
insert into t1 values (0);
663
ERROR 0A000: RESET is not allowed in stored function or trigger
665
create procedure p1() reset master;
666
insert into t1 values (0);
667
ERROR 0A000: RESET is not allowed in stored function or trigger
669
create procedure p1() reset slave;
670
insert into t1 values (0);
671
ERROR 0A000: RESET is not allowed in stored function or trigger
673
create procedure p1() flush hosts;
674
insert into t1 values (0);
675
ERROR 0A000: FLUSH is not allowed in stored function or trigger
677
create procedure p1() flush privileges;
678
insert into t1 values (0);
679
ERROR 0A000: FLUSH is not allowed in stored function or trigger
681
create procedure p1() flush tables with read lock;
682
insert into t1 values (0);
683
ERROR 0A000: FLUSH is not allowed in stored function or trigger
685
create procedure p1() flush tables;
686
insert into t1 values (0);
687
ERROR 0A000: FLUSH is not allowed in stored function or trigger
689
create procedure p1() flush logs;
690
insert into t1 values (0);
691
ERROR 0A000: FLUSH is not allowed in stored function or trigger
693
create procedure p1() flush status;
694
insert into t1 values (0);
695
ERROR 0A000: FLUSH is not allowed in stored function or trigger
697
create procedure p1() flush slave;
698
insert into t1 values (0);
699
ERROR 0A000: FLUSH is not allowed in stored function or trigger
701
create procedure p1() flush master;
702
insert into t1 values (0);
703
ERROR 0A000: FLUSH is not allowed in stored function or trigger
705
create procedure p1() flush des_key_file;
706
insert into t1 values (0);
707
ERROR 0A000: FLUSH is not allowed in stored function or trigger
709
create procedure p1() flush user_resources;
710
insert into t1 values (0);
711
ERROR 0A000: FLUSH is not allowed in stored function or trigger
714
create table t1 (id int, data int, username varchar(16));
715
insert into t1 (id, data) values (1, 0);
716
create trigger t1_whoupdated before update on t1 for each row
718
declare user varchar(32);
720
select user() into user;
721
set NEW.username = user;
722
select count(*) from ((select 1) union (select 2)) as d1 into i;
724
update t1 set data = 1;
725
update t1 set data = 2;
727
create table t1 (c1 int, c2 datetime);
728
create trigger tr1 before insert on t1 for each row
730
set new.c2= '2004-04-01';
733
ERROR 0A000: Not allowed to return a result set from a trigger
734
insert into t1 (c1) values (1),(2),(3);
740
drop procedure if exists bug11587;
741
create procedure bug11587(x char(16))
744
select "hello again";
746
create trigger tr1 before insert on t1 for each row
749
set new.c2= '2004-04-02';
751
insert into t1 (c1) values (4),(5),(6);
752
ERROR 0A000: Not allowed to return a result set from a trigger
758
drop procedure bug11587;
760
create table t1 (f1 integer);
761
create table t2 (f2 integer);
762
create trigger t1_ai after insert on t1
763
for each row insert into t2 values (new.f1+1);
764
create trigger t2_ai after insert on t2
765
for each row insert into t1 values (new.f2+1);
766
set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth;
767
set @@max_sp_recursion_depth=100;
768
insert into t1 values (1);
769
ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
770
set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS;
779
create trigger t1_bu before update on t1
780
for each row insert into t1 values (2);
781
update t1 set f1= 10;
782
ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
787
create trigger t1_bu before update on t1
788
for each row delete from t1 where f1=new.f1;
789
update t1 set f1= 10;
790
ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
795
create trigger t1_bi before insert on t1
796
for each row set new.f1=(select sum(f1) from t1);
797
insert into t1 values (3);
804
create table t1 (id int);
805
create table t2 (id int);
806
create trigger t1_bi before insert on t1 for each row insert into t2 values (new.id);
807
prepare stmt1 from "insert into t1 values (10)";
808
create procedure p1() insert into t1 values (10);
813
deallocate prepare stmt1;
815
create table t3 (id int);
816
create trigger t1_bi after insert on t1 for each row insert into t2 values (new.id);
817
prepare stmt1 from "insert into t1 values (10)";
818
create procedure p1() insert into t1 values (10);
821
create trigger t1_bi after insert on t1 for each row insert into t3 values (new.id);
823
ERROR HY000: Table 't3' was not locked with LOCK TABLES
825
ERROR HY000: Table 't3' was not locked with LOCK TABLES
826
deallocate prepare stmt1;
828
drop table t1, t2, t3;
829
create table t1 (a int);
830
CREATE PROCEDURE `p1`()
832
insert into t1 values (1);
834
create trigger trg before insert on t1 for each row
836
declare done int default 0;
842
create trigger t1_bi before insert on test.t1 for each row set @a:=0;
843
ERROR 3D000: No database selected
844
create trigger test.t1_bi before insert on t1 for each row set @a:=0;
845
ERROR 3D000: No database selected
847
ERROR 3D000: No database selected
848
create table t1 (id int);
849
create trigger t1_bi before insert on t1 for each row set @a:=new.id;
850
create trigger t1_ai after insert on test.t1 for each row set @b:=new.id;
851
insert into t1 values (101);
855
select trigger_schema, trigger_name, event_object_schema,
856
event_object_table, action_statement from information_schema.triggers
857
where event_object_schema = 'test';
858
trigger_schema trigger_name event_object_schema event_object_table action_statement
859
test t1_bi test t1 set @a:=new.id
860
test t1_ai test t1 set @b:=new.id
861
rename table t1 to t2;
862
insert into t2 values (102);
866
select trigger_schema, trigger_name, event_object_schema,
867
event_object_table, action_statement from information_schema.triggers
868
where event_object_schema = 'test';
869
trigger_schema trigger_name event_object_schema event_object_table action_statement
870
test t1_bi test t2 set @a:=new.id
871
test t1_ai test t2 set @b:=new.id
872
alter table t2 rename to t3;
873
insert into t3 values (103);
877
select trigger_schema, trigger_name, event_object_schema,
878
event_object_table, action_statement from information_schema.triggers
879
where event_object_schema = 'test';
880
trigger_schema trigger_name event_object_schema event_object_table action_statement
881
test t1_bi test t3 set @a:=new.id
882
test t1_ai test t3 set @b:=new.id
883
alter table t3 rename to t4, add column val int default 0;
884
insert into t4 values (104, 1);
888
select trigger_schema, trigger_name, event_object_schema,
889
event_object_table, action_statement from information_schema.triggers
890
where event_object_schema = 'test';
891
trigger_schema trigger_name event_object_schema event_object_table action_statement
892
test t1_bi test t4 set @a:=new.id
893
test t1_ai test t4 set @b:=new.id
897
create database mysqltest;
899
create table t1 (id int);
900
create trigger t1_bi before insert on t1 for each row set @a:=new.id;
901
insert into t1 values (101);
905
select trigger_schema, trigger_name, event_object_schema,
906
event_object_table, action_statement from information_schema.triggers
907
where event_object_schema = 'test' or event_object_schema = 'mysqltest';
908
trigger_schema trigger_name event_object_schema event_object_table action_statement
909
mysqltest t1_bi mysqltest t1 set @a:=new.id
910
rename table t1 to test.t2;
911
ERROR HY000: Trigger in wrong schema
912
insert into t1 values (102);
916
select trigger_schema, trigger_name, event_object_schema,
917
event_object_table, action_statement from information_schema.triggers
918
where event_object_schema = 'test' or event_object_schema = 'mysqltest';
919
trigger_schema trigger_name event_object_schema event_object_table action_statement
920
mysqltest t1_bi mysqltest t1 set @a:=new.id
921
drop trigger test.t1_bi;
922
ERROR HY000: Trigger does not exist
923
alter table t1 rename to test.t1;
924
ERROR HY000: Trigger in wrong schema
925
insert into t1 values (103);
929
select trigger_schema, trigger_name, event_object_schema,
930
event_object_table, action_statement from information_schema.triggers
931
where event_object_schema = 'test' or event_object_schema = 'mysqltest';
932
trigger_schema trigger_name event_object_schema event_object_table action_statement
933
mysqltest t1_bi mysqltest t1 set @a:=new.id
934
drop trigger test.t1_bi;
935
ERROR HY000: Trigger does not exist
936
alter table t1 rename to test.t1, add column val int default 0;
937
ERROR HY000: Trigger in wrong schema
938
insert into t1 values (104);
942
select trigger_schema, trigger_name, event_object_schema,
943
event_object_table, action_statement from information_schema.triggers
944
where event_object_schema = 'test' or event_object_schema = 'mysqltest';
945
trigger_schema trigger_name event_object_schema event_object_table action_statement
946
mysqltest t1_bi mysqltest t1 set @a:=new.id
947
show create table t1;
949
t1 CREATE TABLE `t1` (
950
`id` int(11) DEFAULT NULL
951
) ENGINE=PBXT DEFAULT CHARSET=latin1
952
drop trigger test.t1_bi;
953
ERROR HY000: Trigger does not exist
956
drop database mysqltest;
958
create table t1 (id int);
959
create trigger t1_bi before insert on t1 for each row set @a:=new.id;
960
create trigger t1_ai after insert on t1 for each row set @b:=new.id;
961
insert into t1 values (101);
965
select trigger_schema, trigger_name, event_object_schema,
966
event_object_table, action_statement from information_schema.triggers
967
where event_object_schema = 'test';
968
trigger_schema trigger_name event_object_schema event_object_table action_statement
969
test t1_bi test t1 set @a:=new.id
970
test t1_ai test t1 set @b:=new.id
971
rename table t1 to t2;
972
ERROR HY000: Can't create/write to file './test/t1_ai.TRN~' (Errcode: 13)
973
insert into t1 values (102);
977
select trigger_schema, trigger_name, event_object_schema,
978
event_object_table, action_statement from information_schema.triggers
979
where event_object_schema = 'test';
980
trigger_schema trigger_name event_object_schema event_object_table action_statement
981
test t1_bi test t1 set @a:=new.id
982
test t1_ai test t1 set @b:=new.id
986
create table t1 (i int);
987
create trigger t1_bi before insert on t1 for each row return 0;
988
ERROR 42000: RETURN is only allowed in a FUNCTION
989
insert into t1 values (1);
991
create table t1 (a varchar(64), b int);
992
create table t2 like t1;
993
create trigger t1_ai after insert on t1 for each row
994
set @a:= (select max(a) from t1);
995
insert into t1 (a) values
996
("Twas"),("brillig"),("and"),("the"),("slithy"),("toves"),
997
("Did"),("gyre"),("and"),("gimble"),("in"),("the"),("wabe");
998
create trigger t2_ai after insert on t2 for each row
999
set @a:= (select max(a) from t2);
1000
insert into t2 select * from t1;
1001
load data infile '../std_data_ln/words.dat' into table t1 (a);
1004
create function f1() returns int return (select max(b) from t1);
1005
insert into t1 values
1006
("All",f1()),("mimsy",f1()),("were",f1()),("the",f1()),("borogoves",f1()),
1007
("And",f1()),("the",f1()),("mome", f1()),("raths",f1()),("outgrabe",f1());
1008
create function f2() returns int return (select max(b) from t2);
1009
insert into t2 select a, f2() from t1;
1010
load data infile '../std_data_ln/words.dat' into table t1 (a) set b:= f1();
1014
create table t1(i int not null, j int not null, n numeric(15,2), primary key(i,j));
1015
create table t2(i int not null, n numeric(15,2), primary key(i));
1016
create trigger t1_ai after insert on t1 for each row
1018
declare sn numeric(15,2);
1019
select sum(n) into sn from t1 where i=new.i;
1020
replace into t2 values(new.i, sn);
1022
insert into t1 values
1023
(1,1,10.00),(1,2,10.00),(1,3,10.00),(1,4,10.00),(1,5,10.00),
1024
(1,6,10.00),(1,7,10.00),(1,8,10.00),(1,9,10.00),(1,10,10.00),
1025
(1,11,10.00),(1,12,10.00),(1,13,10.00),(1,14,10.00),(1,15,10.00);
1047
DROP TABLE IF EXISTS t1;
1052
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
1053
SET NEW.trigger_conn_id = CONNECTION_ID();
1054
INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1);
1055
INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1);
1056
SELECT * FROM t1 WHERE conn_id != trigger_conn_id;
1057
conn_id trigger_conn_id
1060
DROP TABLE IF EXISTS t1;
1061
CREATE TABLE t1 (i1 INT);
1062
SET @save_sql_mode=@@sql_mode;
1064
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
1066
SET SQL_MODE='traditional';
1067
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
1070
INSERT INTO t1 VALUES (@x);
1075
UPDATE t1 SET i1 = @x;
1076
ERROR 22012: Division by 0
1082
INSERT INTO t1 VALUES (@x);
1087
UPDATE t1 SET i1 = @x;
1088
ERROR 22012: Division by 0
1092
SET @@sql_mode=@save_sql_mode;
1096
DROP TABLE IF EXISTS t1;
1097
DROP PROCEDURE IF EXISTS p1;
1098
DROP PROCEDURE IF EXISTS p2;
1099
CREATE TABLE t1 (i1 INT);
1100
INSERT INTO t1 VALUES (3);
1101
CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET i1 = 5;
1102
CREATE PROCEDURE p2(INOUT i1 INT) DETERMINISTIC NO SQL SET i1 = i1 * 7;
1103
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
1108
UPDATE t1 SET i1 = 11 WHERE i1 = 3;
1112
INSERT INTO t1 VALUES (13);
1113
CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 17;
1114
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
1116
UPDATE t1 SET i1 = 19 WHERE i1 = 13;
1117
ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
1120
INSERT INTO t1 VALUES (23);
1121
CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 29;
1122
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
1124
UPDATE t1 SET i1 = 31 WHERE i1 = 23;
1125
ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
1128
INSERT INTO t1 VALUES (37);
1129
CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 41;
1130
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
1132
UPDATE t1 SET i1 = 43 WHERE i1 = 37;
1133
ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
1136
INSERT INTO t1 VALUES (47);
1137
CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 49;
1138
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
1140
UPDATE t1 SET i1 = 51 WHERE i1 = 47;
1141
ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
1152
create trigger wont_work after update on mysql.user for each row
1156
ERROR HY000: Triggers can not be created on system tables
1158
create trigger wont_work after update on event for each row
1162
ERROR HY000: Triggers can not be created on system tables
1164
DROP TABLE IF EXISTS t1;
1165
DROP TABLE IF EXISTS t2;
1166
CREATE TABLE t1(c INT);
1167
CREATE TABLE t2(c INT);
1168
CREATE DEFINER=1234567890abcdefGHIKL@localhost
1169
TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1;
1170
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
1171
CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY
1172
TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW SET @a = 2;
1173
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
added
removed
pbxt/mysql-test-update/mysql-test/r/trigger.result
