1
SHOW TABLES FROM information_schema LIKE 'TRIGGERS';
2
Tables_in_information_schema (TRIGGERS)
4
#######################################################################
5
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
6
#######################################################################
7
DROP VIEW IF EXISTS test.v1;
8
DROP PROCEDURE IF EXISTS test.p1;
9
DROP FUNCTION IF EXISTS test.f1;
10
CREATE VIEW test.v1 AS SELECT * FROM information_schema.TRIGGERS;
11
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.TRIGGERS;
12
CREATE FUNCTION test.f1() returns BIGINT
14
DECLARE counter BIGINT DEFAULT NULL;
15
SELECT COUNT(*) INTO counter FROM information_schema.TRIGGERS;
18
# Attention: The printing of the next result sets is disabled.
19
SELECT * FROM information_schema.TRIGGERS;
20
SELECT * FROM test.v1;
24
DROP PROCEDURE test.p1;
25
DROP FUNCTION test.f1;
26
#########################################################################
27
# Testcase 3.2.12.1: INFORMATION_SCHEMA.TRIGGERS layout
28
#########################################################################
29
DESCRIBE information_schema.TRIGGERS;
30
Field Type Null Key Default Extra
31
TRIGGER_CATALOG varchar(512) YES NULL
32
TRIGGER_SCHEMA varchar(64) NO
33
TRIGGER_NAME varchar(64) NO
34
EVENT_MANIPULATION varchar(6) NO
35
EVENT_OBJECT_CATALOG varchar(512) YES NULL
36
EVENT_OBJECT_SCHEMA varchar(64) NO
37
EVENT_OBJECT_TABLE varchar(64) NO
38
ACTION_ORDER bigint(4) NO 0
39
ACTION_CONDITION longtext YES NULL
40
ACTION_STATEMENT longtext NO NULL
41
ACTION_ORIENTATION varchar(9) NO
42
ACTION_TIMING varchar(6) NO
43
ACTION_REFERENCE_OLD_TABLE varchar(64) YES NULL
44
ACTION_REFERENCE_NEW_TABLE varchar(64) YES NULL
45
ACTION_REFERENCE_OLD_ROW varchar(3) NO
46
ACTION_REFERENCE_NEW_ROW varchar(3) NO
47
CREATED datetime YES NULL
48
SQL_MODE longtext NO NULL
49
DEFINER longtext NO NULL
50
CHARACTER_SET_CLIENT varchar(32) NO
51
COLLATION_CONNECTION varchar(32) NO
52
DATABASE_COLLATION varchar(32) NO
53
SHOW CREATE TABLE information_schema.TRIGGERS;
55
TRIGGERS CREATE TEMPORARY TABLE `TRIGGERS` (
56
`TRIGGER_CATALOG` varchar(512) DEFAULT NULL,
57
`TRIGGER_SCHEMA` varchar(64) NOT NULL DEFAULT '',
58
`TRIGGER_NAME` varchar(64) NOT NULL DEFAULT '',
59
`EVENT_MANIPULATION` varchar(6) NOT NULL DEFAULT '',
60
`EVENT_OBJECT_CATALOG` varchar(512) DEFAULT NULL,
61
`EVENT_OBJECT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
62
`EVENT_OBJECT_TABLE` varchar(64) NOT NULL DEFAULT '',
63
`ACTION_ORDER` bigint(4) NOT NULL DEFAULT '0',
64
`ACTION_CONDITION` longtext,
65
`ACTION_STATEMENT` longtext NOT NULL,
66
`ACTION_ORIENTATION` varchar(9) NOT NULL DEFAULT '',
67
`ACTION_TIMING` varchar(6) NOT NULL DEFAULT '',
68
`ACTION_REFERENCE_OLD_TABLE` varchar(64) DEFAULT NULL,
69
`ACTION_REFERENCE_NEW_TABLE` varchar(64) DEFAULT NULL,
70
`ACTION_REFERENCE_OLD_ROW` varchar(3) NOT NULL DEFAULT '',
71
`ACTION_REFERENCE_NEW_ROW` varchar(3) NOT NULL DEFAULT '',
72
`CREATED` datetime DEFAULT NULL,
73
`SQL_MODE` longtext NOT NULL,
74
`DEFINER` longtext NOT NULL,
75
`CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',
76
`COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '',
77
`DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT ''
78
) ENGINE=MyISAM DEFAULT CHARSET=utf8
79
SHOW COLUMNS FROM information_schema.TRIGGERS;
80
Field Type Null Key Default Extra
81
TRIGGER_CATALOG varchar(512) YES NULL
82
TRIGGER_SCHEMA varchar(64) NO
83
TRIGGER_NAME varchar(64) NO
84
EVENT_MANIPULATION varchar(6) NO
85
EVENT_OBJECT_CATALOG varchar(512) YES NULL
86
EVENT_OBJECT_SCHEMA varchar(64) NO
87
EVENT_OBJECT_TABLE varchar(64) NO
88
ACTION_ORDER bigint(4) NO 0
89
ACTION_CONDITION longtext YES NULL
90
ACTION_STATEMENT longtext NO NULL
91
ACTION_ORIENTATION varchar(9) NO
92
ACTION_TIMING varchar(6) NO
93
ACTION_REFERENCE_OLD_TABLE varchar(64) YES NULL
94
ACTION_REFERENCE_NEW_TABLE varchar(64) YES NULL
95
ACTION_REFERENCE_OLD_ROW varchar(3) NO
96
ACTION_REFERENCE_NEW_ROW varchar(3) NO
97
CREATED datetime YES NULL
98
SQL_MODE longtext NO NULL
99
DEFINER longtext NO NULL
100
CHARACTER_SET_CLIENT varchar(32) NO
101
COLLATION_CONNECTION varchar(32) NO
102
DATABASE_COLLATION varchar(32) NO
103
SELECT * FROM information_schema.triggers
104
WHERE trigger_catalog IS NOT NULL OR event_object_catalog IS NOT NULL
105
OR action_condition IS NOT NULL OR action_reference_old_table IS NOT NULL
106
OR action_reference_new_table IS NOT NULL;
107
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
108
##################################################################################
109
# Testcase 3.2.18.2 + 3.2.18.3: INFORMATION_SCHEMA.TRIGGERS accessible information
110
##################################################################################
111
DROP DATABASE IF EXISTS db_datadict;
112
CREATE DATABASE db_datadict;
113
DROP USER 'testuser1'@'localhost';
114
CREATE USER 'testuser1'@'localhost';
115
DROP USER 'testuser2'@'localhost';
116
CREATE USER 'testuser2'@'localhost';
117
DROP USER 'testuser3'@'localhost';
118
CREATE USER 'testuser3'@'localhost';
119
DROP USER 'testuser4'@'localhost';
120
CREATE USER 'testuser4'@'localhost';
121
GRANT TRIGGER ON *.* TO 'testuser1'@'localhost';
122
GRANT TRIGGER ON *.* TO 'testuser3'@'localhost';
123
GRANT TRIGGER ON *.* TO 'testuser4'@'localhost';
124
GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
125
# Establish connection testuser1 (user=testuser1)
126
CREATE TABLE db_datadict.t1 (f1 INT, f2 INT, f3 INT)
127
ENGINE = <engine_type>;
128
CREATE TRIGGER trg1 BEFORE INSERT
129
ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
130
GRANT ALL ON db_datadict.t1 TO 'testuser2'@'localhost';
131
REVOKE TRIGGER ON db_datadict.t1 FROM 'testuser2'@'localhost';
132
GRANT SELECT ON db_datadict.t1 TO 'testuser3'@'localhost';
133
SELECT * FROM information_schema.triggers
134
WHERE trigger_name = 'trg1';
135
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
136
NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci
137
SHOW TRIGGERS FROM db_datadict;
138
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
139
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci
140
# Establish connection testuser2 (user=testuser2)
141
SHOW GRANTS FOR 'testuser2'@'localhost';
142
Grants for testuser2@localhost
143
GRANT USAGE ON *.* TO 'testuser2'@'localhost'
144
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `db_datadict`.`t1` TO 'testuser2'@'localhost'
145
# No TRIGGER Privilege --> no result for query
146
SELECT * FROM information_schema.triggers
147
WHERE trigger_name = 'trg1';
148
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
149
SHOW TRIGGERS FROM db_datadict;
150
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
151
# Establish connection testuser3 (user=testuser3)
152
SHOW GRANTS FOR 'testuser3'@'localhost';
153
Grants for testuser3@localhost
154
GRANT TRIGGER ON *.* TO 'testuser3'@'localhost'
155
GRANT SELECT ON `db_datadict`.`t1` TO 'testuser3'@'localhost'
156
# TRIGGER Privilege + SELECT Privilege on t1 --> result for query
157
SELECT * FROM information_schema.triggers
158
WHERE trigger_name = 'trg1';
159
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
160
NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci
161
SHOW TRIGGERS FROM db_datadict;
162
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
163
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci
164
# Establish connection testuser4 (user=testuser4)
165
SHOW GRANTS FOR 'testuser4'@'localhost';
166
Grants for testuser4@localhost
167
GRANT TRIGGER ON *.* TO 'testuser4'@'localhost'
168
# TRIGGER Privilege + no SELECT Privilege on t1 --> result for query
169
SELECT * FROM db_datadict.t1;
170
ERROR 42000: SELECT command denied to user 'testuser4'@'localhost' for table 't1'
172
ERROR 42000: SELECT command denied to user 'testuser4'@'localhost' for table 't1'
173
SELECT * FROM information_schema.triggers
174
WHERE trigger_name = 'trg1';
175
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
176
NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci
177
SHOW TRIGGERS FROM db_datadict;
178
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
179
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci
180
# Switch to connection default and close connections testuser1 - testuser4
181
SELECT * FROM information_schema.triggers
182
WHERE trigger_name = 'trg1';
183
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
184
NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci
185
SHOW TRIGGERS FROM db_datadict;
186
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
187
trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci
188
DROP USER 'testuser1'@'localhost';
189
DROP USER 'testuser2'@'localhost';
190
DROP USER 'testuser3'@'localhost';
191
DROP USER 'testuser4'@'localhost';
192
DROP DATABASE db_datadict;
193
#########################################################################
194
# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TRIGGERS modifications
195
#########################################################################
196
########################################################################
197
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
198
# DDL on INFORMATION_SCHEMA tables are not supported
199
########################################################################
200
DROP DATABASE IF EXISTS db_datadict;
201
CREATE DATABASE db_datadict;
202
CREATE TABLE db_datadict.t1 (f1 BIGINT)
203
ENGINE = <engine_type>;
204
CREATE TRIGGER db_datadict.trg1 BEFORE INSERT
205
ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
206
INSERT INTO information_schema.triggers
207
SELECT * FROM information_schema.triggers;
208
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
209
UPDATE information_schema.triggers SET trigger_schema = 'test'
210
WHERE table_name = 't1';
211
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
212
DELETE FROM information_schema.triggers WHERE trigger_name = 't1';
213
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
214
TRUNCATE information_schema.triggers;
215
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
216
CREATE INDEX my_idx_on_triggers ON information_schema.triggers(trigger_schema);
217
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
218
ALTER TABLE information_schema.triggers DROP PRIMARY KEY;
219
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
220
ALTER TABLE information_schema.triggers ADD f1 INT;
221
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
222
DROP TABLE information_schema.triggers;
223
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
224
ALTER TABLE information_schema.triggers RENAME db_datadict.triggers;
225
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
226
ALTER TABLE information_schema.triggers RENAME information_schema.xtriggers;
227
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
228
DROP DATABASE db_datadict;
added
removed
mysql-test/suite/funcs_1/r/is_triggers.result
