trigger.result

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

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_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 '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
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 table t1;
drop function f1;
drop function f2;