SAVEPOINT in a stored function should be forbidden
CREATE FUNCTION f1 () RETURNS INT BEGIN
SAVEPOINT s;
RETURN 1;
END|
SELECT f1();
f1()
1
DROP FUNCTION f1;
ROLLBACK TO SAVEPOINT in a stored function should be forbidden
CREATE FUNCTION f2 () RETURNS INT BEGIN
ROLLBACK TO SAVEPOINT s;
RETURN 1;
END|
BEGIN;
SAVEPOINT s;
SELECT f2();
ERROR 42000: SAVEPOINT s does not exist
COMMIT;
DROP FUNCTION f2;
BEGIN;
SAVEPOINT S;
ROLLBACK TO SAVEPOINT S;
COMMIT;
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (a INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
INSERT INTO t1 values (110), (111), (112), (113), (114);
Direct SAVEPOINT in a trigger should be forbidden
CREATE TRIGGER i1_t1 BEFORE INSERT ON t1 FOR EACH ROW SAVEPOINT s;
INSERT INTO t1 VALUES (1);
DROP TRIGGER i1_t1;
CREATE TRIGGER i2_t1 AFTER INSERT ON t1 FOR EACH ROW SAVEPOINT s;
INSERT INTO t1 VALUES (2);
DROP TRIGGER i2_t1;
INSERT INTO t1 VALUES (3);
CREATE TRIGGER u1_t1 BEFORE UPDATE ON t1 FOR EACH ROW SAVEPOINT s;
UPDATE t1 SET a=4 WHERE a=3;
DROP TRIGGER u1_t1;
CREATE TRIGGER u2_t1 AFTER UPDATE ON t1 FOR EACH ROW SAVEPOINT s;
UPDATE t1 SET a=4 WHERE a=3;
DROP TRIGGER u2_t1;
CREATE TRIGGER d1_t1 BEFORE DELETE ON t1 FOR EACH ROW SAVEPOINT s;
DELETE FROM t1;
DROP TRIGGER d1_t1;
CREATE TRIGGER d1_t1 AFTER DELETE ON t1 FOR EACH ROW SAVEPOINT s;
DELETE FROM t1;
DROP TRIGGER d1_t1;
SAVEPOINT in a compound statement in a trigger should be forbidden
CREATE TRIGGER i3_t1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
SAVEPOINT s;
END|
INSERT INTO t1 VALUES (5);
DROP TRIGGER i3_t1;
SAVEPOINT in a PS call in a trigger should be forbidden
CREATE TRIGGER i4_t1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
PREPARE set_savepoint FROM "SAVEPOINT s";
EXECUTE set_savepoint;
DEALLOCATE PREPARE set_savepoint;
END|
ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
SAVEPOINT in SP called from a trigger should be forbidden
CREATE PROCEDURE p1() BEGIN
SAVEPOINT s;
END|
CREATE TRIGGER i5_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p1;
INSERT INTO t1 VALUES (6);
DROP TRIGGER i5_t1;
SAVEPOINT in a SP called from a PS called from a trigger be forbidden
PREPARE call_p1 FROM "CALL p1";
CREATE TRIGGER i6_t1 BEFORE INSERT ON t1 FOR EACH ROW EXECUTE call_p1;
ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
SAVEPOINT in a function called from a trigger should be forbidden
CREATE FUNCTION f1 () RETURNS INT BEGIN
SAVEPOINT s;
RETURN 1;
END|
CREATE TRIGGER i7_t1 BEFORE INSERT ON t1 FOR EACH ROW SET @foo = f1();
INSERT INTO t1 VALUES (7);
DROP TRIGGER i7_t1;
SAVEPOINT in a SP called from a SP called from a trigger should be forbidden
CREATE PROCEDURE p2() BEGIN
CALL p1();
END|
CREATE TRIGGER i8_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p2;
INSERT INTO t1 VALUES (8);
DROP TRIGGER i8_t1;
SAVEPOINT in a SP called from a trigger called from a SP should be forbidden
CREATE TRIGGER i9_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p1;
CREATE PROCEDURE p3() BEGIN
INSERT INTO t1 VALUES (9);
END|
CALL p3();
DROP TRIGGER i9_t1;
ROLLBACK TO SAVEPOINT in trigger as a trivial statement should be forbidden
CREATE TRIGGER i4_t1 BEFORE INSERT ON t1 FOR EACH ROW ROLLBACK TO SAVEPOINT s;
BEGIN;
SAVEPOINT s;
INSERT INTO t1 VALUES (5);
ERROR 42000: SAVEPOINT s does not exist
COMMIT;
DROP TRIGGER i4_t1;
ROLLBACK TO SAVEPOINT in a trigger in a SP call should be forbidden
CREATE PROCEDURE p4() BEGIN
ROLLBACK TO SAVEPOINT s;
END|
CREATE TRIGGER i5_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p4;
BEGIN;
SAVEPOINT s;
INSERT INTO t1 VALUES (6);
ERROR 42000: SAVEPOINT s does not exist
COMMIT;
DROP TRIGGER i5_t1;
SAVEPOINT in a SP next to a trigger should work
CREATE TRIGGER i6_t1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a = NEW.a + 1;
CREATE PROCEDURE p5() BEGIN
SAVEPOINT s;
INSERT INTO t1 VALUES (10);
ROLLBACK TO SAVEPOINT s;
END|
BEGIN;
CALL p5();
COMMIT;
DROP TRIGGER i6_t1;
create trigger t1 before insert on t1 for each row
begin
insert into t2 values (NULL);
end|
INSERT INTO t1 VALUES (201), (202), (203);
SELECT * FROM t1;
a
5
6
7
8
9
201
202
203
SELECT COUNT(*) FROM t2;
COUNT(*)
3
SELECT * FROM t1;
a
5
6
7
8
9
201
202
203
SELECT COUNT(*) FROM t2;
COUNT(*)
3
DEALLOCATE PREPARE call_p1;
DROP TABLE t1, t2;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
DROP PROCEDURE p4;
DROP PROCEDURE p5;
DROP FUNCTION f1;