Debian Codegraph @

mysql-test/suite/compat/oracle/t/trigger.test

raw
1 set sql_mode=ORACLE;
2
3 --error ER_PARSE_ERROR
4 :NEW.a := 1;
5 --error ER_PARSE_ERROR
6 :OLD.a := 1;
7 --error ER_PARSE_ERROR
8 :OLa.a := 1;
9
10 --error ER_PARSE_ERROR
11 SELECT :NEW.a;
12 --error ER_PARSE_ERROR
13 SELECT :OLD.a;
14 --error ER_PARSE_ERROR
15 SELECT :OLa.a;
16
17 CREATE TABLE t1 (a INT);
18 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW NEW.a:= 10;
19 INSERT INTO t1 VALUES ();
20 SELECT * FROM t1;
21 DROP TRIGGER tr1;
22 DROP TABLE t1;
23
24
25 CREATE TABLE t1 (a INT);
26 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW :NEW.a:= 10;
27 INSERT INTO t1 VALUES ();
28 SELECT * FROM t1;
29 DROP TRIGGER tr1;
30 DROP TABLE t1;
31
32
33 CREATE TABLE t1 (a INT);
34 DELIMITER /;
35 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW
36 BEGIN
37 IF :NEW.a IS NULL
38 THEN
39 :NEW.a:= 10;
40 END IF;
41 END;
42 /
43 DELIMITER ;/
44 INSERT INTO t1 VALUES (NULL);
45 SELECT * FROM t1;
46 DROP TRIGGER tr1;
47 DROP TABLE t1;
48
49 CREATE TABLE t1 (a INT);
50 DELIMITER /;
51 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW
52 BEGIN
53 IF :OLD.a IS NULL
54 THEN
55 :NEW.a:= 10;
56 END IF;
57 END;
58 /
59 DELIMITER ;/
60 INSERT INTO t1 VALUES (NULL);
61 UPDATE t1 SET a=NULL;
62 SELECT * FROM t1;
63 DROP TRIGGER tr1;
64 DROP TABLE t1;
65
66
67
68 CREATE TABLE t1 (a INT, b INT, c INT);
69 DELIMITER /;
70 CREATE TRIGGER tr1 BEFORE INSERT ON t1
71 FOR EACH ROW
72 DECLARE
73 cnt INT := 0;
74 BEGIN
75 IF :NEW.a IS NULL THEN cnt:=cnt+1; END IF;
76 IF :NEW.b IS NULL THEN cnt:=cnt+1; END IF;
77 IF :NEW.c IS NULL THEN :NEW.c:=cnt; END IF;
78 END;
79 /
80 DELIMITER ;/
81 INSERT INTO t1 VALUES ();
82 INSERT INTO t1 VALUES (1, NULL, NULL);
83 INSERT INTO t1 VALUES (NULL, 1, NULL);
84 INSERT INTO t1 VALUES (1, 1, NULL);
85 SELECT * FROM t1;
86 DROP TABLE t1;
87
88 --echo #
89 --echo # MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
90 --echo #
91
92 CREATE TABLE t1 (a INT, b INT, total INT);
93 DELIMITER $$;
94 CREATE TRIGGER tr1 BEFORE INSERT ON t1
95 FOR EACH ROW
96 DECLARE
97 va t1.a%TYPE:= :NEW.a;
98 vb t1.b%TYPE:= :NEW.b;
99 BEGIN
100 :NEW.total:= va + vb;
101 END;
102 $$
103 DELIMITER ;$$
104 INSERT INTO t1 (a,b) VALUES (10, 20);
105 SELECT * FROM t1;
106 DROP TABLE t1;