mirror of
https://github.com/MariaDB/server.git
synced 2026-01-28 22:39:08 +01:00
9057f741d7
MariaDB 11.8 and above allows to specify list of columns in the update trigger and such trigger is fired only when one of the specified columns is updated. The `INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS` table lists all such columns for a trigger. A column is shown only if the user has any non-SELECT privileges on the column specified in the trigger.
207 lines
5.7 KiB
Text
207 lines
5.7 KiB
Text
set sql_mode=ORACLE;
|
|
|
|
--error ER_PARSE_ERROR
|
|
:NEW.a := 1;
|
|
--error ER_PARSE_ERROR
|
|
:OLD.a := 1;
|
|
--error ER_PARSE_ERROR
|
|
:OLa.a := 1;
|
|
|
|
--error ER_PARSE_ERROR
|
|
SELECT :NEW.a;
|
|
--error ER_PARSE_ERROR
|
|
SELECT :OLD.a;
|
|
--error ER_PARSE_ERROR
|
|
SELECT :OLa.a;
|
|
|
|
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;
|
|
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;
|
|
DROP TRIGGER tr1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
DELIMITER /;
|
|
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF :NEW.a IS NULL
|
|
THEN
|
|
:NEW.a:= 10;
|
|
END IF;
|
|
END;
|
|
/
|
|
DELIMITER ;/
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT * FROM t1;
|
|
DROP TRIGGER tr1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
DELIMITER /;
|
|
CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF :OLD.a IS NULL
|
|
THEN
|
|
:NEW.a:= 10;
|
|
END IF;
|
|
END;
|
|
/
|
|
DELIMITER ;/
|
|
INSERT INTO t1 VALUES (NULL);
|
|
UPDATE t1 SET a=NULL;
|
|
SELECT * FROM t1;
|
|
DROP TRIGGER tr1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
|
|
CREATE TABLE t1 (a INT, b INT, c INT);
|
|
DELIMITER /;
|
|
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;
|
|
/
|
|
DELIMITER ;/
|
|
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;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT, b INT, total INT);
|
|
DELIMITER $$;
|
|
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;
|
|
$$
|
|
DELIMITER ;$$
|
|
INSERT INTO t1 (a,b) VALUES (10, 20);
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-36996: Implement INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS table
|
|
--echo # Tests for oralce mode
|
|
--echo #
|
|
|
|
CREATE TABLE t (a INT, b INT, c INT);
|
|
INSERT INTO t VALUES (1, 2, 3);
|
|
CREATE TABLE t1 (a_old INT, b_old INT, a_new INT, b_new INT);
|
|
CREATE TABLE t2 (a_old INT, b_old INT, a_new INT, b_new INT);
|
|
|
|
--echo # Test BEFORE UPDATE trigger
|
|
CREATE TRIGGER trigger_before_update BEFORE UPDATE OF a, b ON t FOR EACH ROW INSERT INTO t1 VALUES (OLD.a, OLD.b, NEW.a, NEW.b);
|
|
|
|
UPDATE t SET a = 10 WHERE a = 1;
|
|
SELECT * FROM t;
|
|
SELECT * FROM t1;
|
|
|
|
--replace_column 2 # 4 # 5 # 6 # 7 # 8 #
|
|
SHOW CREATE TRIGGER trigger_before_update;
|
|
SELECT * FROM INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS WHERE TRIGGER_NAME = 'trigger_before_update';
|
|
|
|
--echo # Test AFTER UPDATE trigger
|
|
CREATE TRIGGER trigger_after_update AFTER UPDATE OF a, b ON t FOR EACH ROW INSERT INTO t2 VALUES (OLD.a, OLD.b, NEW.a, NEW.b);
|
|
|
|
UPDATE t SET a = -10 WHERE a = 10;
|
|
SELECT * FROM t;
|
|
SELECT * FROM t2;
|
|
|
|
--replace_column 2 # 4 # 5 # 6 # 7 # 8 #
|
|
SHOW CREATE TRIGGER trigger_after_update;
|
|
SELECT * FROM INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS WHERE TRIGGER_NAME = 'trigger_after_update';
|
|
|
|
# Cleanup
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo # Multiple triggers on same table but on different columns
|
|
CREATE TABLE t1 (a_old INT, a_new INT);
|
|
CREATE TABLE t2 (b_old INT, b_new INT);
|
|
CREATE TABLE t3 (c_old INT, c_new INT);
|
|
|
|
CREATE TRIGGER t_bu1 BEFORE UPDATE OF a ON t FOR EACH ROW INSERT INTO t1 VALUES (OLD.a, NEW.a);
|
|
CREATE TRIGGER t_bu2 BEFORE UPDATE OF b ON t FOR EACH ROW INSERT INTO t2 VALUES (OLD.b, NEW.b);
|
|
CREATE TRIGGER t_au1 AFTER UPDATE OF c ON t FOR EACH ROW INSERT INTO t3 VALUES (OLD.c, NEW.c);
|
|
|
|
--replace_column 2 # 4 # 5 # 6 # 7 # 8 #
|
|
SHOW CREATE TRIGGER t_bu1;
|
|
--replace_column 2 # 4 # 5 # 6 # 7 # 8 #
|
|
SHOW CREATE TRIGGER t_bu2;
|
|
--replace_column 2 # 4 # 5 # 6 # 7 # 8 #
|
|
SHOW CREATE TRIGGER t_au1;
|
|
SELECT * FROM INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS WHERE TRIGGER_NAME LIKE 't\_%';
|
|
|
|
# Cleanup
|
|
DROP TABLE t1, t2, t3, t;
|
|
|
|
--echo # Tests with Multi-update
|
|
CREATE TABLE t (a INT, b INT, c INT);
|
|
INSERT INTO t VALUES (1, 2, -3), (2, 3, -4);
|
|
CREATE TABLE t1 (a INT, b INT);
|
|
CREATE TABLE t2 (a INT, b INT);
|
|
|
|
--echo # Test Multi-update with BEFORE_UPDATE trigger
|
|
CREATE TRIGGER trigger_before_update BEFORE UPDATE OF b ON t FOR EACH ROW INSERT INTO t1 VALUES (OLD.a, OLD.b);
|
|
UPDATE t, t1 SET t.c = t.c + 10 WHERE t.b = t1.a;
|
|
|
|
--replace_column 2 # 4 # 5 # 6 # 7 # 8 #
|
|
SHOW CREATE TRIGGER trigger_before_update;
|
|
SELECT * FROM INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS WHERE TRIGGER_NAME = 'trigger_before_update';
|
|
|
|
--echo # Test Multi-update with AFTER_UPDATE trigger
|
|
CREATE TRIGGER trigger_after_update AFTER UPDATE OF a ON t FOR EACH ROW INSERT INTO t2 VALUES (OLD.a, OLD.b);
|
|
|
|
--replace_column 2 # 4 # 5 # 6 # 7 # 8 #
|
|
SHOW CREATE TRIGGER trigger_after_update;
|
|
SELECT * FROM INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS WHERE TRIGGER_NAME = 'trigger_after_update';
|
|
|
|
# Cleanup
|
|
DROP TABLE t, t1, t2;
|
|
|
|
--echo # Test with normal trigger without list of columns. This should not list any rows in TRIGGERED_UPDATE_COLUMNS
|
|
CREATE TABLE t (a INT, b INT, c INT);
|
|
INSERT INTO t VALUES (1, 2, -3), (2, 3, -4);
|
|
CREATE TABLE t1 (a INT, b INT);
|
|
|
|
CREATE TRIGGER trigger_before_update BEFORE UPDATE ON t FOR EACH ROW INSERT INTO t1 VALUES (OLD.a, OLD.b);
|
|
--replace_column 2 # 4 # 5 # 6 # 7 # 8 #
|
|
SHOW CREATE TRIGGER trigger_before_update;
|
|
SELECT * FROM INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS WHERE TRIGGER_NAME = 'trigger_before_update';
|
|
|
|
--echo # Trigger on view is not supported in mariadb
|
|
CREATE VIEW vt AS SELECT * FROM t;
|
|
--error ER_WRONG_OBJECT
|
|
CREATE TRIGGER t_on_view BEFORE UPDATE OF a, b ON vt FOR EACH ROW INSERT INTO t1 VALUES (OLD.a, OLD.b);
|
|
SELECT * FROM INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS WHERE TRIGGER_NAME = 't_on_view';
|
|
|
|
# Cleanup
|
|
DROP VIEW IF EXISTS vt;
|
|
DROP TABLE t, t1;
|
|
|
|
--echo # End of 12.2 tests
|