trigger.result 22.8 KB
Newer Older
1
drop table if exists t1, t2, t3;
2
drop view if exists v1;
3
drop database if exists mysqltest;
4
drop function if exists f1;
5
drop procedure if exists p1;
6 7 8 9 10 11 12 13 14 15
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
16
drop trigger trg;
17 18 19 20 21
create trigger trg before insert on t1 for each row set @a:=new.i;
insert into t1 values (123);
select @a;
@a
123
22
drop trigger trg;
23 24 25 26 27 28 29 30 31 32 33 34 35 36
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
37
drop trigger trg|
38 39 40 41 42 43 44 45 46
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
47
drop trigger trg;
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
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
69
drop trigger trg|
70 71 72 73 74 75 76 77 78 79
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
80
drop trigger trg;
81 82 83 84 85 86 87 88 89 90
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
91
drop trigger trg;
92 93 94 95 96 97 98 99 100
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
101
drop trigger trg;
102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140
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
141 142 143
drop trigger trg1;
drop trigger trg2;
drop trigger trg3;
144
drop table t1;
unknown's avatar
unknown committed
145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186
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);
select @log;
@log
(BEFORE_INSERT: new=(id=1, data=2))
set @log:= "";
replace t1 values (1, 3), (2, 2);
select @log;
@log
(BEFORE_INSERT: new=(id=1, data=3))(BEFORE_UPDATE: old=(id=1, data=1) new=(id=1, data=3))(AFTER_UPDATE: old=(id=1, data=1) new=(id=1, data=3))(BEFORE_INSERT: new=(id=2, data=2))(AFTER_INSERT: new=(id=2, data=2))
alter table t1 add ts timestamp default now();
set @log:= "";
replace t1 (id, data) values (1, 4);
select @log;
@log
(BEFORE_INSERT: new=(id=1, data=4))(BEFORE_DELETE: old=(id=1, data=3))(AFTER_DELETE: old=(id=1, data=3))(AFTER_INSERT: new=(id=1, data=4))
set @log:= "";
insert into t1 (id, data) values (1, 5), (3, 3) on duplicate key update data= data + 2;
select @log;
@log
(BEFORE_INSERT: new=(id=1, data=5))(BEFORE_UPDATE: old=(id=1, data=4) new=(id=1, data=6))(AFTER_UPDATE: old=(id=1, data=4) new=(id=1, data=6))(BEFORE_INSERT: new=(id=3, data=3))(AFTER_INSERT: new=(id=3, data=3))
drop table t1;
187 188 189 190 191 192 193 194 195 196 197 198 199 200
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'
201
drop trigger t1_ai;
202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273
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
4	four	1
select * from t2;
event
INSERT INTO t1 id=1 data='one'
INSERT INTO t1 id=2 data='two'
INSERT INTO t1 id=4 data='four' fk=1
INSERT INTO t1 FAILED id=5 data='five' fk=2
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;
274
create table t1 (i int);
275
create table t3 (i int);
276 277 278 279 280 281 282 283 284 285
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
286 287 288 289
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'
290 291 292 293 294 295 296
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 trg2 before insert on t1 for each row set @a:=1;
ERROR HY000: Trigger already exists
297 298 299 300 301 302
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;
303 304 305
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;
306
ERROR HY000: 'test.v1' is not BASE TABLE
307 308
drop view v1;
drop table t1;
309
drop table t3;
310 311 312 313
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;
314 315 316
create table t1 (x1col char);
create trigger tx1 before insert on t1 for each row set new.x1col = 'x';
insert into t1 values ('y');
317
drop trigger tx1;
318
drop table t1;
319 320 321 322 323 324 325 326 327
create table t1 (i int) engine=myisam;
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
328 329
drop trigger trg1;
drop trigger trg2;
330
drop table t1;
331 332 333 334 335 336 337 338 339 340 341 342 343 344 345
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;
346 347 348 349 350 351 352 353 354
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 HY000: Trigger in wrong schema
drop database mysqltest;
use test;
unknown's avatar
unknown committed
355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406
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:="";
407
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@a, i);
unknown's avatar
unknown committed
408 409 410 411 412
select *, @b from t1;
i	j	k	@b
10	NULL	10	Fired
15	NULL	15	Fired
set @b:="";
413
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, j);
unknown's avatar
unknown committed
414 415 416 417 418 419 420 421
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;
422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449
create table t1 (i int, at int, k int, key(k)) engine=myisam;
create table t2 (i int);
insert into t1 values (1, 1, 1);
insert into t2 values (1), (2), (3);
create trigger ai after insert on t1 for each row set @a:= new.at;
create trigger au after update on t1 for each row set @a:= new.at;
create trigger ad after delete on t1 for each row set @a:= old.at;
alter table t1 drop column at;
select * from t1;
i	k
1	1
insert into t1 values (2, 1);
ERROR 42S22: Unknown column 'at' in 'NEW'
select * from t1;
i	k
1	1
2	1
update t1 set k = 2 where i = 2;
ERROR 42S22: Unknown column 'at' in 'NEW'
select * from t1;
i	k
1	1
2	2
delete from t1 where i = 2;
ERROR 42S22: Unknown column 'at' in 'OLD'
select * from t1;
i	k
1	1
450
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k);
451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530
ERROR 42S22: Unknown column 'at' in 'NEW'
select * from t1;
i	k
1	1
1	2
insert into t1 select 3, 3;
ERROR 42S22: Unknown column 'at' in 'NEW'
select * from t1;
i	k
1	1
1	2
3	3
update t1, t2 set k = k + 10 where t1.i = t2.i;
ERROR 42S22: Unknown column 'at' in 'NEW'
select * from t1;
i	k
1	11
1	2
3	3
update t1, t2 set k = k + 10 where t1.i = t2.i and k < 3;
ERROR 42S22: Unknown column 'at' in 'NEW'
select * from t1;
i	k
1	11
1	12
3	3
delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;
ERROR 42S22: Unknown column 'at' in 'OLD'
select * from t1;
i	k
1	12
3	3
delete t2, t1 from t2 straight_join t1 where t1.i = t2.i;
ERROR 42S22: Unknown column 'at' in 'OLD'
select * from t1;
i	k
3	3
alter table t1 add primary key (i);
insert into t1 values (3, 4) on duplicate key update k= k + 10;
ERROR 42S22: Unknown column 'at' in 'NEW'
select * from t1;
i	k
3	13
replace into t1 values (3, 3);
ERROR 42S22: Unknown column 'at' in 'NEW'
select * from t1;
i	k
3	3
alter table t1 add ts timestamp default now();
replace into t1 (i, k) values (3, 13);
ERROR 42S22: Unknown column 'at' in 'OLD'
select * from t1;
i	k	ts
drop table t1, t2;
create table t1 (i int, bt int, k int, key(k)) engine=myisam;
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
531
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k);
532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567
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);
568
drop trigger bi;
569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588
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 'NEW'
select * from t1;
i	k
1	1
2	2
alter table t1 add ts timestamp default now();
replace into t1 (i, k) values (2, 11);
ERROR 42S22: Unknown column 'bt' in 'OLD'
select * from t1;
i	k	ts
1	1	0000-00-00 00:00:00
2	2	0000-00-00 00:00:00
drop table t1, t2;
589 590 591 592 593 594 595 596
drop function if exists bug5893;
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
597
drop trigger t1_bu;
598
drop table t1;
599 600 601
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;
unknown's avatar
unknown committed
602
set sql_mode="";
603 604 605 606 607 608 609 610 611 612 613
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;
614 615
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer
t1_bi	INSERT	t1	set new."t1 column" = 5	BEFORE	#	REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI	root@localhost
616
t1_af	INSERT	t1	set @a=10	AFTER	#		root@localhost
617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635
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=MyISAM DEFAULT CHARSET=latin1
show triggers;
636
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer
637
t1_bi	INSERT	t1	set new.a = '2004-01-00'	BEFORE	#		root@localhost
638
drop table t1;
639 640 641 642 643 644 645 646 647 648 649 650 651 652 653
create table t1 (id int);
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 procedure p1() flush tables;
create trigger t1_ai after insert on t1 for each row call p1();
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;
drop table t1;
654 655 656 657 658 659 660 661 662 663 664 665 666
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;
667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691
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
drop procedure if exists bug11587;
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();
set new.c2= '2004-04-02';
end|
insert into t1 (c1) values (4),(5),(6);
692
ERROR 0A000: Not allowed to return a result set from a trigger
693 694 695 696 697 698 699
select * from t1;
c1	c2
1	NULL
2	NULL
3	NULL
drop procedure bug11587;
drop table t1;
700 701 702 703 704 705
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);
706 707
set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth;
set @@max_sp_recursion_depth=100;
708 709
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.
710
set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS;
711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743
select * from t1;
f1
1
select * from t2;
f2
2
drop trigger t1_ai;
drop trigger t2_ai;
create trigger t1_bu before update on t1
for each row insert into t1 values (2);
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;
744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768
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;
ERROR HY000: Table 't3' was not locked with LOCK TABLES
call p1();
ERROR HY000: Table 't3' was not locked with LOCK TABLES
deallocate prepare stmt1;
drop procedure p1;
drop table t1, t2, t3;
769
create table t1 (a int);
unknown's avatar
unknown committed
770
CREATE PROCEDURE `p1`()
771 772 773 774 775 776 777 778
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//
unknown's avatar
unknown committed
779 780
CALL p1();
drop procedure p1;
781
drop table t1;
782 783 784 785 786 787
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 3D000: No database selected
drop trigger t1_bi;
ERROR 3D000: No database selected
788 789 790 791 792
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;