summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/vcol/inc/vcol_trigger_sp.inc')
-rw-r--r--mysql-test/suite/vcol/inc/vcol_trigger_sp.inc296
1 files changed, 296 insertions, 0 deletions
diff --git a/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc b/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc
new file mode 100644
index 00000000..c7dedcdd
--- /dev/null
+++ b/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc
@@ -0,0 +1,296 @@
+################################################################################
+# inc/vcol_trigger_sp.inc #
+# #
+# Purpose: #
+# Testing triggers, stored procedures and functions #
+# defined on tables with virtual columns. #
+# #
+# #
+# #
+#------------------------------------------------------------------------------#
+# Original Author: Andrey Zhakov #
+# Original Date: 2008-09-04 #
+# Change Author: Oleksandr Byelkin (Monty program Ab)
+# Date: 2009-03-24
+# Change: Syntax changed
+################################################################################
+
+create table t1 (a int,
+ b int as (a/10),
+ c int as (a/10) persistent);
+
+create table t2 (a timestamp);
+
+delimiter |;
+
+create trigger trg1 before insert on t1 for each row
+begin
+ if (new.b < 10) then
+ set new.a:= 100;
+ set new.b:= 9;
+ set new.c:= 9;
+ end if;
+
+ if (new.c > 50) then
+ set new.a:= 500;
+ end if;
+end|
+
+create trigger trg2 after insert on t1 for each row
+begin
+ if (new.b >= 60) then
+ insert into t2 values (now());
+ end if;
+end|
+
+create function f1()
+returns int
+begin
+ declare sum1 int default '0';
+ declare cur1 cursor for select sum(b) from t1;
+ open cur1;
+ fetch cur1 into sum1;
+ close cur1;
+ return sum1;
+end|
+
+delimiter ;|
+
+set sql_warnings = 1;
+
+insert into t1 (a) values (200);
+select * from t1;
+select * from t2;
+
+insert into t1 (a) values (10);
+select * from t1;
+select * from t2;
+
+insert into t1 (a) values (600);
+select * from t1;
+--replace_column 1 <timestamp>
+select * from t2;
+
+select f1();
+
+set sql_warnings = 0;
+
+drop trigger trg1;
+drop trigger trg2;
+drop table t2;
+
+delimiter |;
+
+create procedure p1()
+begin
+ declare i int default '0';
+ create table t2 like t1;
+ insert into t2 (a) values (100), (200);
+ begin
+ declare cur1 cursor for select sum(c) from t2;
+ open cur1;
+ fetch cur1 into i;
+ close cur1;
+ if (i=30) then
+ insert into t1 values (300,default,default);
+ end if;
+ end;
+end|
+
+delimiter ;|
+
+delete from t1;
+
+call p1();
+
+select * from t2;
+select * from t1;
+
+drop table t1,t2;
+drop procedure p1;
+
+--echo #
+--echo # MDEV-3845 values of virtual columns are not computed for triggers
+--echo #
+
+CREATE TABLE t1 (
+ a INTEGER UNSIGNED NULL DEFAULT NULL,
+ b INTEGER UNSIGNED GENERATED ALWAYS AS (a) VIRTUAL
+);
+
+CREATE TABLE t2 (c INTEGER UNSIGNED NOT NULL);
+
+DELIMITER |;
+
+CREATE TRIGGER t1_ins_aft
+ AFTER INSERT
+ ON t1
+ FOR EACH ROW
+BEGIN
+ INSERT INTO t2 (c) VALUES (NEW.b);
+END |
+
+CREATE TRIGGER t1_del_bef
+ BEFORE DELETE
+ ON t1
+ FOR EACH ROW
+BEGIN
+ INSERT INTO t2 (c) VALUES (OLD.b);
+END |
+
+DELIMITER ;|
+
+INSERT INTO t1 (a) VALUES (1), (2), (3);
+SELECT * FROM t2;
+DELETE FROM t1;
+SELECT * FROM t2;
+
+DROP TRIGGER t1_ins_aft;
+DROP TRIGGER t1_del_bef;
+DROP TABLE t1,t2;
+
+#
+# MDEV-11706 Assertion `is_stat_field || !table || (!table->write_set || bitmap_is_set(table->write_set, field_index) || (table->vcol_set && bitmap_is_set(table->vcol_set, field_index)))' failed in Field_time::store_TIME_with_warning
+#
+create table t1 (i int, t time not null, vt time(4) as (t) virtual);
+create trigger trg before update on t1 for each row set @a = 1;
+insert ignore into t1 (i) values (1);
+drop table t1;
+
+--echo #
+--echo # Examine the number of times triggers are recalculated for updates
+--echo #
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
+CREATE TABLE t1 (
+ a INTEGER UNSIGNED NULL DEFAULT NULL,
+ b CHAR(10) NULL DEFAULT NULL,
+ c blob NULL DEFAULT NULL,
+ blob_a blob GENERATED ALWAYS AS (last_value(@a:=@a+1,a)) VIRTUAL,
+ blob_b blob GENERATED ALWAYS AS (last_value(@b:=@b+1,b)) VIRTUAL,
+ blob_c blob GENERATED ALWAYS AS (last_value(@c:=@c+1,c)) VIRTUAL
+);
+
+DELIMITER |;
+CREATE TRIGGER t1_ins
+ BEFORE INSERT
+ ON t1
+ FOR EACH ROW
+BEGIN
+ IF NEW.b IS NULL THEN
+ SET NEW.b="generated before insert";
+ END IF;
+END |
+
+CREATE TRIGGER t1_update
+ BEFORE UPDATE
+ ON t1
+ FOR EACH ROW
+BEGIN
+ IF NEW.b IS NULL or NEW.c IS NULL THEN
+ SET NEW.b="generated before update";
+ SET NEW.c="generated before update";
+ END IF;
+END |
+
+DELIMITER ;|
+
+--echo # Inserts
+set @a=0,@b=0,@c=0;
+
+--disable_ps2_protocol
+insert into t1 (a) values(1);
+insert into t1 (a,b) values(2, "*2*");
+insert into t1 (a,b,c) values(3, "*3*", "**3**");
+insert into t1 (a,c) values(4, "**4**");
+select * from t1;
+select @a,@b,@c;
+select * from t1;
+select @a,@b,@c;
+select a,b,c from t1;
+select @a,@b,@c;
+select a,b,c,blob_a from t1;
+select @a,@b,@c;
+
+--echo # updates
+set @a=0,@b=0,@c=0;
+
+update t1 set a=a+100 where a=1;
+update t1 set a=a+100, b="*102*" where a=2;
+update t1 set a=a+100, b=NULL where a=3;
+update t1 set a=a+100, b="invisible", c=NULL where a=4;
+select @a,@b,@c;
+select * from t1;
+--enable_ps2_protocol
+
+drop trigger t1_ins;
+drop trigger t1_update;
+drop table t1;
+
+SET sql_mode = DEFAULT;
+
+--echo #
+--echo # Same test, but with virtual keys
+--echo #
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
+CREATE TABLE t1 (
+ a INTEGER UNSIGNED NULL DEFAULT NULL,
+ b CHAR(10) NULL DEFAULT NULL,
+ c blob NULL DEFAULT NULL,
+ blob_a blob GENERATED ALWAYS AS (a) VIRTUAL,
+ blob_b blob GENERATED ALWAYS AS (RTRIM(b)) VIRTUAL,
+ blob_c blob GENERATED ALWAYS AS (c) VIRTUAL,
+ key (a),
+ key (blob_a(10)),
+ key (blob_b(10)),
+ key (blob_c(10))
+);
+
+DELIMITER |;
+CREATE TRIGGER t1_ins
+ BEFORE INSERT
+ ON t1
+ FOR EACH ROW
+BEGIN
+ IF NEW.b IS NULL THEN
+ SET NEW.b="generated before insert";
+ END IF;
+END |
+
+CREATE TRIGGER t1_update
+ BEFORE UPDATE
+ ON t1
+ FOR EACH ROW
+BEGIN
+ IF NEW.b IS NULL or NEW.c IS NULL THEN
+ SET NEW.b="generated before update";
+ SET NEW.c="generated before update";
+ END IF;
+END |
+
+DELIMITER ;|
+
+--echo # Inserts
+insert into t1 (a) values(1);
+insert into t1 (a,b) values(2, "*2*");
+insert into t1 (a,b,c) values(3, "*3*", "**3**");
+insert into t1 (a,c) values(4, "**4**");
+select * from t1;
+select @a,@b,@c;
+select * from t1;
+select @a,@b,@c;
+select a,b,c from t1;
+select @a,@b,@c;
+select a,b,c,blob_a from t1;
+select @a,@b,@c;
+
+--echo # updates
+update t1 set a=a+100 where a=1;
+update t1 set a=a+100, b="*102*" where a=2;
+update t1 set a=a+100, b=NULL where a=3;
+update t1 set a=a+100, b="invisible", c=NULL where a=4;
+select * from t1;
+
+drop trigger t1_ins;
+drop trigger t1_update;
+drop table t1;
+SET sql_mode = DEFAULT;
generated by cgit v1.2.3 (git 2.39.1) at 2025-01-11 22:36:12 +0000