diff options
Diffstat (limited to 'mysql-test/suite/compat/oracle/r/trigger.result')
| -rw-r--r-- | mysql-test/suite/compat/oracle/r/trigger.result | 100 |
1 files changed, 100 insertions, 0 deletions
diff --git a/mysql-test/suite/compat/oracle/r/trigger.result b/mysql-test/suite/compat/oracle/r/trigger.result new file mode 100644 index 00000000..7c24c78b --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/trigger.result @@ -0,0 +1,100 @@ +set sql_mode=ORACLE; +:NEW.a := 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':NEW.a := 1' at line 1 +:OLD.a := 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':OLD.a := 1' at line 1 +:OLa.a := 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':OLa.a := 1' at line 1 +SELECT :NEW.a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'a' at line 1 +SELECT :OLD.a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'a' at line 1 +SELECT :OLa.a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'a' at line 1 +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW NEW.a:= 10; +INSERT INTO t1 VALUES (); +SELECT * FROM t1; +a +10 +DROP TRIGGER tr1; +DROP TABLE t1; +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW :NEW.a:= 10; +INSERT INTO t1 VALUES (); +SELECT * FROM t1; +a +10 +DROP TRIGGER tr1; +DROP TABLE t1; +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW +BEGIN +IF :NEW.a IS NULL +THEN +:NEW.a:= 10; +END IF; +END; +/ +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +a +10 +DROP TRIGGER tr1; +DROP TABLE t1; +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW +BEGIN +IF :OLD.a IS NULL +THEN +:NEW.a:= 10; +END IF; +END; +/ +INSERT INTO t1 VALUES (NULL); +UPDATE t1 SET a=NULL; +SELECT * FROM t1; +a +10 +DROP TRIGGER tr1; +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT, c INT); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 +FOR EACH ROW +DECLARE +cnt INT := 0; +BEGIN +IF :NEW.a IS NULL THEN cnt:=cnt+1; END IF; +IF :NEW.b IS NULL THEN cnt:=cnt+1; END IF; +IF :NEW.c IS NULL THEN :NEW.c:=cnt; END IF; +END; +/ +INSERT INTO t1 VALUES (); +INSERT INTO t1 VALUES (1, NULL, NULL); +INSERT INTO t1 VALUES (NULL, 1, NULL); +INSERT INTO t1 VALUES (1, 1, NULL); +SELECT * FROM t1; +a b c +NULL NULL 2 +1 NULL 1 +NULL 1 1 +1 1 0 +DROP TABLE t1; +# +# MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations +# +CREATE TABLE t1 (a INT, b INT, total INT); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 +FOR EACH ROW +DECLARE +va t1.a%TYPE:= :NEW.a; +vb t1.b%TYPE:= :NEW.b; +BEGIN +:NEW.total:= va + vb; +END; +$$ +INSERT INTO t1 (a,b) VALUES (10, 20); +SELECT * FROM t1; +a b total +10 20 30 +DROP TABLE t1; |