trigger.test 18.9 KB
Newer Older
1 2 3 4 5
#
# Basic triggers test
#

--disable_warnings
6
drop table if exists t1, t2, t3;
7
drop view if exists v1;
8
drop database if exists mysqltest;
9
drop function if exists f1;
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
--enable_warnings

create table t1 (i int);

# let us test some very simple trigger
create trigger trg before insert on t1 for each row set @a:=1;
set @a:=0;
select @a;
insert into t1 values (1);
select @a;
drop trigger t1.trg;

# let us test simple trigger reading some values 
create trigger trg before insert on t1 for each row set @a:=new.i;
insert into t1 values (123);
select @a;
drop trigger t1.trg;

drop table t1;

# Let us test before insert trigger
# Such triggers can be used for setting complex default values
create table t1 (i int not null, j int);
delimiter |;
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|
drop trigger t1.trg|
drop table t1|
delimiter ;|

# After insert trigger
# Useful for aggregating data
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;
drop trigger t1.trg;
drop table t1;

monty@mysql.com's avatar
monty@mysql.com committed
58
# PS doesn't work with multi-row statements
59
--disable_ps_protocol
60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
# Before update trigger
# (In future we will achieve this via proper error handling in triggers)
create table t1 (aid int not null primary key, balance int not null default 0);
insert into t1 values (1, 1000), (2,3000);
delimiter |;
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|
drop trigger t1.trg|
drop table t1|
delimiter ;|
81
--enable_ps_protocol
82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 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 141 142 143 144 145 146 147 148 149 150 151 152 153

# After update trigger
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;
drop trigger t1.trg;
drop table t1;

# Before delete trigger
# This can be used for aggregation too :)
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;
drop trigger t1.trg;
drop table t1;

# After delete trigger. 
# Just run out of imagination.
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;
drop trigger t1.trg;
drop table t1;

# Several triggers on one table
create table t1 (i int, j int);

delimiter |;
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|
delimiter ;|
set @fired:="";
insert into t1 values (1,2),(2,3),(3,14);
select @fired;
select * from t1;
update t1 set j= 20;
select @fired;
select * from t1;

drop trigger t1.trg1;
drop trigger t1.trg2;
drop trigger t1.trg3;
drop table t1;


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 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202
# Let us test how triggers work for special forms of INSERT such as
# REPLACE and INSERT ... ON DUPLICATE KEY UPDATE
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,"))");
# Simple INSERT - both triggers should be called
set @log:= "";
insert into t1 values (1, 1);
select @log;
# INSERT IGNORE for already existing key - only before trigger should fire
set @log:= "";
insert ignore t1 values (1, 2);
select @log;
# REPLACE: before insert trigger should be called for both records,
#          but then for first one update will be executed (and both update
#          triggers should fire). For second after insert trigger will be
#          called as for usual insert
set @log:= "";
replace t1 values (1, 3), (2, 2);
select @log;
# Now let us change table in such way that REPLACE on won't be executed
# using update.
alter table t1 add ts timestamp default now();
set @log:= "";
# This REPLACE should be executed via DELETE and INSERT so proper 
# triggers should be invoked.
replace t1 (id, data) values (1, 4);
select @log;
# Finally let us test INSERT ... ON DUPLICATE KEY UPDATE ...
set @log:= "";
insert into t1 (id, data) values (1, 5), (3, 3) on duplicate key update data= data + 2; 
select @log;

# This also drops associated triggers
drop table t1;


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 274 275 276 277 278 279 280
#
# Let us test triggers which access other tables.
#
# Trivial trigger which inserts data into another table
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;
select * from t2;
drop trigger t1.t1_ai;
# Trigger which uses couple of tables (and partially emulates FK constraint)
delimiter |;
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|
delimiter ;|
insert into t3 values (1);
--error 1048
insert into t1 values (4, "four", 1), (5, "five", 2);
select * from t1;
select * from t2;
drop table t1, t2, t3;
# Trigger which invokes function
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);
delimiter |;
create trigger t1_bi before insert on t1 for each row
begin
  if new.id > f1() then
    set new.id:= f1();
  end if;
end|
delimiter ;|
insert into t1 values (1, "first");
insert into t1 values (f1(), "max");
select * from t1;
drop table t1, t2;
drop function f1;
# Trigger which forces invocation of another trigger
# (emulation of FK on delete cascade policy)
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;
drop table t1, t2, t3;
# Trigger which assigns value selected from table to field of row
# being inserted/updated.
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;
drop table t1, t2;

281 282 283 284 285
#
# Test of wrong column specifiers in triggers
#
create table t1 (i int);

286
--error 1363
287
create trigger trg before insert on t1 for each row set @a:= old.i;
288
--error 1363
289
create trigger trg before delete on t1 for each row set @a:= new.i;
290
--error 1362
291 292
create trigger trg before update on t1 for each row set old.i:=1;
--error 1363
293
create trigger trg before delete on t1 for each row set new.i:=1;
294
--error 1362
295
create trigger trg after update on t1 for each row set new.i:=1;
296 297 298 299
--error 1054
create trigger trg before update on t1 for each row set new.j:=1;
--error 1054
create trigger trg before update on t1 for each row set @a:=old.j;
300 301 302 303 304 305 306 307 308 309


#
# Let us test various trigger creation errors
#
#
--error 1146
create trigger trg before insert on t2 for each row set @a:=1;

create trigger trg before insert on t1 for each row set @a:=1;
310
--error 1359
311
create trigger trg after insert on t1 for each row set @a:=1;
312
--error 1359
313 314 315
create trigger trg2 before insert on t1 for each row set @a:=1;
drop trigger t1.trg;

316
--error 1360
317 318 319
drop trigger t1.trg;

create view v1 as select * from t1;
320
--error 1361
321 322 323 324 325 326
create trigger trg before insert on v1 for each row set @a:=1;
drop view v1;

drop table t1;

create temporary table t1 (i int);
327
--error 1361
328 329
create trigger trg before insert on t1 for each row set @a:=1;
drop table t1;
330 331 332 333 334 335 336 337 338 339 340 341 342 343



#
# Tests for various trigger-related bugs
#

# Test for bug #5887 "Triggers with string literals cause errors".
# New .FRM parser was not handling escaped strings properly.
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 t1.tx1;
drop table t1;
344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360

#
# Test for bug #5890 "Triggers fail for DELETE without WHERE".
# If we are going to delete all rows in table but DELETE triggers exist
# we should perform row-by-row deletion instead of using optimized
# delete_all_rows() method.
#
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;
drop trigger t1.trg1;
drop trigger t1.trg2;
drop table t1;
361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379

# Test for bug #5859 "DROP TABLE does not drop triggers". Trigger should not
# magically reappear when we recreate dropped table.
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;
drop table t1;

# Test for bug #6559 "DROP DATABASE forgets to drop triggers". 
create database mysqltest;
use mysqltest;
create table t1 (i int);
create trigger trg1 before insert on t1 for each row set @a:= 1;
# This should succeed
drop database mysqltest;
use test;
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 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 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

# Test for bug #5860 "Multi-table UPDATE does not activate update triggers"
# We will also test how delete triggers wor for multi-table DELETE.
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:= "";
# Check that trigger works in case of update on the fly
update t1, t2 set j = j + 10 where t1.i = t2.i;
select @a, @b;
insert into t1 values (2, 13, 2);
insert into t2 values (2);
set @a:= 0, @b:= "";
# And now let us check that triggers work in case of multi-update which
# is done through temporary tables...
update t1, t2 set j = j + 15 where t1.i = t2.i and t1.k >= 2;
select @a, @b;
# Let us test delete triggers for multi-delete now.
# We create triggers for both tables because we want test how they
# work in both on-the-fly and via-temp-tables cases.
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;
# This also will drop triggers
drop table t1, t2;

# Test for bug #6812 "Triggers are not activated for INSERT ... SELECT".
# (We also check the fact that trigger modifies some field does not affect
#  value of next record inserted).
delimiter |;
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|
select @a|
# This also will drop triggers
drop table t1, t2|
delimiter ;|

# Test for bug #8755 "Trigger is not activated by LOAD DATA"
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:="";
# Test triggers with file with separators
load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, i);
select *, @b from t1;
set @b:="";
# Test triggers with fixed size row file
load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, j);
select *, @b from t1;
# This also will drop triggers
drop table t1;
449 450 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 531 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 568 569 570 571 572 573 574 575

# Test for bug #5894 "Triggers with altered tables cause corrupt databases"
# Also tests basic error handling for various kinds of triggers.
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);
# We need at least 3 elements in t2 to test multi-update properly
insert into t2 values (1), (2), (3);
# Create and then break "after" triggers
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;
# We still should be able select data from tables.
select * from t1;
# The following statements changing t1 should fail, but still cause
# their main effect. This is because operation on the table row is
# executed before "after" trigger and its effect cannot be rolled back
# when whole statement fails, because t1 is MyISAM table.
--error 1054
insert into t1 values (2, 1);
select * from t1;
--error 1054
update t1 set k = 2 where i = 2;
select * from t1;
--error 1054
delete from t1 where i = 2;
select * from t1;
# Should fail and insert only 1 row
--error 1054
load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k);
select * from t1;
--error 1054
insert into t1 select 3, 3;
select * from t1;
# Multi-update working on the fly, again it will update only
# one row even if more matches
--error 1054
update t1, t2 set k = k + 10 where t1.i = t2.i;
select * from t1;
# The same for multi-update via temp table
--error 1054
update t1, t2 set k = k + 10 where t1.i = t2.i and k < 3;
select * from t1;
# Multi-delete on the fly
--error 1054
delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;
select * from t1;
# And via temporary storage
--error 1054
delete t2, t1 from t2 straight_join t1 where t1.i = t2.i;
select * from t1;
# Prepare table for testing of REPLACE and INSERT ... ON DUPLICATE KEY UPDATE
alter table t1 add primary key (i);
--error 1054
insert into t1 values (3, 4) on duplicate key update k= k + 10;
select * from t1;
--error 1054
replace into t1 values (3, 3);
select * from t1;
# Change table in such way that REPLACE will delete row
alter table t1 add ts timestamp default now();
--error 1054
replace into t1 (i, k) values (3, 13);
select * from t1;
# Also drops all triggers
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 and then break "before" triggers
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;
# The following statements changing t1 should fail and should not
# cause any effect on table, since "before" trigger is executed 
# before operation on the table row.
--error 1054
insert into t1 values (3, 3);
select * from t1;
--error 1054
update t1 set i = 2;
select * from t1;
--error 1054
delete from t1;
select * from t1;
--error 1054
load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k);
select * from t1;
--error 1054
insert into t1 select 3, 3;
select * from t1;
# Both types of multi-update (on the fly and via temp table)
--error 1054
update t1, t2 set k = k + 10 where t1.i = t2.i;
select * from t1;
--error 1054
update t1, t2 set k = k + 10 where t1.i = t2.i and k < 2;
select * from t1;
# Both types of multi-delete
--error 1054
delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;
select * from t1;
--error 1054
delete t2, t1 from t2 straight_join t1 where t1.i = t2.i;
select * from t1;
# Let us test REPLACE/INSERT ... ON DUPLICATE KEY UPDATE.
# To test properly code-paths different from those that are used
# in ordinary INSERT we need to drop "before insert" trigger.
alter table t1 add primary key (i);
drop trigger t1.bi;
--error 1054
insert into t1 values (2, 4) on duplicate key update k= k + 10;
select * from t1;
--error 1054
replace into t1 values (2, 4);
select * from t1;
# Change table in such way that REPLACE will delete row
alter table t1 add ts timestamp default now();
--error 1054
replace into t1 (i, k) values (2, 11);
select * from t1;
# Also drops all triggers
drop table t1, t2;
576 577 578

# Test for bug #5893 "Triggers with dropped functions cause crashes"
# Appropriate error should be reported instead of crash.
579 580 581 582 583 584 585 586 587 588 589
--disable_warnings
drop function if exists bug5893;
--enable_warnings
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; 
--error 1305
update t1 set col2 = 4;
drop table t1;