# 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