trigger.result

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

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/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/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/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 'NEW'
select * from t1;
i	k
3	3
alter table t1 add ts timestamp default now();
replace into t1 (i, k) values (3, 13);
ERROR 42S22: Unknown column 'at' in 'OLD'
select * from t1;
i	k	ts
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/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 'NEW'
select * from t1;
i	k
1	1
2	2
alter table t1 add ts timestamp default now();
replace into t1 (i, k) values (2, 11);
ERROR 42S22: Unknown column 'bt' in 'OLD'
select * from t1;
i	k	ts
1	1	0000-00-00 00:00:00
2	2	0000-00-00 00:00:00
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