trigger.test

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· TEST 代码 · 共 2,221 行 · 第 1/5 页

# 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_ln/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_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, j);
select *, @b from t1;
# This also will drop triggers
drop table t1;

# 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 ER_BAD_FIELD_ERROR
insert into t1 values (2, 1);
select * from t1;
--error ER_BAD_FIELD_ERROR
update t1 set k = 2 where i = 2;
select * from t1;
--error ER_BAD_FIELD_ERROR
delete from t1 where i = 2;
select * from t1;
# Should fail and insert only 1 row
--error ER_BAD_FIELD_ERROR
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k);
select * from t1;
--error ER_BAD_FIELD_ERROR
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 ER_BAD_FIELD_ERROR
update t1, t2 set k = k + 10 where t1.i = t2.i;
select * from t1;
# The same for multi-update via temp table
--error ER_BAD_FIELD_ERROR
update t1, t2 set k = k + 10 where t1.i = t2.i and k < 3;
select * from t1;
# Multi-delete on the fly
--error ER_BAD_FIELD_ERROR
delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;
select * from t1;
# And via temporary storage
--error ER_BAD_FIELD_ERROR
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 ER_BAD_FIELD_ERROR
insert into t1 values (3, 4) on duplicate key update k= k + 10;
select * from t1;
# The following statement will delete old row and won't
# insert new one since after delete trigger will fail.
--error ER_BAD_FIELD_ERROR
replace into t1 values (3, 3);
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 ER_BAD_FIELD_ERROR
insert into t1 values (3, 3);
select * from t1;
--error ER_BAD_FIELD_ERROR
update t1 set i = 2;
select * from t1;
--error ER_BAD_FIELD_ERROR
delete from t1;
select * from t1;
--error ER_BAD_FIELD_ERROR
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k);
select * from t1;
--error ER_BAD_FIELD_ERROR
insert into t1 select 3, 3;
select * from t1;
# Both types of multi-update (on the fly and via temp table)
--error ER_BAD_FIELD_ERROR
update t1, t2 set k = k + 10 where t1.i = t2.i;
select * from t1;
--error ER_BAD_FIELD_ERROR
update t1, t2 set k = k + 10 where t1.i = t2.i and k < 2;
select * from t1;
# Both types of multi-delete
--error ER_BAD_FIELD_ERROR
delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;
select * from t1;
--error ER_BAD_FIELD_ERROR
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 bi;
--error ER_BAD_FIELD_ERROR
insert into t1 values (2, 4) on duplicate key update k= k + 10;
select * from t1;
--error ER_BAD_FIELD_ERROR
replace into t1 values (2, 4);
select * from t1;
# Also drops all triggers
drop table t1, t2;

# Test for bug #5893 "Triggers with dropped functions cause crashes"
# Appropriate error should be reported instead of crash.
# Also test for bug #11889 "Server crashes when dropping trigger
# using stored routine".
--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 ER_SP_DOES_NOT_EXIST
update t1 set col2 = 4;
# This should not crash server too.
drop trigger t1_bu;
drop table t1;

#
# storing and restoring parsing modes for triggers (BUG#5891)
#
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;
set sql_mode="";
insert into t1 values (0);
# create trigger with different sql_mode
create trigger t1_af after insert on t1 for each row set @a=10;
insert into t1 values (0);
select * from t1;
select @a;
--replace_column 6 #
show triggers;
drop table t1;
# check that rigger preserve sql_mode during execution
set sql_mode="traditional";
create table t1 (a date);
-- error 1292
insert into t1 values ('2004-01-00');
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;
set sql_mode=default;
show create table t1;
--replace_column 6 #
show triggers;
drop table t1;

# Test for bug #12280 "Triggers: crash if flush tables"
# FLUSH TABLES and FLUSH PRIVILEGES should be disallowed inside
# of functions and triggers.
create table t1 (id int);
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create trigger t1_ai after insert on t1 for each row reset query cache;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create trigger t1_ai after insert on t1 for each row reset master;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create trigger t1_ai after insert on t1 for each row reset slave;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create trigger t1_ai after insert on t1 for each row flush hosts;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create trigger t1_ai after insert on t1 for each row flush tables with read lock;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create trigger t1_ai after insert on t1 for each row flush logs;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create trigger t1_ai after insert on t1 for each row flush status;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create trigger t1_ai after insert on t1 for each row flush slave;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create trigger t1_ai after insert on t1 for each row flush master;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create trigger t1_ai after insert on t1 for each row flush des_key_file;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create trigger t1_ai after insert on t1 for each row flush user_resources;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create trigger t1_ai after insert on t1 for each row flush tables;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create trigger t1_ai after insert on t1 for each row flush privileges;
--disable_warnings
drop procedure if exists p1;
--enable_warnings

create trigger t1_ai after insert on t1 for each row call p1();
create procedure p1() flush tables;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
create procedure p1() reset query cache;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
create procedure p1() reset master;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
create procedure p1() reset slave;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
create procedure p1() flush hosts;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
create procedure p1() flush privileges;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
create procedure p1() flush tables with read lock;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
create procedure p1() flush tables;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
create procedure p1() flush logs;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
create procedure p1() flush status;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
create procedure p1() flush slave;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
create procedure p1() flush master;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
create procedure p1() flush des_key_file;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
create procedure p1() flush user_resources;
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 values (0);

drop procedure p1;
drop table t1;

# Test for bug #11973 "SELECT .. INTO var_name; in trigger cause
#                      crash on update"

create table t1 (id int, data int, username varchar(16));
insert into t1 (id, data) values (1, 0);
delimiter |;
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|
delimiter ;|
update t1 set data = 1;

connection addconroot1;
update t1 set data = 2;

connection default;
drop table t1;

#
# #11587 Trigger causes lost connection error
#

create table t1 (c1 int, c2 datetime);
delimiter |;
--error ER_SP_NO_RETSET
create trigger tr1 before insert on t1 for each row 
begin 
  set new.c2= '2004-04-01';
  select 'hello';
end|
delimiter ;|

insert into t1 (c1) values (1),(2),(3);
select * from t1;

--disable_warnings
drop procedure if exists bug11587;
--enable_warnings

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

--error ER_SP_NO_RETSET
insert into t1 (c1) values (4),(5),(6);
select * from t1;

drop procedure bug11587;
drop table t1;

# Test for bug #11896 "Partial locking in case of recursive trigger
# definitions". Recursion in triggers should not be allowed.
# We also should not allow to change tables which are used in
# statements invoking this trigger.
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);
# Allow SP resursion to be show that it has not influence here
set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth;
set @@max_sp_recursion_depth=100;
--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
insert into t1 values (1);
set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS;
select * from t1;
select * from t2;
drop trigger t1_ai;
drop trigger t2_ai;
create trigger t1_bu before update on t1
  for each row insert into t1 values (2);
--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
update t1 set f1= 10;
select * from t1;
drop trigger t1_bu;
create trigger t1_bu before update on t1
  for each row delete from t1 where f1=new.f1;
--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
update t1 set f1= 10;
select * from t1;
drop trigger t1_bu;
# This should work tough
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;
drop trigger t1_bi;
drop tables t1, t2;

# Tests for bug #12704 "Server crashes during trigger execution".
# If we run DML statements and CREATE TRIGGER statements concurrently
# it may happen that trigger will be created while DML statement is
# waiting for table lock. In this case we have to reopen tables and
# recalculate prelocking set.
# Unfortunately these tests rely on the order in which tables are locked
# by statement so they are non determenistic and are disabled.
--disable_parsing
create table t1 (id int);
create table t2 (id int);
create table t3 (id int);
create function f1() returns int return (select max(id)+2 from t2);
create view v1 as select f1() as f;

# Let us check that we notice trigger at all
connection addconroot1;
lock tables t2 write;
connection default;
send insert into t1 values ((select max(id) from t2)), (2);
--sleep 1
connection addconroot2;
create trigger t1_trg before insert on t1 for each row set NEW.id:= 1;
connection addconroot1;
unlock tables;
connection default;
reap;