trigger.test

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

#
# Basic triggers test
#

--disable_warnings
drop table if exists t1, t2, t3, t4;
drop view if exists v1;
drop database if exists mysqltest;
drop function if exists f1;
drop function if exists f2;
drop procedure if exists p1;
--enable_warnings

# Create additional connections used through test
connect (addconroot1, localhost, root,,);
connect (addconroot2, localhost, root,,);
# Connection without current database set
connect (addconwithoutdb, localhost, root,,*NO-ONE*);
connection default;

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 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 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 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 trg;
drop table t1;

# PS doesn't work with multi-row statements
--disable_ps_protocol
# 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 trg|
drop table t1|
delimiter ;|
--enable_ps_protocol

# 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 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 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 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 trg1;
drop trigger trg2;
drop trigger trg3;
drop table t1;


# 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;


#
# 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_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;

#
# Test of wrong column specifiers in triggers
#
create table t1 (i int);
create table t3 (i int);

--error 1363
create trigger trg before insert on t1 for each row set @a:= old.i;
--error 1363
create trigger trg before delete on t1 for each row set @a:= new.i;
--error 1362
create trigger trg before update on t1 for each row set old.i:=1;
--error 1363
create trigger trg before delete on t1 for each row set new.i:=1;
--error 1362
create trigger trg after update on t1 for each row set new.i:=1;
--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;


#
# Let us test various trigger creation errors
# Also quickly test table namespace (bug#5892/6182)
#
--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;
--error 1359
create trigger trg after insert on t1 for each row set @a:=1;
--error 1359
create trigger trg2 before insert on t1 for each row set @a:=1;
--error 1359
create trigger trg before insert on t3 for each row set @a:=1;
create trigger trg2 before insert on t3 for each row set @a:=1;
drop trigger trg2;
drop trigger trg;

--error 1360
drop trigger trg;

create view v1 as select * from t1;
--error 1347
create trigger trg before insert on v1 for each row set @a:=1;
drop view v1;

drop table t1;
drop table t3;

create temporary table t1 (i int);
--error 1361
create trigger trg before insert on t1 for each row set @a:=1;
drop table t1;



#
# 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 tx1;
drop table t1;

#
# 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 trg1;