mirror of
https://github.com/MariaDB/server.git
synced 2025-06-20 11:41:23 +02:00
4c956fa15b
Implementation of this task adds ability to raise the signal with SQLSTATE '02TRG' from a BEFORE INSERT/UPDATE/DELETE trigger and handles this signal as an indicator meaning 'to throw away the current row' on processing the INSERT/UPDATE/DELETE statement. The signal with SQLSTATE '02TRG' has special meaning only in case it is raised inside BEFORE triggers, for AFTER trigger's this value of SQLSTATE isn't treated in any special way. In according with SQL standard, the SQLSTATE class '02' means NO DATA and sql_errno for this class is set to value ER_SIGNAL_NOT_FOUND by current implementation of MariaDB server. Implementation of this task assigns the value ER_SIGNAL_SKIP_ROW_FROM_TRIGGER to sql_errno in Diagnostics_area in case the signal is raised from a trigger and SQLSTATE has value '02TRG'. To catch signal with SQLTSATE '02TRG' and handle it in special way, the methods Table_triggers_list::process_triggers select_insert::store_values select_create::store_values Rows_log_event::process_triggers and the overloaded function fill_record_n_invoke_before_triggers were extended with extra out parameter for returning the flag whether to skip the current values being processed by INSERT/UPDATE/DELETE statement. This extra parameter is passed as nullptr in case of AFTER trigger and BEFORE trigger this parameter points to a variable to store a marker whether to skip the current record or store it by calling write_record().
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 4207 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 4207 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
Error 4207 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 4207 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
Error 4207 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 4207 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
Error 4207 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 4207 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
Error 4207 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 4207 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bi
|
|
Error 4207 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 4207 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 4207 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 4207 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 4207 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bd
|
|
Error 4207 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 4207 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bd
|
|
Error 4207 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 4207 The row is skipped by a trigger implementation
|
|
Note 4094 At line 4 in test.t1_bd
|
|
Error 4207 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
|