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; create table t1 (i int); create trigger trg before insert on t1 for each row set @a:=1; set @a:=0; select @a; @a 0 insert into t1 values (1); select @a; @a 1 drop trigger trg; create trigger trg before insert on t1 for each row set @a:=new.i; insert into t1 values (123); select @a; @a 123 drop trigger trg; drop table t1; create table t1 (i int not null, j int); create trigger trg before insert on t1 for each row begin if isnull(new.j) then set new.j:= new.i * 10; end if; end| insert into t1 (i) values (1)| insert into t1 (i,j) values (2, 3)| select * from t1| i j 1 10 2 3 drop trigger trg| drop table t1| create table t1 (i int not null primary key); create trigger trg after insert on t1 for each row set @a:= if(@a,concat(@a, ":", new.i), new.i); set @a:=""; insert into t1 values (2),(3),(4),(5); select @a; @a 2:3:4:5 drop trigger trg; drop table t1; create table t1 (aid int not null primary key, balance int not null default 0); insert into t1 values (1, 1000), (2,3000); create trigger trg before update on t1 for each row begin declare loc_err varchar(255); if abs(new.balance - old.balance) > 1000 then set new.balance:= old.balance; set loc_err := concat("Too big change for aid = ", new.aid); set @update_failed:= if(@update_failed, concat(@a, ":", loc_err), loc_err); end if; end| set @update_failed:=""| update t1 set balance=1500| select @update_failed; select * from t1| @update_failed Too big change for aid = 2 aid balance 1 1500 2 3000 drop trigger trg| drop table t1| create table t1 (i int); insert into t1 values (1),(2),(3),(4); create trigger trg after update on t1 for each row set @total_change:=@total_change + new.i - old.i; set @total_change:=0; update t1 set i=3; select @total_change; @total_change 2 drop trigger trg; drop table t1; create table t1 (i int); insert into t1 values (1),(2),(3),(4); create trigger trg before delete on t1 for each row set @del_sum:= @del_sum + old.i; set @del_sum:= 0; delete from t1 where i <= 3; select @del_sum; @del_sum 6 drop trigger trg; drop table t1; create table t1 (i int); insert into t1 values (1),(2),(3),(4); create trigger trg after delete on t1 for each row set @del:= 1; set @del:= 0; delete from t1 where i <> 0; select @del; @del 1 drop trigger trg; drop table t1; create table t1 (i int, j int); create trigger trg1 before insert on t1 for each row begin if new.j > 10 then set new.j := 10; end if; end| create trigger trg2 before update on t1 for each row begin if old.i % 2 = 0 then set new.j := -1; end if; end| create trigger trg3 after update on t1 for each row begin if new.j = -1 then set @fired:= "Yes"; end if; end| set @fired:=""; insert into t1 values (1,2),(2,3),(3,14); select @fired; @fired select * from t1; i j 1 2 2 3 3 10 update t1 set j= 20; select @fired; @fired Yes select * from t1; i j 1 20 2 -1 3 20 drop trigger trg1; drop trigger trg2; drop trigger trg3; drop table t1; create table t1 (id int not null primary key, data int); create trigger t1_bi before insert on t1 for each row set @log:= concat(@log, "(BEFORE_INSERT: new=(id=", new.id, ", data=", new.data,"))"); create trigger t1_ai after insert on t1 for each row set @log:= concat(@log, "(AFTER_INSERT: new=(id=", new.id, ", data=", new.data,"))"); create trigger t1_bu before update on t1 for each row set @log:= concat(@log, "(BEFORE_UPDATE: old=(id=", old.id, ", data=", old.data, ") new=(id=", new.id, ", data=", new.data,"))"); create trigger t1_au after update on t1 for each row set @log:= concat(@log, "(AFTER_UPDATE: old=(id=", old.id, ", data=", old.data, ") new=(id=", new.id, ", data=", new.data,"))"); create trigger t1_bd before delete on t1 for each row set @log:= concat(@log, "(BEFORE_DELETE: old=(id=", old.id, ", data=", old.data,"))"); create trigger t1_ad after delete on t1 for each row set @log:= concat(@log, "(AFTER_DELETE: old=(id=", old.id, ", data=", old.data,"))"); set @log:= ""; insert into t1 values (1, 1); select @log; @log (BEFORE_INSERT: new=(id=1, data=1))(AFTER_INSERT: new=(id=1, data=1)) set @log:= ""; insert ignore t1 values (1, 2); select @log; @log (BEFORE_INSERT: new=(id=1, data=2)) set @log:= ""; insert into t1 (id, data) values (1, 3), (2, 2) on duplicate key update data= data + 1; select @log; @log (BEFORE_INSERT: new=(id=1, data=3))(BEFORE_UPDATE: old=(id=1, data=1) new=(id=1, data=2))(AFTER_UPDATE: old=(id=1, data=1) new=(id=1, data=2))(BEFORE_INSERT: new=(id=2, data=2))(AFTER_INSERT: new=(id=2, data=2)) set @log:= ""; replace t1 values (1, 4), (3, 3); select @log; @log (BEFORE_INSERT: new=(id=1, data=4))(BEFORE_DELETE: old=(id=1, data=2))(AFTER_DELETE: old=(id=1, data=2))(AFTER_INSERT: new=(id=1, data=4))(BEFORE_INSERT: new=(id=3, data=3))(AFTER_INSERT: new=(id=3, data=3)) drop trigger t1_bd; drop trigger t1_ad; set @log:= ""; replace t1 values (1, 5); select @log; @log (BEFORE_INSERT: new=(id=1, data=5))(AFTER_INSERT: new=(id=1, data=5)) drop table t1; create table t1 (id int primary key, data varchar(10), fk int); create table t2 (event varchar(100)); create table t3 (id int primary key); create trigger t1_ai after insert on t1 for each row insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "'")); insert into t1 (id, data) values (1, "one"), (2, "two"); select * from t1; id data fk 1 one NULL 2 two NULL select * from t2; event INSERT INTO t1 id=1 data='one' INSERT INTO t1 id=2 data='two' drop trigger t1_ai; create trigger t1_bi before insert on t1 for each row begin if exists (select id from t3 where id=new.fk) then insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "' fk=", new.fk)); else insert into t2 values (concat("INSERT INTO t1 FAILED id=", new.id, " data='", new.data, "' fk=", new.fk)); set new.id= NULL; end if; end| insert into t3 values (1); insert into t1 values (4, "four", 1), (5, "five", 2); ERROR 23000: Column 'id' cannot be null select * from t1; id data fk 1 one NULL 2 two NULL 4 four 1 select * from t2; event INSERT INTO t1 id=1 data='one' INSERT INTO t1 id=2 data='two' INSERT INTO t1 id=4 data='four' fk=1 INSERT INTO t1 FAILED id=5 data='five' fk=2 drop table t1, t2, t3; create table t1 (id int primary key, data varchar(10)); create table t2 (seq int); insert into t2 values (10); create function f1 () returns int return (select max(seq) from t2); create trigger t1_bi before insert on t1 for each row begin if new.id > f1() then set new.id:= f1(); end if; end| insert into t1 values (1, "first"); insert into t1 values (f1(), "max"); select * from t1; id data 1 first 10 max drop table t1, t2; drop function f1; create table t1 (id int primary key, fk_t2 int); create table t2 (id int primary key, fk_t3 int); create table t3 (id int primary key); insert into t1 values (1,1), (2,1), (3,2); insert into t2 values (1,1), (2,2); insert into t3 values (1), (2); create trigger t3_ad after delete on t3 for each row delete from t2 where fk_t3=old.id; create trigger t2_ad after delete on t2 for each row delete from t1 where fk_t2=old.id; delete from t3 where id = 1; select * from t1 left join (t2 left join t3 on t2.fk_t3 = t3.id) on t1.fk_t2 = t2.id; id fk_t2 id fk_t3 id 3 2 2 2 2 drop table t1, t2, t3; create table t1 (id int primary key, copy int); create table t2 (id int primary key, data int); insert into t2 values (1,1), (2,2); create trigger t1_bi before insert on t1 for each row set new.copy= (select data from t2 where id = new.id); create trigger t1_bu before update on t1 for each row set new.copy= (select data from t2 where id = new.id); insert into t1 values (1,3), (2,4), (3,3); update t1 set copy= 1 where id = 2; select * from t1; id copy 1 1 2 2 3 NULL drop table t1, t2; create table t1 (i int); create table t3 (i int); create trigger trg before insert on t1 for each row set @a:= old.i; ERROR HY000: There is no OLD row in on INSERT trigger create trigger trg before delete on t1 for each row set @a:= new.i; ERROR HY000: There is no NEW row in on DELETE trigger create trigger trg before update on t1 for each row set old.i:=1; ERROR HY000: Updating of OLD row is not allowed in trigger create trigger trg before delete on t1 for each row set new.i:=1; ERROR HY000: There is no NEW row in on DELETE trigger create trigger trg after update on t1 for each row set new.i:=1; ERROR HY000: Updating of NEW row is not allowed in after trigger create trigger trg before update on t1 for each row set new.j:=1; ERROR 42S22: Unknown column 'j' in 'NEW' create trigger trg before update on t1 for each row set @a:=old.j; ERROR 42S22: Unknown column 'j' in 'OLD' create trigger trg before insert on t2 for each row set @a:=1; ERROR 42S02: Table 'test.t2' doesn't exist create trigger trg before insert on t1 for each row set @a:=1; create trigger trg after insert on t1 for each row set @a:=1; ERROR HY000: Trigger already exists create trigger trg2 before insert on t1 for each row set @a:=1; ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table' create trigger trg before insert on t3 for each row set @a:=1; ERROR HY000: Trigger already exists create trigger trg2 before insert on t3 for each row set @a:=1; drop trigger trg2; drop trigger trg; drop trigger trg; ERROR HY000: Trigger does not exist create view v1 as select * from t1; create trigger trg before insert on v1 for each row set @a:=1; ERROR HY000: 'test.v1' is not BASE TABLE drop view v1; drop table t1; drop table t3; create temporary table t1 (i int); create trigger trg before insert on t1 for each row set @a:=1; ERROR HY000: Trigger's 't1' is view or temporary table drop table t1; 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; create table t1 (i int) engine=myisam; insert into t1 values (1), (2); create trigger trg1 before delete on t1 for each row set @del_before:= @del_before + old.i; create trigger trg2 after delete on t1 for each row set @del_after:= @del_after + old.i; set @del_before:=0, @del_after:= 0; delete from t1; select @del_before, @del_after; @del_before @del_after 3 3 drop trigger trg1; drop trigger trg2; drop table t1; create table t1 (a int); create trigger trg1 before insert on t1 for each row set new.a= 10; drop table t1; create table t1 (a int); insert into t1 values (); select * from t1; a NULL drop table t1; create database mysqltest; use mysqltest; create table t1 (i int); create trigger trg1 before insert on t1 for each row set @a:= 1; drop database mysqltest; use test; create database mysqltest; create table mysqltest.t1 (i int); create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1; ERROR HY000: Trigger in wrong schema use mysqltest; create trigger test.trg1 before insert on t1 for each row set @a:= 1; ERROR HY000: Trigger in wrong schema drop database mysqltest; use test; create table t1 (i int, j int default 10, k int not null, key (k)); create table t2 (i int); insert into t1 (i, k) values (1, 1); insert into t2 values (1); create trigger trg1 before update on t1 for each row set @a:= @a + new.j - old.j; create trigger trg2 after update on t1 for each row set @b:= "Fired"; set @a:= 0, @b:= ""; update t1, t2 set j = j + 10 where t1.i = t2.i; select @a, @b; @a @b 10 Fired insert into t1 values (2, 13, 2); insert into t2 values (2); set @a:= 0, @b:= ""; update t1, t2 set j = j + 15 where t1.i = t2.i and t1.k >= 2; select @a, @b; @a @b 15 Fired create trigger trg3 before delete on t1 for each row set @c:= @c + old.j; create trigger trg4 before delete on t2 for each row set @d:= @d + old.i; create trigger trg5 after delete on t1 for each row set @e:= "After delete t1 fired"; create trigger trg6 after delete on t2 for each row set @f:= "After delete t2 fired"; set @c:= 0, @d:= 0, @e:= "", @f:= ""; delete t1, t2 from t1, t2 where t1.i = t2.i; select @c, @d, @e, @f; @c @d @e @f 48 3 After delete t1 fired After delete t2 fired drop table t1, t2; create table t1 (i int, j int default 10)| create table t2 (i int)| insert into t2 values (1), (2)| create trigger trg1 before insert on t1 for each row begin if new.i = 1 then set new.j := 1; end if; end| create trigger trg2 after insert on t1 for each row set @a:= 1| set @a:= 0| insert into t1 (i) select * from t2| select * from t1| i j 1 1 2 10 select @a| @a 1 drop table t1, t2| create table t1 (i int, j int, k int); create trigger trg1 before insert on t1 for each row set new.k = new.i; create trigger trg2 after insert on t1 for each row set @b:= "Fired"; set @b:=""; load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@a, i); select *, @b from t1; i j k @b 10 NULL 10 Fired 15 NULL 15 Fired set @b:=""; load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, j); select *, @b from t1; i j k @b 10 NULL 10 Fired 15 NULL 15 Fired 1 2 1 Fired 3 4 3 Fired 5 6 5 Fired drop table t1; create table t1 (i int, at int, k int, key(k)) engine=myisam; create table t2 (i int); insert into t1 values (1, 1, 1); insert into t2 values (1), (2), (3); create trigger ai after insert on t1 for each row set @a:= new.at; create trigger au after update on t1 for each row set @a:= new.at; create trigger ad after delete on t1 for each row set @a:= old.at; alter table t1 drop column at; select * from t1; i k 1 1 insert into t1 values (2, 1); ERROR 42S22: Unknown column 'at' in 'NEW' select * from t1; i k 1 1 2 1 update t1 set k = 2 where i = 2; ERROR 42S22: Unknown column 'at' in 'NEW' select * from t1; i k 1 1 2 2 delete from t1 where i = 2; ERROR 42S22: Unknown column 'at' in 'OLD' select * from t1; i k 1 1 load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k); ERROR 42S22: Unknown column 'at' in 'NEW' select * from t1; i k 1 1 1 2 insert into t1 select 3, 3; ERROR 42S22: Unknown column 'at' in 'NEW' select * from t1; i k 1 1 1 2 3 3 update t1, t2 set k = k + 10 where t1.i = t2.i; ERROR 42S22: Unknown column 'at' in 'NEW' select * from t1; i k 1 11 1 2 3 3 update t1, t2 set k = k + 10 where t1.i = t2.i and k < 3; ERROR 42S22: Unknown column 'at' in 'NEW' select * from t1; i k 1 11 1 12 3 3 delete t1, t2 from t1 straight_join t2 where t1.i = t2.i; ERROR 42S22: Unknown column 'at' in 'OLD' select * from t1; i k 1 12 3 3 delete t2, t1 from t2 straight_join t1 where t1.i = t2.i; ERROR 42S22: Unknown column 'at' in 'OLD' select * from t1; i k 3 3 alter table t1 add primary key (i); insert into t1 values (3, 4) on duplicate key update k= k + 10; ERROR 42S22: Unknown column 'at' in 'NEW' select * from t1; i k 3 13 replace into t1 values (3, 3); ERROR 42S22: Unknown column 'at' in 'OLD' select * from t1; i k drop table t1, t2; create table t1 (i int, bt int, k int, key(k)) engine=myisam; create table t2 (i int); insert into t1 values (1, 1, 1), (2, 2, 2); insert into t2 values (1), (2), (3); create trigger bi before insert on t1 for each row set @a:= new.bt; create trigger bu before update on t1 for each row set @a:= new.bt; create trigger bd before delete on t1 for each row set @a:= old.bt; alter table t1 drop column bt; insert into t1 values (3, 3); ERROR 42S22: Unknown column 'bt' in 'NEW' select * from t1; i k 1 1 2 2 update t1 set i = 2; ERROR 42S22: Unknown column 'bt' in 'NEW' select * from t1; i k 1 1 2 2 delete from t1; ERROR 42S22: Unknown column 'bt' in 'OLD' select * from t1; i k 1 1 2 2 load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k); ERROR 42S22: Unknown column 'bt' in 'NEW' select * from t1; i k 1 1 2 2 insert into t1 select 3, 3; ERROR 42S22: Unknown column 'bt' in 'NEW' select * from t1; i k 1 1 2 2 update t1, t2 set k = k + 10 where t1.i = t2.i; ERROR 42S22: Unknown column 'bt' in 'NEW' select * from t1; i k 1 1 2 2 update t1, t2 set k = k + 10 where t1.i = t2.i and k < 2; ERROR 42S22: Unknown column 'bt' in 'NEW' select * from t1; i k 1 1 2 2 delete t1, t2 from t1 straight_join t2 where t1.i = t2.i; ERROR 42S22: Unknown column 'bt' in 'OLD' select * from t1; i k 1 1 2 2 delete t2, t1 from t2 straight_join t1 where t1.i = t2.i; ERROR 42S22: Unknown column 'bt' in 'OLD' select * from t1; i k 1 1 2 2 alter table t1 add primary key (i); drop trigger bi; insert into t1 values (2, 4) on duplicate key update k= k + 10; ERROR 42S22: Unknown column 'bt' in 'NEW' select * from t1; i k 1 1 2 2 replace into t1 values (2, 4); ERROR 42S22: Unknown column 'bt' in 'OLD' select * from t1; i k 1 1 2 2 drop table t1, t2; drop function if exists bug5893; create table t1 (col1 int, col2 int); insert into t1 values (1, 2); create function bug5893 () returns int return 5; create trigger t1_bu before update on t1 for each row set new.col1= bug5893(); drop function bug5893; update t1 set col2 = 4; ERROR 42000: FUNCTION test.bug5893 does not exist drop trigger t1_bu; drop table t1; 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 t1_af after insert on t1 for each row set @a=10; insert into t1 values (0); select * from t1; t1 column 5 5 select @a; @a 10 show triggers; Trigger Event Table Statement Timing Created sql_mode Definer t1_bi INSERT t1 set new."t1 column" = 5 BEFORE # REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI root@localhost t1_af INSERT t1 set @a=10 AFTER # root@localhost drop table t1; set sql_mode="traditional"; create table t1 (a date); insert into t1 values ('2004-01-00'); ERROR 22007: Incorrect date value: '2004-01-00' for column 'a' at row 1 set sql_mode=""; create trigger t1_bi before insert on t1 for each row set new.a = '2004-01-00'; set sql_mode="traditional"; insert into t1 values ('2004-01-01'); select * from t1; a 2004-01-00 set sql_mode=default; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show triggers; Trigger Event Table Statement Timing Created sql_mode Definer t1_bi INSERT t1 set new.a = '2004-01-00' BEFORE # root@localhost drop table t1; create table t1 (id int); create trigger t1_ai after insert on t1 for each row flush tables; ERROR 0A000: FLUSH is not allowed in stored function or trigger create trigger t1_ai after insert on t1 for each row flush privileges; ERROR 0A000: FLUSH is not allowed in stored function or trigger create procedure p1() flush tables; create trigger t1_ai after insert on t1 for each row call p1(); insert into t1 values (0); ERROR 0A000: FLUSH is not allowed in stored function or trigger drop procedure p1; create procedure p1() flush privileges; insert into t1 values (0); ERROR 0A000: FLUSH is not allowed in stored function or trigger drop procedure p1; drop table t1; create table t1 (id int, data int, username varchar(16)); insert into t1 (id, data) values (1, 0); create trigger t1_whoupdated before update on t1 for each row begin declare user varchar(32); declare i int; select user() into user; set NEW.username = user; select count(*) from ((select 1) union (select 2)) as d1 into i; end| update t1 set data = 1; update t1 set data = 2; drop table t1; create table t1 (c1 int, c2 datetime); create trigger tr1 before insert on t1 for each row begin set new.c2= '2004-04-01'; select 'hello'; end| ERROR 0A000: Not allowed to return a result set from a trigger insert into t1 (c1) values (1),(2),(3); select * from t1; c1 c2 1 NULL 2 NULL 3 NULL drop procedure if exists bug11587; create procedure bug11587(x char(16)) begin select "hello"; select "hello again"; end| create trigger tr1 before insert on t1 for each row begin call bug11587(); set new.c2= '2004-04-02'; end| insert into t1 (c1) values (4),(5),(6); ERROR 0A000: Not allowed to return a result set from a trigger select * from t1; c1 c2 1 NULL 2 NULL 3 NULL drop procedure bug11587; drop table t1; 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); set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth; set @@max_sp_recursion_depth=100; insert into t1 values (1); ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS; select * from t1; f1 1 select * from t2; f2 2 drop trigger t1_ai; drop trigger t2_ai; create trigger t1_bu before update on t1 for each row insert into t1 values (2); update t1 set f1= 10; ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. select * from t1; f1 1 drop trigger t1_bu; create trigger t1_bu before update on t1 for each row delete from t1 where f1=new.f1; update t1 set f1= 10; ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. select * from t1; f1 1 drop trigger t1_bu; create trigger t1_bi before insert on t1 for each row set new.f1=(select sum(f1) from t1); insert into t1 values (3); select * from t1; f1 1 1 drop trigger t1_bi; drop tables t1, t2; create table t1 (id int); create table t2 (id int); create trigger t1_bi before insert on t1 for each row insert into t2 values (new.id); prepare stmt1 from "insert into t1 values (10)"; create procedure p1() insert into t1 values (10); call p1(); drop trigger t1_bi; execute stmt1; call p1(); deallocate prepare stmt1; drop procedure p1; create table t3 (id int); create trigger t1_bi after insert on t1 for each row insert into t2 values (new.id); prepare stmt1 from "insert into t1 values (10)"; create procedure p1() insert into t1 values (10); call p1(); drop trigger t1_bi; create trigger t1_bi after insert on t1 for each row insert into t3 values (new.id); execute stmt1; ERROR HY000: Table 't3' was not locked with LOCK TABLES call p1(); ERROR HY000: Table 't3' was not locked with LOCK TABLES deallocate prepare stmt1; drop procedure p1; drop table t1, t2, t3; create table t1 (a int); 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// CALL p1(); drop procedure p1; drop table t1; create trigger t1_bi before insert on test.t1 for each row set @a:=0; ERROR 3D000: No database selected create trigger test.t1_bi before insert on t1 for each row set @a:=0; ERROR 3D000: No database selected drop trigger t1_bi; ERROR 3D000: No database selected 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; @a @b 101 101 select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; trigger_schema trigger_name event_object_schema event_object_table action_statement test t1_bi test t1 set @a:=new.id test t1_ai test t1 set @b:=new.id rename table t1 to t2; insert into t2 values (102); select @a, @b; @a @b 102 102 select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; trigger_schema trigger_name event_object_schema event_object_table action_statement test t1_bi test t2 set @a:=new.id test t1_ai test t2 set @b:=new.id alter table t2 rename to t3; insert into t3 values (103); select @a, @b; @a @b 103 103 select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; trigger_schema trigger_name event_object_schema event_object_table action_statement test t1_bi test t3 set @a:=new.id test t1_ai test t3 set @b:=new.id alter table t3 rename to t4, add column val int default 0; insert into t4 values (104, 1); select @a, @b; @a @b 104 104 select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; trigger_schema trigger_name event_object_schema event_object_table action_statement test t1_bi test t4 set @a:=new.id test t1_ai test t4 set @b:=new.id drop trigger t1_bi; drop trigger t1_ai; drop table t4; 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; @a 101 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'; trigger_schema trigger_name event_object_schema event_object_table action_statement mysqltest t1_bi mysqltest t1 set @a:=new.id rename table t1 to test.t2; ERROR HY000: Trigger in wrong schema insert into t1 values (102); select @a; @a 102 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'; trigger_schema trigger_name event_object_schema event_object_table action_statement mysqltest t1_bi mysqltest t1 set @a:=new.id drop trigger test.t1_bi; ERROR HY000: Trigger does not exist alter table t1 rename to test.t1; ERROR HY000: Trigger in wrong schema insert into t1 values (103); select @a; @a 103 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'; trigger_schema trigger_name event_object_schema event_object_table action_statement mysqltest t1_bi mysqltest t1 set @a:=new.id drop trigger test.t1_bi; ERROR HY000: Trigger does not exist alter table t1 rename to test.t1, add column val int default 0; ERROR HY000: Trigger in wrong schema insert into t1 values (104); select @a; @a 104 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'; trigger_schema trigger_name event_object_schema event_object_table action_statement mysqltest t1_bi mysqltest t1 set @a:=new.id show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop trigger test.t1_bi; ERROR HY000: Trigger does not exist drop trigger t1_bi; drop table t1; drop database mysqltest; use test; 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; @a @b 101 101 select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; trigger_schema trigger_name event_object_schema event_object_table action_statement test t1_bi test t1 set @a:=new.id test t1_ai test t1 set @b:=new.id rename table t1 to t2; ERROR HY000: Can't create/write to file './test/t1_ai.TRN~' (Errcode: 13) insert into t1 values (102); select @a, @b; @a @b 102 102 select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; trigger_schema trigger_name event_object_schema event_object_table action_statement test t1_bi test t1 set @a:=new.id test t1_ai test t1 set @b:=new.id drop trigger t1_bi; drop trigger t1_ai; drop table t1; create table t1 (i int); create trigger t1_bi before insert on t1 for each row return 0; ERROR 42000: RETURN is only allowed in a FUNCTION insert into t1 values (1); drop table t1; 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; 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 function f1; drop function f2; drop table t1, t2; 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)); 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| 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; i j n 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 t2; i n 1 150.00 drop tables t1, t2; DROP TABLE IF EXISTS t1; 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); INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1); SELECT * FROM t1 WHERE conn_id != trigger_conn_id; conn_id trigger_conn_id DROP TRIGGER t1_bi; DROP TABLE t1; DROP TABLE IF EXISTS t1; 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; @x NULL SET @x=2; UPDATE t1 SET i1 = @x; ERROR 22012: Division by 0 SELECT @x; @x 2 SET SQL_MODE=''; SET @x=3; INSERT INTO t1 VALUES (@x); SELECT @x; @x NULL SET @x=4; UPDATE t1 SET i1 = @x; ERROR 22012: Division by 0 SELECT @x; @x 4 SET @@sql_mode=@save_sql_mode; DROP TRIGGER t1_ai; DROP TRIGGER t1_au; DROP TABLE t1; DROP TABLE IF EXISTS t1; DROP PROCEDURE IF EXISTS p1; DROP PROCEDURE IF EXISTS p2; CREATE TABLE t1 (i1 INT); 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; CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW BEGIN CALL p1(NEW.i1); CALL p2(NEW.i1); END// UPDATE t1 SET i1 = 11 WHERE i1 = 3; DROP TRIGGER t1_bu; DROP PROCEDURE p2; DROP PROCEDURE p1; 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); UPDATE t1 SET i1 = 19 WHERE i1 = 13; ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 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); UPDATE t1 SET i1 = 31 WHERE i1 = 23; ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger DROP TRIGGER t1_bu; DROP PROCEDURE p1; 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); UPDATE t1 SET i1 = 43 WHERE i1 = 37; ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 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); UPDATE t1 SET i1 = 51 WHERE i1 = 47; ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger DROP TRIGGER t1_au; DROP PROCEDURE p1; SELECT * FROM t1; i1 35 13 23 43 51 DROP TABLE t1; create trigger wont_work after update on mysql.user for each row begin set @a:= 1; end| ERROR HY000: Triggers can not be created on system tables use mysql| create trigger wont_work after update on event for each row begin set @a:= 1; end| ERROR HY000: Triggers can not be created on system tables End of 5.0 tests