mirror of
https://github.com/MariaDB/server.git
synced 2025-02-22 13:23:07 +01:00
edd52b7fc7
Upon rebase, some error codes changed order so record tests to update accordingly. Merge with trigger changes from MDEV-34724.
414 lines
11 KiB
Text
414 lines
11 KiB
Text
# Test case 1: check that a row being inserted
|
|
# can be filtered out by means running of a statement
|
|
# SIGNAL SQLSTATE '02TRG' from the trigger body
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF NEW.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
# Execution of the following INSERT statement produces warnings
|
|
# for the row (1) filtered out by the statement SIGNAL SQLSTATE '02TRG'
|
|
# invoked from the trigger
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
Warnings:
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
# Expected output is the rows (2), (3)
|
|
SELECT * FROM t1;
|
|
a
|
|
2
|
|
3
|
|
# Clean up
|
|
TRUNCATE TABLE t1;
|
|
# Test case 2: run the statement INSERT SELECT and check
|
|
# that the rows (1) is filtered out by the trigger `t1_bi`
|
|
CREATE TABLE t2 (a INT);
|
|
INSERT INTO t2 VALUES (1), (2), (1), (3), (5);
|
|
# Execution of the following INSERT ... SELECT statement produces warnings
|
|
# for the row (1) filtered out by the statement SIGNAL SQLSTATE '02TRG'
|
|
# invoked from the trigger
|
|
INSERT INTO t1 SELECT * FROM t2;
|
|
Warnings:
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
# Expected output is the rows (2), (3), (5)
|
|
SELECT * FROM t1;
|
|
a
|
|
2
|
|
3
|
|
5
|
|
# Clean up
|
|
DROP TABLE t1, t2;
|
|
# Test case 3: check for cooperation of the feature
|
|
# 'skipping rows from a trigger' and execution of
|
|
# the statement LOAD
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF NEW.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
# Test case 3.1: check for LOAD DATA INFILE
|
|
# Prepare a file with dump of the table `t2` and
|
|
# then use it for loading data into the table `t1`
|
|
CREATE TABLE t2 (a INT);
|
|
INSERT INTO t2 VALUES (1), (2), (1), (3), (5);
|
|
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t2' FROM t2;
|
|
# Execution of the following LOAD DATA INFILE statement produces warnings
|
|
# for the rows (1) filtered out by the statement SIGNAL SQLSTATE '02TRG'
|
|
# invoked from the trigger
|
|
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t2' INTO TABLE t1;
|
|
Warnings:
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
# Querying of the table `t1` should return three rows: (2), (3), (5)
|
|
SELECT * FROM t1;
|
|
a
|
|
2
|
|
3
|
|
5
|
|
# Clean up
|
|
TRUNCATE TABLE t1;
|
|
# Test case 3.2: check for LOAD XML INFILE
|
|
# The same test with loading data from a file in presence of
|
|
# a BEFORE INSERT trigger, but in this case the data to be loaded
|
|
# is in xml format.
|
|
# Prepare a file with dump of the table `t2` in xml format and
|
|
# then use it for loading data into the table `t1`
|
|
# Execution of the following LOAD XML INFILE statement produces warnings
|
|
# for the rows (1) filtered out by the statement SIGNAL SQLSTATE '02TRG'
|
|
# invoked from the trigger
|
|
LOAD XML INFILE 'MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' INTO TABLE t1 ROWS IDENTIFIED BY '<row>';
|
|
Warnings:
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
SELECT * FROM t1;
|
|
a
|
|
2
|
|
3
|
|
5
|
|
# Clean up
|
|
DROP TABLE t1, t2;
|
|
# Test case 4: check that a row being deleted
|
|
# can be filtered out by means running of a statement
|
|
# SIGNAL SQLSTATE '02TRG' from the trigger body
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1), (2), (3), (1);
|
|
CREATE TRIGGER t1_bi BEFORE DELETE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF OLD.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
# No rows deleted in result of running the following statement
|
|
# Execution of the following DELETE statement produces warnings
|
|
# for the row (1) filtered out by the statement SIGNAL SQLSTATE '02TRG'
|
|
# invoked from the trigger
|
|
DELETE FROM t1 WHERE a = 1;
|
|
Warnings:
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
# Show that the rows satisfying the condition a = 1 are retained
|
|
# in the table. Expected output is the rows (1), (2), (3), (1)
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
1
|
|
# Shows that rows with a column value different
|
|
# from '1' are deleted successfully
|
|
DELETE FROM t1 WHERE a = 2;
|
|
# Expected output is the rows (1), (3), (1)
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
3
|
|
1
|
|
# Check that the DELETE statement without condition takes into
|
|
# account the fact that some of rows should be skipped.
|
|
DELETE FROM t1;
|
|
Warnings:
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
# Expected output is the rows (1), (1) since they are explicilty
|
|
# skipped by the trigger logic
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
1
|
|
# Clean up
|
|
DROP TABLE t1;
|
|
# Test case 5: check that AFTER INSERT/UPDATE/DELETE trigger is not fired
|
|
# in case a row skipped by corresponding BEFORE trigger
|
|
#
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (a INT);
|
|
# Test case 5.1: check for the pair BEFORE INSERT/AFTER INSERT
|
|
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF NEW.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO t2 VALUES (NEW.a);
|
|
END
|
|
$
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
Warnings:
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
SELECT * FROM t1;
|
|
a
|
|
2
|
|
3
|
|
SELECT * FROM t2;
|
|
a
|
|
2
|
|
3
|
|
# Clean up
|
|
DROP TABLE t1, t2;
|
|
# Test case 5.2: check for the pair BEFORE DELETE/AFTER DELETE
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (a INT);
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF OLD.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO t2 VALUES (OLD.a);
|
|
END
|
|
$
|
|
DELETE FROM t1;
|
|
Warnings:
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bd
|
|
# The row (1) is skipped by implementation of the trigger t1_bd,
|
|
# therefore the row (1) isn't inserted into the table t2 since
|
|
# the trigger t1_ad isn't fired for the row (1)
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
SELECT * FROM t2;
|
|
a
|
|
2
|
|
3
|
|
# Clean up
|
|
DROP TABLE t1, t2;
|
|
# Test case 5.3: check for the pair BEFORE UPDATE/AFTER UPDATE
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (old_a INT, new_a INT);
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF OLD.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO t2 VALUES (OLD.a, NEW.a);
|
|
END
|
|
$
|
|
# The following statement UPDATE doens't modify the row (1)
|
|
# since this row is explicitly ignored by implementation of
|
|
# trigger t1_bu, therefore the trigger t1_au is not fired
|
|
# for this row and the row (1, 11) not inserted into the table t2
|
|
UPDATE t1 SET a = a + 10;
|
|
Warnings:
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bu
|
|
# Expected output of the following statement SELECT is (1), (12), (13)
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
12
|
|
13
|
|
# Expected output of query from the table t2 is (2, 12), (3, 13)
|
|
SELECT * FROM t2;
|
|
old_a new_a
|
|
2 12
|
|
3 13
|
|
# Clean up
|
|
DROP TABLE t1, t2;
|
|
# Test case 6: check cooperation of UPDATE with the
|
|
# 'skipping a row from a trigger' feature
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1), (2), (1), (3), (5);
|
|
CREATE TRIGGER t1_bd BEFORE UPDATE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF OLD.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
# Check for update with condition
|
|
UPDATE t1 SET a = 1000 WHERE a = 1;
|
|
Warnings:
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bd
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bd
|
|
# Expected result is the rows (1), (2), (1), (3), (5)
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
2
|
|
1
|
|
3
|
|
5
|
|
# Check for unconditional update
|
|
UPDATE t1 SET a = a + 100;
|
|
Warnings:
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bd
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bd
|
|
# Expected result is the rows (1), (102), (1), (103), (105)
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
102
|
|
1
|
|
103
|
|
105
|
|
# Multi-update
|
|
TRUNCATE TABLE t1;
|
|
INSERT INTO t1 VALUES (1), (2), (1), (3), (5);
|
|
CREATE TABLE t2 (a INT);
|
|
INSERT INTO t2 VALUES (1), (3);
|
|
# In multi-update the trigger skips an update of
|
|
# the first table only (the one that has an associated trigger),
|
|
# the second table (without a trigger) is still updated
|
|
UPDATE t1, t2 SET t1.a = t1.a + 300, t2.a = t2.a + 300 WHERE t1.a = t2.a;
|
|
Warnings:
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bd
|
|
Error 4208 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bd
|
|
# Expected results is the rows (1), (2), (1), (303), (5)
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
2
|
|
1
|
|
303
|
|
5
|
|
# Expected results is the rows (301), (303)
|
|
SELECT * FROM t2;
|
|
a
|
|
301
|
|
303
|
|
# Clean up
|
|
DROP TABLE t1, t2;
|
|
# Test case 7: check that MESSAGE_TEXT and MYSQL_ERRNOR still
|
|
# can be assigned by a user for SQLSTATE '02TRG'
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF NEW.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG' SET MYSQL_ERRNO=1, MESSAGE_TEXT='This value is intentionally ignored';
|
|
END IF;
|
|
END
|
|
$
|
|
# Execution of the following INSERT statement produces warnings
|
|
# for the row (1) filtered out by the statement SIGNAL SQLSTATE '02TRG'
|
|
# invoked from the trigger. The errno has value 1 and the message is
|
|
# is the text message 'This value is intentionally ignored'
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
Warnings:
|
|
Error 1 This value is intentionally ignored
|
|
Note 4094 At line 4 in test.t1_bi
|
|
# Expected output is the rows (2), (3)
|
|
SELECT * FROM t1;
|
|
a
|
|
2
|
|
3
|
|
# Clean up
|
|
DROP TABLE t1;
|
|
# Test case 8: check that SQLSTATE '02TRG' doesn't have any special
|
|
# meaning in AFTER triggers
|
|
# Test case 8.1: check it for AFTER INSERT trigger
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF NEW.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
# There is no a handler for the signal raised from inside
|
|
# the trigger t1_ai, so the statement INSERT INTO fails
|
|
# with the error ER_SIGNAL_NOT_FOUND
|
|
INSERT INTO t1 VALUES (1);
|
|
ERROR 02TRG: Unhandled user-defined not found condition
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
# Clean up
|
|
DROP TABLE t1;
|
|
# Test case 8.2: check it for AFTER UPDATE trigger
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF OLD.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
INSERT INTO t1 VALUES (1);
|
|
# There is no a handler for the signal raised from inside
|
|
# the trigger t1_au, so the statement UPDATE fails
|
|
# with the error ER_SIGNAL_NOT_FOUND
|
|
UPDATE t1 SET a = 10;
|
|
ERROR 02TRG: Unhandled user-defined not found condition
|
|
SELECT * FROM t1;
|
|
a
|
|
10
|
|
# Clean up
|
|
DROP TABLE t1;
|
|
# Test case 8.3: check it for AFTER DELETE trigger
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1);
|
|
CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF OLD.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
# There is no a handler for the signal raised from inside
|
|
# the trigger t1_ad, so the statement DELETE fails
|
|
# with the error ER_SIGNAL_NOT_FOUND
|
|
DELETE FROM t1;
|
|
ERROR 02TRG: Unhandled user-defined not found condition
|
|
SELECT * FROM t1;
|
|
a
|
|
# Clean up
|
|
DROP TABLE t1;
|
|
# End of 11.8 tests
|