# # 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; # INSERT ... ON DUPLICATE KEY UPDATE ... set @log:= ""; insert into t1 (id, data) values (1, 3), (2, 2) on duplicate key update data= data + 1; select @log; # REPLACE (also test for bug#13479 "REPLACE activates UPDATE trigger, # not the DELETE and INSERT triggers") # We define REPLACE as INSERT which DELETEs old rows which conflict with # row being inserted. So for the first record in statement below we will # call before insert trigger, then delete will be executed (and both delete # triggers should fire). Finally after insert trigger will be called. # For the second record we will just call both on insert triggers. set @log:= ""; replace t1 values (1, 4), (3, 3); select @log; # Now we will drop ON DELETE triggers to test REPLACE which is internally # executed via update drop trigger t1_bd; drop trigger t1_ad; set @log:= ""; replace t1 values (1, 5); 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 ER_BAD_NULL_ERROR 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 ER_TRG_NO_SUCH_ROW_IN_TRG create trigger trg before insert on t1 for each row set @a:= old.i; --error ER_TRG_NO_SUCH_ROW_IN_TRG create trigger trg before delete on t1 for each row set @a:= new.i; --error ER_TRG_CANT_CHANGE_ROW create trigger trg before update on t1 for each row set old.i:=1; --error ER_TRG_NO_SUCH_ROW_IN_TRG create trigger trg before delete on t1 for each row set new.i:=1; --error ER_TRG_CANT_CHANGE_ROW create trigger trg after update on t1 for each row set new.i:=1; --error ER_BAD_FIELD_ERROR create trigger trg before update on t1 for each row set new.j:=1; --error ER_BAD_FIELD_ERROR 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 ER_NO_SUCH_TABLE 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 ER_TRG_ALREADY_EXISTS create trigger trg after insert on t1 for each row set @a:=1; --error ER_NOT_SUPPORTED_YET create trigger trg2 before insert on t1 for each row set @a:=1; --error ER_TRG_ALREADY_EXISTS 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 ER_TRG_DOES_NOT_EXIST drop trigger trg; create view v1 as select * from t1; --error ER_WRONG_OBJECT 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 ER_TRG_ON_VIEW_OR_TEMP_TABLE 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; drop trigger trg2; drop table t1; # 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; # Test for bug #8791 # "Triggers: Allowed to create triggers on a subject table in a different DB". create database mysqltest; create table mysqltest.t1 (i int); --error ER_TRG_IN_WRONG_SCHEMA create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1; use mysqltest; --error ER_TRG_IN_WRONG_SCHEMA create trigger test.trg1 before insert on t1 for each row set @a:= 1; drop database mysqltest; use test; # 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_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; select * from t1; # Check that we properly calculate new prelocking set insert into t2 values (3); connection addconroot1; lock tables t2 write; connection default; send insert into t1 values ((select max(id) from t2)), (4); --sleep 1 connection addconroot2; drop trigger t1_trg; create trigger t1_trg before insert on t1 for each row insert into t3 values (new.id); connection addconroot1; unlock tables; connection default; reap; select * from t1; select * from t3; # We should be able to do this even if fancy views are involved connection addconroot1; lock tables t2 write; connection default; send insert into t1 values ((select max(f) from v1)), (6); --sleep 1 connection addconroot2; drop trigger t1_trg; create trigger t1_trg before insert on t1 for each row insert into t3 values (new.id + 100); connection addconroot1; unlock tables; connection default; reap; select * from t1; select * from t3; # This also should work for multi-update # Let us drop trigger to demonstrate that prelocking set is really # rebuilt drop trigger t1_trg; connection addconroot1; lock tables t2 write; connection default; send update t1, t2 set t1.id=10 where t1.id=t2.id; --sleep 1 connection addconroot2; create trigger t1_trg before update on t1 for each row insert into t3 values (new.id); connection addconroot1; unlock tables; connection default; reap; select * from t1; select * from t3; # And even for multi-update converted from ordinary update thanks to view drop view v1; drop trigger t1_trg; create view v1 as select t1.id as id1 from t1, t2 where t1.id= t2.id; insert into t2 values (10); connection addconroot1; lock tables t2 write; connection default; send update v1 set id1= 11; --sleep 1 connection addconroot2; create trigger t1_trg before update on t1 for each row insert into t3 values (new.id + 100); connection addconroot1; unlock tables; connection default; reap; select * from t1; select * from t3; drop function f1; drop view v1; drop table t1, t2, t3; --enable_parsing # # Test for bug #13399 "Crash when executing PS/SP which should activate # trigger which is now dropped". See also test for similar bug for stored # routines in sp-error.test (#12329). 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(); # Actually it is enough to do FLUSH TABLES instead of DROP TRIGGER drop trigger t1_bi; # Server should not crash on these two statements execute stmt1; call p1(); deallocate prepare stmt1; drop procedure p1; # Let us test more complex situation when we alter trigger in such way that # it uses different set of tables (or simply add new trigger). 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(); # Altering trigger forcing it use different set of tables drop trigger t1_bi; create trigger t1_bi after insert on t1 for each row insert into t3 values (new.id); # Until we implement proper mechanism for invalidation of PS/SP when table # or SP's are changed these two statements will fail with 'Table ... was # not locked' error (this mechanism should be based on the new TDC). --error 1100 #ER_TABLE_NOT_LOCKED execute stmt1; --error 1100 #ER_TABLE_NOT_LOCKED call p1(); deallocate prepare stmt1; drop procedure p1; drop table t1, t2, t3; # # BUG#13549 "Server crash with nested stored procedures". # Server should not crash when during execution of stored procedure # we have to parse trigger/function definition and this new trigger/ # function has more local variables declared than invoking stored # procedure and last of these variables is used in argument of NOT # operator. # create table t1 (a int); DELIMITER //; CREATE PROCEDURE `p1`() 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// DELIMITER ;// CALL p1(); drop procedure p1; drop table t1; # # Test for bug #14863 "Triggers: crash if create and there is no current # database". We should not crash and give proper error when database for # trigger or its table is not specified and there is no current database. # connection addconwithoutdb; --error ER_NO_DB_ERROR create trigger t1_bi before insert on test.t1 for each row set @a:=0; --error ER_NO_DB_ERROR create trigger test.t1_bi before insert on t1 for each row set @a:=0; --error ER_NO_DB_ERROR drop trigger t1_bi; connection default; # # Tests for bug #13525 "Rename table does not keep info of triggers" # and bug #17866 "Problem with renaming table with triggers with fully # qualified subject table". # create table t1 (id int); create trigger t1_bi before insert on t1 for each row set @a:=new.id; create trigger t1_ai after insert on test.t1 for each row set @b:=new.id; insert into t1 values (101); select @a, @b; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; rename table t1 to t2; # Trigger should work after rename insert into t2 values (102); select @a, @b; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; # Let us check that the same works for simple ALTER TABLE ... RENAME alter table t2 rename to t3; insert into t3 values (103); select @a, @b; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; # And for more complex ALTER TABLE alter table t3 rename to t4, add column val int default 0; insert into t4 values (104, 1); select @a, @b; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; # .TRN file should be updated with new table name drop trigger t1_bi; drop trigger t1_ai; drop table t4; # Rename between different databases if triggers exist should fail create database mysqltest; use mysqltest; create table t1 (id int); create trigger t1_bi before insert on t1 for each row set @a:=new.id; insert into t1 values (101); select @a; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test' or event_object_schema = 'mysqltest'; --error ER_TRG_IN_WRONG_SCHEMA rename table t1 to test.t2; insert into t1 values (102); select @a; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test' or event_object_schema = 'mysqltest'; # There should be no fantom .TRN files --error ER_TRG_DOES_NOT_EXIST drop trigger test.t1_bi; # Let us also check handling of this restriction in ALTER TABLE ... RENAME --error ER_TRG_IN_WRONG_SCHEMA alter table t1 rename to test.t1; insert into t1 values (103); select @a; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test' or event_object_schema = 'mysqltest'; # Again there should be no fantom .TRN files --error ER_TRG_DOES_NOT_EXIST drop trigger test.t1_bi; --error ER_TRG_IN_WRONG_SCHEMA alter table t1 rename to test.t1, add column val int default 0; insert into t1 values (104); select @a; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test' or event_object_schema = 'mysqltest'; # Table definition should not change show create table t1; # And once again check for fantom .TRN files --error ER_TRG_DOES_NOT_EXIST drop trigger test.t1_bi; drop trigger t1_bi; drop table t1; drop database mysqltest; use test; # And now let us check that the properly handle rename if there is some # error during it (that we rollback such renames completely). create table t1 (id int); create trigger t1_bi before insert on t1 for each row set @a:=new.id; create trigger t1_ai after insert on t1 for each row set @b:=new.id; insert into t1 values (101); select @a, @b; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; # Trick which makes update of second .TRN file impossible system echo dummy >$MYSQLTEST_VARDIR/master-data/test/t1_ai.TRN~; system chmod 000 $MYSQLTEST_VARDIR/master-data/test/t1_ai.TRN~; --replace_result $MYSQLTEST_VARDIR . master-data/ '' --error 1 rename table t1 to t2; # 't1' should be still there and triggers should work correctly insert into t1 values (102); select @a, @b; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; system chmod 600 $MYSQLTEST_VARDIR/master-data/test/t1_ai.TRN~; system rm $MYSQLTEST_VARDIR/master-data/test/t1_ai.TRN~; # Let us check that updates to .TRN files were rolled back too drop trigger t1_bi; drop trigger t1_ai; drop table t1; # Test for bug #16829 "Firing trigger with RETURN crashes the server" # RETURN is not supposed to be used anywhere except functions, so error # should be returned when one attempts to create trigger with RETURN. create table t1 (i int); --error ER_SP_BADRETURN create trigger t1_bi before insert on t1 for each row return 0; insert into t1 values (1); drop table t1; # Test for bug #17764 "Trigger crashes MyISAM table" # # Table was reported as crashed when it was subject table of trigger invoked # by insert statement which was executed with enabled bulk insert mode (which # is actually set of optimizations enabled by handler::start_bulk_insert()) # and this trigger also explicitly referenced it. # The same problem arose when table to which bulk insert was done was also # referenced in function called by insert statement. create table t1 (a varchar(64), b int); create table t2 like t1; create trigger t1_ai after insert on t1 for each row set @a:= (select max(a) from t1); insert into t1 (a) values ("Twas"),("brillig"),("and"),("the"),("slithy"),("toves"), ("Did"),("gyre"),("and"),("gimble"),("in"),("the"),("wabe"); create trigger t2_ai after insert on t2 for each row set @a:= (select max(a) from t2); insert into t2 select * from t1; load data infile '../std_data_ln/words.dat' into table t1 (a); drop trigger t1_ai; drop trigger t2_ai; # Test that the problem for functions is fixed as well create function f1() returns int return (select max(b) from t1); insert into t1 values ("All",f1()),("mimsy",f1()),("were",f1()),("the",f1()),("borogoves",f1()), ("And",f1()),("the",f1()),("mome", f1()),("raths",f1()),("outgrabe",f1()); create function f2() returns int return (select max(b) from t2); insert into t2 select a, f2() from t1; load data infile '../std_data_ln/words.dat' into table t1 (a) set b:= f1(); drop table t1, t2; drop function f1; drop function f2; # # Test for bug #16021 "Wrong index given to function in trigger" which # was caused by the same bulk insert optimization as bug #17764 but had # slightly different symptoms (instead of reporting table as crashed # storage engine reported error number 124) # create table t1(i int not null, j int not null, n numeric(15,2), primary key(i,j)); create table t2(i int not null, n numeric(15,2), primary key(i)); delimiter |; create trigger t1_ai after insert on t1 for each row begin declare sn numeric(15,2); select sum(n) into sn from t1 where i=new.i; replace into t2 values(new.i, sn); end| delimiter ;| insert into t1 values (1,1,10.00),(1,2,10.00),(1,3,10.00),(1,4,10.00),(1,5,10.00), (1,6,10.00),(1,7,10.00),(1,8,10.00),(1,9,10.00),(1,10,10.00), (1,11,10.00),(1,12,10.00),(1,13,10.00),(1,14,10.00),(1,15,10.00); select * from t1; select * from t2; drop tables t1, t2; # # Test for Bug #16461 connection_id() does not work properly inside trigger # --disable_warnings DROP TABLE IF EXISTS t1; --enable_warnings CREATE TABLE t1 ( conn_id INT, trigger_conn_id INT ); CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET NEW.trigger_conn_id = CONNECTION_ID(); INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1); connect (con1,localhost,root,,); INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1); connection default; disconnect con1; SELECT * FROM t1 WHERE conn_id != trigger_conn_id; DROP TRIGGER t1_bi; DROP TABLE t1; # # Bug#6951: Triggers/Traditional: SET @ result wrong # --disable_warnings DROP TABLE IF EXISTS t1; --enable_warnings CREATE TABLE t1 (i1 INT); SET @save_sql_mode=@@sql_mode; SET SQL_MODE=''; CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW SET @x = 5/0; SET SQL_MODE='traditional'; CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW SET @x = 5/0; SET @x=1; INSERT INTO t1 VALUES (@x); SELECT @x; SET @x=2; UPDATE t1 SET i1 = @x; SELECT @x; SET SQL_MODE=''; SET @x=3; INSERT INTO t1 VALUES (@x); SELECT @x; SET @x=4; UPDATE t1 SET i1 = @x; SELECT @x; SET @@sql_mode=@save_sql_mode; DROP TRIGGER t1_ai; DROP TRIGGER t1_au; DROP TABLE t1; # # Test for bug #14635 Accept NEW.x as INOUT parameters to stored # procedures from within triggers # --disable_warnings DROP TABLE IF EXISTS t1; DROP PROCEDURE IF EXISTS p1; DROP PROCEDURE IF EXISTS p2; --enable_warnings CREATE TABLE t1 (i1 INT); # Check that NEW.x pseudo variable is accepted as INOUT and OUT # parameter to stored routine. INSERT INTO t1 VALUES (3); CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET i1 = 5; CREATE PROCEDURE p2(INOUT i1 INT) DETERMINISTIC NO SQL SET i1 = i1 * 7; delimiter //; CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW BEGIN CALL p1(NEW.i1); CALL p2(NEW.i1); END// delimiter ;// UPDATE t1 SET i1 = 11 WHERE i1 = 3; DROP TRIGGER t1_bu; DROP PROCEDURE p2; DROP PROCEDURE p1; # Check that OLD.x pseudo variable is not accepted as INOUT and OUT # parameter to stored routine. INSERT INTO t1 VALUES (13); CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 17; CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW CALL p1(OLD.i1); --error ER_SP_NOT_VAR_ARG UPDATE t1 SET i1 = 19 WHERE i1 = 13; DROP TRIGGER t1_bu; DROP PROCEDURE p1; INSERT INTO t1 VALUES (23); CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 29; CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW CALL p1(OLD.i1); --error ER_SP_NOT_VAR_ARG UPDATE t1 SET i1 = 31 WHERE i1 = 23; DROP TRIGGER t1_bu; DROP PROCEDURE p1; # Check that NEW.x pseudo variable is read-only in the AFTER TRIGGER. INSERT INTO t1 VALUES (37); CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 41; CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW CALL p1(NEW.i1); --error ER_SP_NOT_VAR_ARG UPDATE t1 SET i1 = 43 WHERE i1 = 37; DROP TRIGGER t1_au; DROP PROCEDURE p1; INSERT INTO t1 VALUES (47); CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 49; CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW CALL p1(NEW.i1); --error ER_SP_NOT_VAR_ARG UPDATE t1 SET i1 = 51 WHERE i1 = 47; DROP TRIGGER t1_au; DROP PROCEDURE p1; # Post requisite. SELECT * FROM t1; DROP TABLE t1; # # Bug #18005: Creating a trigger on mysql.event leads to server crash on # scheduler startup # # Bug #18361: Triggers on mysql.user table cause server crash # # We don't allow triggers on the mysql schema delimiter |; --error ER_NO_TRIGGERS_ON_SYSTEM_SCHEMA create trigger wont_work after update on mysql.user for each row begin set @a:= 1; end| # Try when we're already using the mysql schema use mysql| --error ER_NO_TRIGGERS_ON_SYSTEM_SCHEMA create trigger wont_work after update on event for each row begin set @a:= 1; end| use test| delimiter ;| # # Test for BUG#16899: Possible buffer overflow in handling of DEFINER-clause. # # Prepare. --disable_warnings DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; --enable_warnings CREATE TABLE t1(c INT); CREATE TABLE t2(c INT); --error ER_WRONG_STRING_LENGTH CREATE DEFINER=1234567890abcdefGHIKL@localhost TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; --error ER_WRONG_STRING_LENGTH CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW SET @a = 2; # Cleanup. DROP TABLE t1; DROP TABLE t2; # # Bug#20028 Function with select return no data # --disable_warnings drop table if exists t1; drop table if exists t2; drop table if exists t3; drop table if exists t4; --enable_warnings SET @save_sql_mode=@@sql_mode; delimiter |; SET sql_mode='TRADITIONAL'| create table t1 (id int(10) not null primary key, v int(10) )| create table t2 (id int(10) not null primary key, v int(10) )| create table t3 (id int(10) not null primary key, v int(10) )| create table t4 (c int)| create trigger t4_bi before insert on t4 for each row set @t4_bi_called:=1| create trigger t4_bu before update on t4 for each row set @t4_bu_called:=1| insert into t1 values(10, 10)| set @a:=1/0| select 1/0 from t1| create trigger t1_bi before insert on t1 for each row set @a:=1/0| insert into t1 values(20, 20)| drop trigger t1_bi| create trigger t1_bi before insert on t1 for each row begin insert into t2 values (new.id, new.v); update t2 set v=v+1 where id= new.id; replace t3 values (new.id, 0); update t2, t3 set t2.v=new.v, t3.v=new.v where t2.id=t3.id; create temporary table t5 select * from t1; delete from t5; insert into t5 select * from t1; insert into t4 values (0); set @check= (select count(*) from t5); update t4 set c= @check; drop temporary table t5; set @a:=1/0; end| set @check=0, @t4_bi_called=0, @t4_bu_called=0| insert into t1 values(30, 30)| select @check, @t4_bi_called, @t4_bu_called| delimiter ;| SET @@sql_mode=@save_sql_mode; drop table t1; drop table t2; drop table t3; drop table t4; # # Bug#20670 "UPDATE using key and invoking trigger that modifies # this key does not stop" # --disable_warnings drop table if exists t1; --enable_warnings create table t1 (i int, j int key); insert into t1 values (1,1), (2,2), (3,3); create trigger t1_bu before update on t1 for each row set new.j = new.j + 10; # This should not work indefinitely and should cause # expected result update t1 set i= i+ 10 where j > 2; select * from t1; drop table t1; # # Bug #23651 "Server crashes when trigger which uses stored function # invoked from different connections". # --disable_warnings drop table if exists t1; drop function if exists f1; --enable_warnings create table t1 (i int); create function f1() returns int return 10; create trigger t1_bi before insert on t1 for each row set @a:= f1() + 10; insert into t1 values (); select @a; connection addconroot1; insert into t1 values (); select @a; connection default; drop table t1; drop function f1; # # Bug#23703: DROP TRIGGER needs an IF EXISTS # --disable_warnings drop table if exists t1; --enable_warnings create table t1(a int, b varchar(50)); -- error ER_TRG_DOES_NOT_EXIST drop trigger not_a_trigger; drop trigger if exists not_a_trigger; create trigger t1_bi before insert on t1 for each row set NEW.b := "In trigger t1_bi"; insert into t1 values (1, "a"); drop trigger if exists t1_bi; insert into t1 values (2, "b"); drop trigger if exists t1_bi; insert into t1 values (3, "c"); select * from t1; drop table t1; --echo End of 5.0 tests