mirror of
https://github.com/MariaDB/server.git
synced 2025-04-09 00:35:40 +02:00
c170aaf06b
Added capability to create a trigger associated with several trigger events.
For this goal, the syntax of the CREATE TRIGGER statement was extended
to support the syntax structure { event [ OR ... ] } for the `trigger_event`
clause. Since one trigger will be able to handle several events it should be
provided a way to determine what kind of event is handled on execution of a
trigger. For this goal support of the clauses INSERTING, UPDATING , DELETING
was added by this patch. These clauses can be used inside a trigger body
to detect what kind of trigger action is currently processed using the following
boilerplate:
IF INSERTING THEN ...
ELSIF UPDATING THEN ...
ELSIF DELETING THEN ...
In case one of the clauses INSERTING, UPDATING, DELETING specified in
a trigger's body not matched with a trigger event type, the error
ER_INCOMPATIBLE_EVENT_FLAG is emitted.
After this patch be pushed, one Trigger object will be associated with several
trigger events. It means that the array Table_triggers_list::triggers can
contain several pointers to the same Trigger object in array members
corresponding to different events. Moreover, support of several trigger events
for the same trigger requires that the data members `next` and `action_order`
of the Trigger class be converted to arrays to store relating information per
trigger event base.
Ability to specify the same trigger for different event types results in
necessity to handle invalid cases on execution of the multi-event trigger,
when the OLD or NEW qualifiers doesn't match a current event type against that
the trigger is run. The clause OLD should produce the NULL value for INSERT event,
whereas the clause NEW should produce the NULL value for DELETE event.
238 lines
14 KiB
Text
238 lines
14 KiB
Text
set @save_sql_mode=@@global.sql_mode;
|
|
set global sql_mode="";
|
|
SHOW TABLES FROM information_schema LIKE 'TRIGGERS';
|
|
Tables_in_information_schema (TRIGGERS)
|
|
TRIGGERS
|
|
#######################################################################
|
|
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
|
|
#######################################################################
|
|
DROP VIEW IF EXISTS test.v1;
|
|
DROP PROCEDURE IF EXISTS test.p1;
|
|
DROP FUNCTION IF EXISTS test.f1;
|
|
CREATE VIEW test.v1 AS SELECT * FROM information_schema.TRIGGERS;
|
|
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.TRIGGERS;
|
|
CREATE FUNCTION test.f1() returns BIGINT
|
|
BEGIN
|
|
DECLARE counter BIGINT DEFAULT NULL;
|
|
SELECT COUNT(*) INTO counter FROM information_schema.TRIGGERS;
|
|
RETURN counter;
|
|
END//
|
|
# Attention: The printing of the next result sets is disabled.
|
|
SELECT * FROM information_schema.TRIGGERS;
|
|
SELECT * FROM test.v1;
|
|
CALL test.p1;
|
|
SELECT test.f1();
|
|
DROP VIEW test.v1;
|
|
DROP PROCEDURE test.p1;
|
|
DROP FUNCTION test.f1;
|
|
#########################################################################
|
|
# Testcase 3.2.12.1: INFORMATION_SCHEMA.TRIGGERS layout
|
|
#########################################################################
|
|
DESCRIBE information_schema.TRIGGERS;
|
|
Field Type Null Key Default Extra
|
|
TRIGGER_CATALOG varchar(512) NO NULL
|
|
TRIGGER_SCHEMA varchar(64) NO NULL
|
|
TRIGGER_NAME varchar(64) NO NULL
|
|
EVENT_MANIPULATION varchar(20) NO NULL
|
|
EVENT_OBJECT_CATALOG varchar(512) NO NULL
|
|
EVENT_OBJECT_SCHEMA varchar(64) NO NULL
|
|
EVENT_OBJECT_TABLE varchar(64) NO NULL
|
|
ACTION_ORDER bigint(4) NO NULL
|
|
ACTION_CONDITION longtext YES NULL
|
|
ACTION_STATEMENT longtext NO NULL
|
|
ACTION_ORIENTATION varchar(9) NO NULL
|
|
ACTION_TIMING varchar(6) NO NULL
|
|
ACTION_REFERENCE_OLD_TABLE varchar(64) YES NULL
|
|
ACTION_REFERENCE_NEW_TABLE varchar(64) YES NULL
|
|
ACTION_REFERENCE_OLD_ROW varchar(3) NO NULL
|
|
ACTION_REFERENCE_NEW_ROW varchar(3) NO NULL
|
|
CREATED datetime(2) YES NULL
|
|
SQL_MODE varchar(8192) NO NULL
|
|
DEFINER varchar(384) NO NULL
|
|
CHARACTER_SET_CLIENT varchar(32) NO NULL
|
|
COLLATION_CONNECTION varchar(64) NO NULL
|
|
DATABASE_COLLATION varchar(64) NO NULL
|
|
SHOW CREATE TABLE information_schema.TRIGGERS;
|
|
Table Create Table
|
|
TRIGGERS CREATE TEMPORARY TABLE `TRIGGERS` (
|
|
`TRIGGER_CATALOG` varchar(512) NOT NULL,
|
|
`TRIGGER_SCHEMA` varchar(64) NOT NULL,
|
|
`TRIGGER_NAME` varchar(64) NOT NULL,
|
|
`EVENT_MANIPULATION` varchar(20) NOT NULL,
|
|
`EVENT_OBJECT_CATALOG` varchar(512) NOT NULL,
|
|
`EVENT_OBJECT_SCHEMA` varchar(64) NOT NULL,
|
|
`EVENT_OBJECT_TABLE` varchar(64) NOT NULL,
|
|
`ACTION_ORDER` bigint(4) NOT NULL,
|
|
`ACTION_CONDITION` longtext,
|
|
`ACTION_STATEMENT` longtext NOT NULL,
|
|
`ACTION_ORIENTATION` varchar(9) NOT NULL,
|
|
`ACTION_TIMING` varchar(6) NOT NULL,
|
|
`ACTION_REFERENCE_OLD_TABLE` varchar(64),
|
|
`ACTION_REFERENCE_NEW_TABLE` varchar(64),
|
|
`ACTION_REFERENCE_OLD_ROW` varchar(3) NOT NULL,
|
|
`ACTION_REFERENCE_NEW_ROW` varchar(3) NOT NULL,
|
|
`CREATED` datetime(2),
|
|
`SQL_MODE` varchar(8192) NOT NULL,
|
|
`DEFINER` varchar(384) NOT NULL,
|
|
`CHARACTER_SET_CLIENT` varchar(32) NOT NULL,
|
|
`COLLATION_CONNECTION` varchar(64) NOT NULL,
|
|
`DATABASE_COLLATION` varchar(64) NOT NULL
|
|
) DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
|
|
SHOW COLUMNS FROM information_schema.TRIGGERS;
|
|
Field Type Null Key Default Extra
|
|
TRIGGER_CATALOG varchar(512) NO NULL
|
|
TRIGGER_SCHEMA varchar(64) NO NULL
|
|
TRIGGER_NAME varchar(64) NO NULL
|
|
EVENT_MANIPULATION varchar(20) NO NULL
|
|
EVENT_OBJECT_CATALOG varchar(512) NO NULL
|
|
EVENT_OBJECT_SCHEMA varchar(64) NO NULL
|
|
EVENT_OBJECT_TABLE varchar(64) NO NULL
|
|
ACTION_ORDER bigint(4) NO NULL
|
|
ACTION_CONDITION longtext YES NULL
|
|
ACTION_STATEMENT longtext NO NULL
|
|
ACTION_ORIENTATION varchar(9) NO NULL
|
|
ACTION_TIMING varchar(6) NO NULL
|
|
ACTION_REFERENCE_OLD_TABLE varchar(64) YES NULL
|
|
ACTION_REFERENCE_NEW_TABLE varchar(64) YES NULL
|
|
ACTION_REFERENCE_OLD_ROW varchar(3) NO NULL
|
|
ACTION_REFERENCE_NEW_ROW varchar(3) NO NULL
|
|
CREATED datetime(2) YES NULL
|
|
SQL_MODE varchar(8192) NO NULL
|
|
DEFINER varchar(384) NO NULL
|
|
CHARACTER_SET_CLIENT varchar(32) NO NULL
|
|
COLLATION_CONNECTION varchar(64) NO NULL
|
|
DATABASE_COLLATION varchar(64) NO NULL
|
|
SELECT * FROM information_schema.triggers
|
|
WHERE trigger_catalog IS NOT NULL OR event_object_catalog IS NOT NULL
|
|
OR action_condition IS NOT NULL OR action_reference_old_table IS NOT NULL
|
|
OR action_reference_new_table IS NOT NULL
|
|
ORDER BY trigger_schema, trigger_name;
|
|
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
def mtr gs_insert INSERT def mtr global_suppressions 1 NULL BEGIN DECLARE dummy INT; SELECT "" REGEXP NEW.pattern INTO dummy; END ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
def mtr ts_insert INSERT def mtr test_suppressions 1 NULL BEGIN DECLARE dummy INT; SELECT "" REGEXP NEW.pattern INTO dummy; END ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
##################################################################################
|
|
# Testcase 3.2.18.2 + 3.2.18.3: INFORMATION_SCHEMA.TRIGGERS accessible information
|
|
##################################################################################
|
|
DROP DATABASE IF EXISTS db_datadict;
|
|
CREATE DATABASE db_datadict;
|
|
DROP USER 'testuser1'@'localhost';
|
|
CREATE USER 'testuser1'@'localhost';
|
|
DROP USER 'testuser2'@'localhost';
|
|
CREATE USER 'testuser2'@'localhost';
|
|
DROP USER 'testuser3'@'localhost';
|
|
CREATE USER 'testuser3'@'localhost';
|
|
DROP USER 'testuser4'@'localhost';
|
|
CREATE USER 'testuser4'@'localhost';
|
|
GRANT TRIGGER ON *.* TO 'testuser1'@'localhost';
|
|
GRANT TRIGGER ON *.* TO 'testuser3'@'localhost';
|
|
GRANT TRIGGER ON *.* TO 'testuser4'@'localhost';
|
|
GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
|
|
connect testuser1, localhost, testuser1, , db_datadict;
|
|
CREATE TABLE db_datadict.t1 (f1 INT, f2 INT, f3 INT)
|
|
ENGINE = <engine_type>;
|
|
CREATE TRIGGER trg1 BEFORE INSERT
|
|
ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
|
|
GRANT ALL ON db_datadict.t1 TO 'testuser2'@'localhost';
|
|
REVOKE TRIGGER ON db_datadict.t1 FROM 'testuser2'@'localhost';
|
|
GRANT SELECT ON db_datadict.t1 TO 'testuser3'@'localhost';
|
|
SELECT * FROM information_schema.triggers
|
|
WHERE trigger_name = 'trg1';
|
|
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
SHOW TRIGGERS FROM db_datadict;
|
|
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
|
|
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
connect testuser2, localhost, testuser2, , db_datadict;
|
|
SHOW GRANTS FOR 'testuser2'@'localhost';
|
|
Grants for testuser2@localhost
|
|
GRANT USAGE ON *.* TO `testuser2`@`localhost`
|
|
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, DELETE HISTORY ON `db_datadict`.`t1` TO `testuser2`@`localhost`
|
|
# No TRIGGER Privilege --> no result for query
|
|
SELECT * FROM information_schema.triggers
|
|
WHERE trigger_name = 'trg1';
|
|
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
SHOW TRIGGERS FROM db_datadict;
|
|
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
|
|
connect testuser3, localhost, testuser3, , test;
|
|
SHOW GRANTS FOR 'testuser3'@'localhost';
|
|
Grants for testuser3@localhost
|
|
GRANT TRIGGER ON *.* TO `testuser3`@`localhost`
|
|
GRANT SELECT ON `db_datadict`.`t1` TO `testuser3`@`localhost`
|
|
# TRIGGER Privilege + SELECT Privilege on t1 --> result for query
|
|
SELECT * FROM information_schema.triggers
|
|
WHERE trigger_name = 'trg1';
|
|
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
SHOW TRIGGERS FROM db_datadict;
|
|
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
|
|
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
connect testuser4, localhost, testuser4, , test;
|
|
SHOW GRANTS FOR 'testuser4'@'localhost';
|
|
Grants for testuser4@localhost
|
|
GRANT TRIGGER ON *.* TO `testuser4`@`localhost`
|
|
# TRIGGER Privilege + no SELECT Privilege on t1 --> result for query
|
|
SELECT * FROM db_datadict.t1;
|
|
ERROR 42000: SELECT command denied to user 'testuser4'@'localhost' for table `db_datadict`.`t1`
|
|
DESC db_datadict.t1;
|
|
ERROR 42000: SELECT command denied to user 'testuser4'@'localhost' for table `db_datadict`.`t1`
|
|
SELECT * FROM information_schema.triggers
|
|
WHERE trigger_name = 'trg1';
|
|
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
SHOW TRIGGERS FROM db_datadict;
|
|
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
|
|
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
connection default;
|
|
disconnect testuser1;
|
|
disconnect testuser2;
|
|
disconnect testuser3;
|
|
disconnect testuser4;
|
|
SELECT * FROM information_schema.triggers
|
|
WHERE trigger_name = 'trg1';
|
|
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
SHOW TRIGGERS FROM db_datadict;
|
|
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
|
|
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
DROP USER 'testuser1'@'localhost';
|
|
DROP USER 'testuser2'@'localhost';
|
|
DROP USER 'testuser3'@'localhost';
|
|
DROP USER 'testuser4'@'localhost';
|
|
DROP DATABASE db_datadict;
|
|
#########################################################################
|
|
# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TRIGGERS modifications
|
|
#########################################################################
|
|
########################################################################
|
|
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
|
|
# DDL on INFORMATION_SCHEMA tables are not supported
|
|
########################################################################
|
|
DROP DATABASE IF EXISTS db_datadict;
|
|
CREATE DATABASE db_datadict;
|
|
CREATE TABLE db_datadict.t1 (f1 BIGINT)
|
|
ENGINE = <engine_type>;
|
|
CREATE TRIGGER db_datadict.trg1 BEFORE INSERT
|
|
ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
|
|
INSERT INTO information_schema.triggers
|
|
SELECT * FROM information_schema.triggers;
|
|
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
UPDATE information_schema.triggers SET trigger_schema = 'test'
|
|
WHERE table_name = 't1';
|
|
Got one of the listed errors
|
|
DELETE FROM information_schema.triggers WHERE trigger_name = 't1';
|
|
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
TRUNCATE information_schema.triggers;
|
|
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
CREATE INDEX my_idx_on_triggers ON information_schema.triggers(trigger_schema);
|
|
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
ALTER TABLE information_schema.triggers DROP PRIMARY KEY;
|
|
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
ALTER TABLE information_schema.triggers ADD f1 INT;
|
|
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
DROP TABLE information_schema.triggers;
|
|
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
ALTER TABLE information_schema.triggers RENAME db_datadict.triggers;
|
|
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
ALTER TABLE information_schema.triggers RENAME information_schema.xtriggers;
|
|
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
DROP DATABASE db_datadict;
|
|
set global sql_mode=@save_sql_mode;
|