1SET @@session.storage_engine = 'MyISAM'; 2create table t1 (a int, 3b int as (a/10), 4c int as (a/10) persistent); 5create table t2 (a timestamp); 6create trigger trg1 before insert on t1 for each row 7begin 8if (new.b < 10) then 9set new.a:= 100; 10set new.b:= 9; 11set new.c:= 9; 12end if; 13if (new.c > 50) then 14set new.a:= 500; 15end if; 16end| 17create trigger trg2 after insert on t1 for each row 18begin 19if (new.b >= 60) then 20insert into t2 values (now()); 21end if; 22end| 23create function f1() 24returns int 25begin 26declare sum1 int default '0'; 27declare cur1 cursor for select sum(b) from t1; 28open cur1; 29fetch cur1 into sum1; 30close cur1; 31return sum1; 32end| 33set sql_warnings = 1; 34insert into t1 (a) values (200); 35select * from t1; 36a b c 37200 20 20 38select * from t2; 39a 40insert into t1 (a) values (10); 41select * from t1; 42a b c 43200 20 20 44100 10 10 45select * from t2; 46a 47insert into t1 (a) values (600); 48select * from t1; 49a b c 50200 20 20 51100 10 10 52500 50 50 53select * from t2; 54a 55select f1(); 56f1() 5780 58set sql_warnings = 0; 59drop trigger trg1; 60drop trigger trg2; 61drop table t2; 62create procedure p1() 63begin 64declare i int default '0'; 65create table t2 like t1; 66insert into t2 (a) values (100), (200); 67begin 68declare cur1 cursor for select sum(c) from t2; 69open cur1; 70fetch cur1 into i; 71close cur1; 72if (i=30) then 73insert into t1 values (300,default,default); 74end if; 75end; 76end| 77delete from t1; 78call p1(); 79select * from t2; 80a b c 81100 10 10 82200 20 20 83select * from t1; 84a b c 85300 30 30 86drop table t1,t2; 87drop procedure p1; 88# 89# MDEV-3845 values of virtual columns are not computed for triggers 90# 91CREATE TABLE t1 ( 92a INTEGER UNSIGNED NULL DEFAULT NULL, 93b INTEGER UNSIGNED GENERATED ALWAYS AS (a) VIRTUAL 94); 95CREATE TABLE t2 (c INTEGER UNSIGNED NOT NULL); 96CREATE TRIGGER t1_ins_aft 97AFTER INSERT 98ON t1 99FOR EACH ROW 100BEGIN 101INSERT INTO t2 (c) VALUES (NEW.b); 102END | 103CREATE TRIGGER t1_del_bef 104BEFORE DELETE 105ON t1 106FOR EACH ROW 107BEGIN 108INSERT INTO t2 (c) VALUES (OLD.b); 109END | 110INSERT INTO t1 (a) VALUES (1), (2), (3); 111SELECT * FROM t2; 112c 1131 1142 1153 116DELETE FROM t1; 117SELECT * FROM t2; 118c 1191 1202 1213 1221 1232 1243 125DROP TRIGGER t1_ins_aft; 126DROP TRIGGER t1_del_bef; 127DROP TABLE t1,t2; 128create table t1 (i int, t time not null, vt time(4) as (t) virtual); 129create trigger trg before update on t1 for each row set @a = 1; 130insert ignore into t1 (i) values (1); 131Warnings: 132Warning 1364 Field 't' doesn't have a default value 133drop table t1; 134# 135# Examine the number of times triggers are recalculated for updates 136# 137SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 138CREATE TABLE t1 ( 139a INTEGER UNSIGNED NULL DEFAULT NULL, 140b CHAR(10) NULL DEFAULT NULL, 141c blob NULL DEFAULT NULL, 142blob_a blob GENERATED ALWAYS AS (last_value(@a:=@a+1,a)) VIRTUAL, 143blob_b blob GENERATED ALWAYS AS (last_value(@b:=@b+1,b)) VIRTUAL, 144blob_c blob GENERATED ALWAYS AS (last_value(@c:=@c+1,c)) VIRTUAL 145); 146CREATE TRIGGER t1_ins 147BEFORE INSERT 148ON t1 149FOR EACH ROW 150BEGIN 151IF NEW.b IS NULL THEN 152SET NEW.b="generated before insert"; 153END IF; 154END | 155CREATE TRIGGER t1_update 156BEFORE UPDATE 157ON t1 158FOR EACH ROW 159BEGIN 160IF NEW.b IS NULL or NEW.c IS NULL THEN 161SET NEW.b="generated before update"; 162SET NEW.c="generated before update"; 163END IF; 164END | 165# Inserts 166set @a=0,@b=0,@c=0; 167insert into t1 (a) values(1); 168insert into t1 (a,b) values(2, "*2*"); 169insert into t1 (a,b,c) values(3, "*3*", "**3**"); 170insert into t1 (a,c) values(4, "**4**"); 171select * from t1; 172a b c blob_a blob_b blob_c 1731 generated NULL 1 generated NULL 1742 *2* NULL 2 *2* NULL 1753 *3* **3** 3 *3* **3** 1764 generated **4** 4 generated **4** 177select @a,@b,@c; 178@a @b @c 1794 4 4 180select * from t1; 181a b c blob_a blob_b blob_c 1821 generated NULL 1 generated NULL 1832 *2* NULL 2 *2* NULL 1843 *3* **3** 3 *3* **3** 1854 generated **4** 4 generated **4** 186select @a,@b,@c; 187@a @b @c 1888 8 8 189select a,b,c from t1; 190a b c 1911 generated NULL 1922 *2* NULL 1933 *3* **3** 1944 generated **4** 195select @a,@b,@c; 196@a @b @c 1978 8 8 198select a,b,c,blob_a from t1; 199a b c blob_a 2001 generated NULL 1 2012 *2* NULL 2 2023 *3* **3** 3 2034 generated **4** 4 204select @a,@b,@c; 205@a @b @c 20612 8 8 207# updates 208set @a=0,@b=0,@c=0; 209update t1 set a=a+100 where a=1; 210update t1 set a=a+100, b="*102*" where a=2; 211update t1 set a=a+100, b=NULL where a=3; 212update t1 set a=a+100, b="invisible", c=NULL where a=4; 213select @a,@b,@c; 214@a @b @c 2150 0 0 216select * from t1; 217a b c blob_a blob_b blob_c 218101 generated generated before update 101 generated generated before update 219102 generated generated before update 102 generated generated before update 220103 generated generated before update 103 generated generated before update 221104 generated generated before update 104 generated generated before update 222drop trigger t1_ins; 223drop trigger t1_update; 224drop table t1; 225SET sql_mode = DEFAULT; 226# 227# Same test, but with virtual keys 228# 229SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 230CREATE TABLE t1 ( 231a INTEGER UNSIGNED NULL DEFAULT NULL, 232b CHAR(10) NULL DEFAULT NULL, 233c blob NULL DEFAULT NULL, 234blob_a blob GENERATED ALWAYS AS (a) VIRTUAL, 235blob_b blob GENERATED ALWAYS AS (RTRIM(b)) VIRTUAL, 236blob_c blob GENERATED ALWAYS AS (c) VIRTUAL, 237key (a), 238key (blob_a(10)), 239key (blob_b(10)), 240key (blob_c(10)) 241); 242CREATE TRIGGER t1_ins 243BEFORE INSERT 244ON t1 245FOR EACH ROW 246BEGIN 247IF NEW.b IS NULL THEN 248SET NEW.b="generated before insert"; 249END IF; 250END | 251CREATE TRIGGER t1_update 252BEFORE UPDATE 253ON t1 254FOR EACH ROW 255BEGIN 256IF NEW.b IS NULL or NEW.c IS NULL THEN 257SET NEW.b="generated before update"; 258SET NEW.c="generated before update"; 259END IF; 260END | 261# Inserts 262insert into t1 (a) values(1); 263insert into t1 (a,b) values(2, "*2*"); 264insert into t1 (a,b,c) values(3, "*3*", "**3**"); 265insert into t1 (a,c) values(4, "**4**"); 266select * from t1; 267a b c blob_a blob_b blob_c 2681 generated NULL 1 generated NULL 2692 *2* NULL 2 *2* NULL 2703 *3* **3** 3 *3* **3** 2714 generated **4** 4 generated **4** 272select @a,@b,@c; 273@a @b @c 2744 4 4 275select * from t1; 276a b c blob_a blob_b blob_c 2771 generated NULL 1 generated NULL 2782 *2* NULL 2 *2* NULL 2793 *3* **3** 3 *3* **3** 2804 generated **4** 4 generated **4** 281select @a,@b,@c; 282@a @b @c 2834 4 4 284select a,b,c from t1; 285a b c 2861 generated NULL 2872 *2* NULL 2883 *3* **3** 2894 generated **4** 290select @a,@b,@c; 291@a @b @c 2924 4 4 293select a,b,c,blob_a from t1; 294a b c blob_a 2951 generated NULL 1 2962 *2* NULL 2 2973 *3* **3** 3 2984 generated **4** 4 299select @a,@b,@c; 300@a @b @c 3014 4 4 302# updates 303update t1 set a=a+100 where a=1; 304update t1 set a=a+100, b="*102*" where a=2; 305update t1 set a=a+100, b=NULL where a=3; 306update t1 set a=a+100, b="invisible", c=NULL where a=4; 307select * from t1; 308a b c blob_a blob_b blob_c 309101 generated generated before update 101 generated generated before update 310102 generated generated before update 102 generated generated before update 311103 generated generated before update 103 generated generated before update 312104 generated generated before update 104 generated generated before update 313drop trigger t1_ins; 314drop trigger t1_update; 315drop table t1; 316SET sql_mode = DEFAULT; 317