set sql_mode=strict_all_tables; set time_zone="+02:00"; create table t1 (a int not null, b int, c int); create trigger trgi before insert on t1 for each row set new.a=if(new.a is null,new.b,new.c); insert t1 values (10, NULL, 1); insert t1 values (NULL, 2, NULL); insert t1 values (NULL, NULL, 20); ERROR 23000: Column 'a' cannot be null insert t1 values (1, 2, NULL); ERROR 23000: Column 'a' cannot be null select * from t1; a b c 1 NULL 1 2 2 NULL insert ignore t1 values (NULL, NULL, 30); Warnings: Warning 1048 Column 'a' cannot be null insert ignore t1 values (1, 3, NULL); Warnings: Warning 1048 Column 'a' cannot be null select * from t1; a b c 1 NULL 1 2 2 NULL 0 NULL 30 0 3 NULL insert t1 set a=NULL, b=4, c=a; select * from t1; a b c 1 NULL 1 2 2 NULL 0 NULL 30 0 3 NULL 4 4 NULL delete from t1; insert t1 (a,c) values (10, 1); insert t1 (a,b) values (NULL, 2); insert t1 (a,c) values (NULL, 20); ERROR 23000: Column 'a' cannot be null insert t1 (a,b) values (1, 2); ERROR 23000: Column 'a' cannot be null select * from t1; a b c 1 NULL 1 2 2 NULL delete from t1; insert t1 select 10, NULL, 1; insert t1 select NULL, 2, NULL; insert t1 select NULL, NULL, 20; ERROR 23000: Column 'a' cannot be null insert t1 select 1, 2, NULL; ERROR 23000: Column 'a' cannot be null insert ignore t1 select NULL, NULL, 30; Warnings: Warning 1048 Column 'a' cannot be null insert ignore t1 select 1, 3, NULL; Warnings: Warning 1048 Column 'a' cannot be null select * from t1; a b c 1 NULL 1 2 2 NULL 0 NULL 30 0 3 NULL delete from t1; insert delayed t1 values (10, NULL, 1); insert delayed t1 values (NULL, 2, NULL); insert delayed t1 values (NULL, NULL, 20); ERROR 23000: Column 'a' cannot be null insert delayed t1 values (1, 2, NULL); ERROR 23000: Column 'a' cannot be null select * from t1; a b c 1 NULL 1 2 2 NULL insert delayed ignore t1 values (NULL, NULL, 30); Warnings: Warning 1048 Column 'a' cannot be null insert delayed ignore t1 values (1, 3, NULL); Warnings: Warning 1048 Column 'a' cannot be null flush table t1; select * from t1; a b c 1 NULL 1 2 2 NULL 0 NULL 30 0 3 NULL delete from t1; alter table t1 add primary key (a); create trigger trgu before update on t1 for each row set new.a=if(new.a is null,new.b,new.c); insert t1 values (100,100,100), (200,200,200), (300,300,300); insert t1 values (100,100,100) on duplicate key update a=10, b=NULL, c=1; insert t1 values (200,200,200) on duplicate key update a=NULL, b=2, c=NULL; insert t1 values (300,300,300) on duplicate key update a=NULL, b=NULL, c=20; ERROR 23000: Column 'a' cannot be null insert t1 values (300,300,300) on duplicate key update a=1, b=2, c=NULL; ERROR 23000: Column 'a' cannot be null select * from t1; a b c 1 NULL 1 2 2 NULL 300 300 300 delete from t1; insert t1 values (1,100,1), (2,200,2); replace t1 values (10, NULL, 1); replace t1 values (NULL, 2, NULL); replace t1 values (NULL, NULL, 30); ERROR 23000: Column 'a' cannot be null replace t1 values (1, 3, NULL); ERROR 23000: Column 'a' cannot be null select * from t1; a b c 1 NULL 1 2 2 NULL delete from t1; insert t1 values (100,100,100), (200,200,200), (300,300,300); update t1 set a=10, b=NULL, c=1 where a=100; update t1 set a=NULL, b=2, c=NULL where a=200; update t1 set a=NULL, b=NULL, c=20 where a=300; ERROR 23000: Column 'a' cannot be null update t1 set a=1, b=2, c=NULL where a=300; ERROR 23000: Column 'a' cannot be null select * from t1; a b c 1 NULL 1 2 2 NULL 300 300 300 set statement sql_mode='' for update t1 set a=1, b=2, c=NULL where a > 1; ERROR 23000: Duplicate entry '0' for key 'PRIMARY' select * from t1; a b c 1 NULL 1 0 2 NULL 300 300 300 update t1 set a=NULL, b=4, c=a where a=300; select * from t1; a b c 1 NULL 1 0 2 NULL 4 4 NULL delete from t1; create table t2 (d int, e int); insert t1 values (100,100,100), (200,200,200), (300,300,300); insert t2 select a,b from t1; update t1,t2 set a=10, b=NULL, c=1 where b=d and e=100; update t1,t2 set a=NULL, b=2, c=NULL where b=d and e=200; update t1,t2 set a=NULL, b=NULL, c=20 where b=d and e=300; ERROR 23000: Column 'a' cannot be null update t1,t2 set a=1, b=2, c=NULL where b=d and e=300; ERROR 23000: Column 'a' cannot be null select * from t1; a b c 1 NULL 1 2 2 NULL 300 300 300 update t1,t2 set a=NULL, b=4, c=a where b=d and e=300; select * from t1; a b c 1 NULL 1 2 2 NULL 4 4 300 delete from t1; insert t2 values (2,2); create view v1 as select * from t1, t2 where d=2; insert v1 (a,c) values (10, 1); insert v1 (a,b) values (NULL, 2); insert v1 (a,c) values (NULL, 20); ERROR 23000: Column 'a' cannot be null insert v1 (a,b) values (1, 2); ERROR 23000: Column 'a' cannot be null select * from v1; a b c d e 1 NULL 1 2 2 2 2 NULL 2 2 delete from t1; drop view v1; drop table t2; load data infile 'mdev8605.txt' into table t1 fields terminated by ','; ERROR 23000: Column 'a' cannot be null select * from t1; a b c 1 NULL 1 2 2 NULL drop table t1; create table t1 (a timestamp not null default now() on update now(), b int auto_increment primary key); create trigger trgi before insert on t1 for each row set new.a=if(new.a is null, '2000-10-20 10:20:30', NULL); set statement timestamp=777777777 for insert t1 (a) values (NULL); set statement timestamp=888888888 for insert t1 (a) values ('1999-12-11 10:9:8'); select b, a, unix_timestamp(a) from t1; b a unix_timestamp(a) 1 2000-10-20 10:20:30 972030030 2 1998-03-03 03:34:48 888888888 set statement timestamp=999999999 for update t1 set b=3 where b=2; select b, a, unix_timestamp(a) from t1; b a unix_timestamp(a) 1 2000-10-20 10:20:30 972030030 3 2001-09-09 03:46:39 999999999 create trigger trgu before update on t1 for each row set new.a='2011-11-11 11:11:11'; update t1 set b=4 where b=3; select b, a, unix_timestamp(a) from t1; b a unix_timestamp(a) 1 2000-10-20 10:20:30 972030030 4 2011-11-11 11:11:11 1321002671 drop table t1; create table t1 (a int auto_increment primary key); create trigger trgi before insert on t1 for each row set new.a=if(new.a is null, 5, NULL); insert t1 values (NULL); insert t1 values (10); select a from t1; a 5 6 drop table t1; create table t1 (a int, b int, c int); create trigger trgi before insert on t1 for each row set new.a=if(new.a is null,new.b,new.c); insert t1 values (10, NULL, 1); insert t1 values (NULL, 2, NULL); insert t1 values (NULL, NULL, 20); insert t1 values (1, 2, NULL); select * from t1; a b c 1 NULL 1 2 2 NULL NULL NULL 20 NULL 2 NULL drop table t1; create table t1 (a1 tinyint not null, a2 timestamp not null, a3 tinyint not null auto_increment primary key, b tinyint, c int not null); create trigger trgi before insert on t1 for each row begin if new.b=1 then set new.a1=if(new.c,new.c,null); end if; if new.b=2 then set new.a2=if(new.c,new.c,null); end if; if new.b=3 then set new.a3=if(new.c,new.c,null); end if; end| set statement timestamp=777777777 for load data infile 'sep8605.txt' into table t1 fields terminated by ','; ERROR 23000: Column 'a1' cannot be null select * from t1; a1 a2 a3 b c 1 2010-11-12 01:02:03 10 0 0 2 2010-11-12 01:02:03 11 1 2 3 1994-08-25 03:22:57 12 0 0 4 2000-09-08 07:06:05 13 2 908070605 5 1994-08-25 03:22:57 14 2 0 6 2010-11-12 01:02:03 15 0 0 7 2010-11-12 01:02:03 20 3 20 8 2010-11-12 01:02:03 21 3 0 delete from t1; set statement timestamp=777777777 for load data infile 'sep8605.txt' into table t1 fields terminated by ',' (@a,a2,a3,b,c) set a1=100-@a; ERROR 23000: Column 'a1' cannot be null select 100-a1,a2,a3,b,c from t1; 100-a1 a2 a3 b c 1 2010-11-12 01:02:03 10 0 0 98 2010-11-12 01:02:03 11 1 2 3 1994-08-25 03:22:57 12 0 0 4 2000-09-08 07:06:05 13 2 908070605 5 1994-08-25 03:22:57 14 2 0 6 2010-11-12 01:02:03 22 0 0 7 2010-11-12 01:02:03 20 3 20 8 2010-11-12 01:02:03 23 3 0 delete from t1; set statement timestamp=777777777 for load data infile 'fix8605.txt' into table t1 fields terminated by ''; ERROR 23000: Column 'a1' cannot be null select * from t1; a1 a2 a3 b c 1 2010-11-12 01:02:03 10 0 0 5 1994-08-25 03:22:57 14 2 0 8 2010-11-12 01:02:03 24 3 0 delete from t1; set statement timestamp=777777777 for load xml infile 'xml8605.txt' into table t1 rows identified by ''; ERROR 23000: Column 'a1' cannot be null select * from t1; a1 a2 a3 b c 1 2010-11-12 01:02:03 10 0 0 2 2010-11-12 01:02:03 11 1 2 3 1994-08-25 03:22:57 12 0 0 4 2000-09-08 07:06:05 13 2 908070605 5 1994-08-25 03:22:57 14 2 0 6 2010-11-12 01:02:03 25 0 0 7 2010-11-12 01:02:03 20 3 20 8 2010-11-12 01:02:03 26 3 0 drop table t1; create table t1 (a int not null default 5, b int, c int); create trigger trgi before insert on t1 for each row set new.b=new.c; insert t1 values (DEFAULT,2,1); select * from t1; a b c 5 1 1 drop table t1; create table t1 (a int not null, b int not null default 5, c int); create trigger trgi before insert on t1 for each row begin if new.c=1 then set new.a=1, new.b=1; end if; if new.c=2 then set new.a=NULL, new.b=NULL; end if; if new.c=3 then set new.a=2; end if; end| insert t1 values (9, 9, 1); insert t1 values (9, 9, 2); ERROR 23000: Column 'a' cannot be null insert t1 (a,c) values (9, 3); select * from t1; a b c 1 1 1 2 5 3 drop table t1; set session sql_mode ='no_auto_value_on_zero'; create table t1 (id int unsigned auto_increment primary key); insert t1 values (0); select * from t1; id 0 delete from t1; create trigger t1_bi before insert on t1 for each row begin end; insert t1 values (0); insert t1 (id) values (0); ERROR 23000: Duplicate entry '0' for key 'PRIMARY' drop table t1; create table t1 (a int not null, b int); create trigger trgi before update on t1 for each row do 1; insert t1 values (1,1),(2,2),(3,3),(1,4); create table t2 select a as c, b as d from t1; update t1 set a=(select count(c) from t2 where c+1=a+1 group by a); select * from t1; a b 2 1 1 2 1 3 2 4 drop table t1, t2; create table t1 (a int not null); create table t2 (f1 int unsigned not null, f2 int); insert into t2 values (1, null); create trigger tr1 before update on t1 for each row do 1; create trigger tr2 after update on t2 for each row update t1 set a=new.f2; update t2 set f2=1 where f1=1; drop table t1, t2; create table t1 (a int not null, primary key (a)); insert into t1 (a) values (1); show columns from t1; Field Type Null Key Default Extra a int(11) NO PRI NULL create trigger t1bu before update on t1 for each row begin end; show columns from t1; Field Type Null Key Default Extra a int(11) NO PRI NULL insert into t1 (a) values (3); show columns from t1; Field Type Null Key Default Extra a int(11) NO PRI NULL drop table t1; create table t1 ( pk int primary key, i int, v1 int as (i) virtual, v2 int as (i) virtual ); create trigger tr before update on t1 for each row set @a = 1; insert into t1 (pk, i) values (null, null); ERROR 23000: Column 'pk' cannot be null drop table t1;