summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/information_schema.result77
-rw-r--r--mysql-test/r/information_schema_db.result2
-rw-r--r--mysql-test/r/rpl_sp.result4
-rw-r--r--mysql-test/r/trigger.result53
-rw-r--r--mysql-test/r/view.result2
-rw-r--r--mysql-test/t/information_schema.test35
-rw-r--r--mysql-test/t/rpl_sp.test2
-rw-r--r--mysql-test/t/trigger.test59
-rw-r--r--mysql-test/t/view.test2
-rw-r--r--sql/handler.cc1
-rw-r--r--sql/item.h4
-rw-r--r--sql/lex.h1
-rw-r--r--sql/mysql_priv.h1
-rw-r--r--sql/mysqld.cc1
-rw-r--r--sql/share/errmsg.txt2
-rw-r--r--sql/sp.cc4
-rw-r--r--sql/sql_base.cc2
-rw-r--r--sql/sql_lex.h1
-rw-r--r--sql/sql_parse.cc1
-rw-r--r--sql/sql_show.cc94
-rw-r--r--sql/sql_table.cc14
-rw-r--r--sql/sql_trigger.cc367
-rw-r--r--sql/sql_trigger.h11
-rw-r--r--sql/sql_yacc.yy32
-rw-r--r--sql/table.h2
25 files changed, 644 insertions, 130 deletions
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index 98f3d59485f..857e8a61a37 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -48,6 +48,7 @@ TABLE_PRIVILEGES
COLUMN_PRIVILEGES
TABLE_CONSTRAINTS
KEY_COLUMN_USAGE
+TRIGGERS
columns_priv
db
func
@@ -77,6 +78,7 @@ c table_name
TABLES TABLES
TABLE_PRIVILEGES TABLE_PRIVILEGES
TABLE_CONSTRAINTS TABLE_CONSTRAINTS
+TRIGGERS TRIGGERS
tables_priv tables_priv
time_zone time_zone
time_zone_leap_second time_zone_leap_second
@@ -94,6 +96,7 @@ c table_name
TABLES TABLES
TABLE_PRIVILEGES TABLE_PRIVILEGES
TABLE_CONSTRAINTS TABLE_CONSTRAINTS
+TRIGGERS TRIGGERS
tables_priv tables_priv
time_zone time_zone
time_zone_leap_second time_zone_leap_second
@@ -111,6 +114,7 @@ c table_name
TABLES TABLES
TABLE_PRIVILEGES TABLE_PRIVILEGES
TABLE_CONSTRAINTS TABLE_CONSTRAINTS
+TRIGGERS TRIGGERS
tables_priv tables_priv
time_zone time_zone
time_zone_leap_second time_zone_leap_second
@@ -577,6 +581,7 @@ Tables_in_information_schema (T%)
TABLES
TABLE_PRIVILEGES
TABLE_CONSTRAINTS
+TRIGGERS
create database information_schema;
ERROR HY000: Can't create database 'information_schema'; database exists
use information_schema;
@@ -585,6 +590,7 @@ Tables_in_information_schema (T%) Table_type
TABLES TEMPORARY
TABLE_PRIVILEGES TEMPORARY
TABLE_CONSTRAINTS TEMPORARY
+TRIGGERS TEMPORARY
create table t1(a int);
ERROR 42S02: Unknown table 't1' in information_schema
use test;
@@ -596,6 +602,7 @@ Tables_in_information_schema (T%)
TABLES
TABLE_PRIVILEGES
TABLE_CONSTRAINTS
+TRIGGERS
select table_name from tables where table_name='user';
table_name
user
@@ -690,7 +697,7 @@ CREATE TABLE t_crashme ( f1 BIGINT);
CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1;
CREATE VIEW a2 AS SELECT t_CRASHME FROM a1;
count(*)
-100
+101
drop view a2, a1;
drop table t_crashme;
select table_schema,table_name, column_name from
@@ -701,6 +708,8 @@ information_schema COLUMNS COLUMN_TYPE
information_schema ROUTINES ROUTINE_DEFINITION
information_schema ROUTINES SQL_MODE
information_schema VIEWS VIEW_DEFINITION
+information_schema TRIGGERS ACTION_CONDITION
+information_schema TRIGGERS ACTION_STATEMENT
select table_name, column_name, data_type from information_schema.columns
where data_type = 'datetime';
table_name column_name data_type
@@ -709,6 +718,7 @@ TABLES UPDATE_TIME datetime
TABLES CHECK_TIME datetime
ROUTINES CREATED datetime
ROUTINES LAST_ALTERED datetime
+TRIGGERS CREATED datetime
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A
WHERE NOT EXISTS
(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
@@ -755,8 +765,71 @@ delete from mysql.db where user='mysqltest_4';
flush privileges;
SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA;
table_schema count(*)
-information_schema 15
+information_schema 16
mysql 17
+create table t1 (i int, j int);
+create trigger trg1 before insert on t1 for each row
+begin
+if new.j > 10 then
+set new.j := 10;
+end if;
+end|
+create trigger trg2 before update on t1 for each row
+begin
+if old.i % 2 = 0 then
+set new.j := -1;
+end if;
+end|
+create trigger trg3 after update on t1 for each row
+begin
+if new.j = -1 then
+set @fired:= "Yes";
+end if;
+end|
+show triggers;
+Trigger Event Table Statement Timing Created
+trg1 INSERT t1
+begin
+if new.j > 10 then
+set new.j := 10;
+end if;
+end BEFORE NULL
+trg2 UPDATE t1
+begin
+if old.i % 2 = 0 then
+set new.j := -1;
+end if;
+end BEFORE NULL
+trg3 UPDATE t1
+begin
+if new.j = -1 then
+set @fired:= "Yes";
+end if;
+end AFTER NULL
+select * from information_schema.triggers;
+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
+NULL test trg1 INSERT NULL test t1 0 NULL
+begin
+if new.j > 10 then
+set new.j := 10;
+end if;
+end ROW BEFORE NULL NULL OLD NEW NULL
+NULL test trg2 UPDATE NULL test t1 0 NULL
+begin
+if old.i % 2 = 0 then
+set new.j := -1;
+end if;
+end ROW BEFORE NULL NULL OLD NEW NULL
+NULL test trg3 UPDATE NULL test t1 0 NULL
+begin
+if new.j = -1 then
+set @fired:= "Yes";
+end if;
+end ROW AFTER NULL NULL OLD NEW NULL
+drop trigger trg1;
+drop trigger trg2;
+drop trigger trg3;
+drop table t1;
create database mysqltest;
create table mysqltest.t1 (f1 int, f2 int);
create table mysqltest.t2 (f1 int);
diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result
index 3da5cc7bd11..ece30924055 100644
--- a/mysql-test/r/information_schema_db.result
+++ b/mysql-test/r/information_schema_db.result
@@ -16,11 +16,13 @@ TABLE_PRIVILEGES
COLUMN_PRIVILEGES
TABLE_CONSTRAINTS
KEY_COLUMN_USAGE
+TRIGGERS
show tables from INFORMATION_SCHEMA like 'T%';
Tables_in_information_schema (T%)
TABLES
TABLE_PRIVILEGES
TABLE_CONSTRAINTS
+TRIGGERS
create database `inf%`;
use `inf%`;
show tables;
diff --git a/mysql-test/r/rpl_sp.result b/mysql-test/r/rpl_sp.result
index 64e09839f1b..15180abe8fd 100644
--- a/mysql-test/r/rpl_sp.result
+++ b/mysql-test/r/rpl_sp.result
@@ -237,7 +237,7 @@ select * from t1;
a
10
delete from t1;
-drop trigger t1.trg;
+drop trigger trg;
insert into t1 values (1);
select * from t1;
a
@@ -248,7 +248,7 @@ master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1
master-bin.000002 # Query 1 # use `mysqltest1`; create trigger trg before insert on t1 for each row set new.a= 10
master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1)
master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1
-master-bin.000002 # Query 1 # use `mysqltest1`; drop trigger t1.trg
+master-bin.000002 # Query 1 # use `mysqltest1`; drop trigger trg
master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1)
select * from t1;
a
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index efd09ba08fc..7e3a6fa65d4 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -12,13 +12,13 @@ insert into t1 values (1);
select @a;
@a
1
-drop trigger t1.trg;
+drop trigger trg;
create trigger trg before insert on t1 for each row set @a:=new.i;
insert into t1 values (123);
select @a;
@a
123
-drop trigger t1.trg;
+drop trigger trg;
drop table t1;
create table t1 (i int not null, j int);
create trigger trg before insert on t1 for each row
@@ -33,7 +33,7 @@ select * from t1|
i j
1 10
2 3
-drop trigger t1.trg|
+drop trigger trg|
drop table t1|
create table t1 (i int not null primary key);
create trigger trg after insert on t1 for each row
@@ -43,7 +43,7 @@ insert into t1 values (2),(3),(4),(5);
select @a;
@a
2:3:4:5
-drop trigger t1.trg;
+drop trigger trg;
drop table t1;
create table t1 (aid int not null primary key, balance int not null default 0);
insert into t1 values (1, 1000), (2,3000);
@@ -65,7 +65,7 @@ Too big change for aid = 2
aid balance
1 1500
2 3000
-drop trigger t1.trg|
+drop trigger trg|
drop table t1|
create table t1 (i int);
insert into t1 values (1),(2),(3),(4);
@@ -76,7 +76,7 @@ update t1 set i=3;
select @total_change;
@total_change
2
-drop trigger t1.trg;
+drop trigger trg;
drop table t1;
create table t1 (i int);
insert into t1 values (1),(2),(3),(4);
@@ -87,7 +87,7 @@ delete from t1 where i <= 3;
select @del_sum;
@del_sum
6
-drop trigger t1.trg;
+drop trigger trg;
drop table t1;
create table t1 (i int);
insert into t1 values (1),(2),(3),(4);
@@ -97,7 +97,7 @@ delete from t1 where i <> 0;
select @del;
@del
1
-drop trigger t1.trg;
+drop trigger trg;
drop table t1;
create table t1 (i int, j int);
create trigger trg1 before insert on t1 for each row
@@ -137,9 +137,9 @@ i j
1 20
2 -1
3 20
-drop trigger t1.trg1;
-drop trigger t1.trg2;
-drop trigger t1.trg3;
+drop trigger trg1;
+drop trigger trg2;
+drop trigger trg3;
drop table t1;
create table t1 (id int not null primary key, data int);
create trigger t1_bi before insert on t1 for each row
@@ -197,7 +197,7 @@ select * from t2;
event
INSERT INTO t1 id=1 data='one'
INSERT INTO t1 id=2 data='two'
-drop trigger t1.t1_ai;
+drop trigger t1_ai;
create trigger t1_bi before insert on t1 for each row
begin
if exists (select id from t3 where id=new.fk) then
@@ -271,6 +271,7 @@ id copy
3 NULL
drop table t1, t2;
create table t1 (i int);
+create table t3 (i int);
create trigger trg before insert on t1 for each row set @a:= old.i;
ERROR HY000: There is no OLD row in on INSERT trigger
create trigger trg before delete on t1 for each row set @a:= new.i;
@@ -292,14 +293,19 @@ create trigger trg after insert on t1 for each row set @a:=1;
ERROR HY000: Trigger already exists
create trigger trg2 before insert on t1 for each row set @a:=1;
ERROR HY000: Trigger already exists
-drop trigger t1.trg;
-drop trigger t1.trg;
+create trigger trg before insert on t3 for each row set @a:=1;
+ERROR HY000: Trigger already exists
+create trigger trg2 before insert on t3 for each row set @a:=1;
+drop trigger trg2;
+drop trigger trg;
+drop trigger trg;
ERROR HY000: Trigger does not exist
create view v1 as select * from t1;
create trigger trg before insert on v1 for each row set @a:=1;
ERROR HY000: 'test.v1' is not BASE TABLE
drop view v1;
drop table t1;
+drop table t3;
create temporary table t1 (i int);
create trigger trg before insert on t1 for each row set @a:=1;
ERROR HY000: Trigger's 't1' is view or temporary table
@@ -307,7 +313,7 @@ drop table t1;
create table t1 (x1col char);
create trigger tx1 before insert on t1 for each row set new.x1col = 'x';
insert into t1 values ('y');
-drop trigger t1.tx1;
+drop trigger tx1;
drop table t1;
create table t1 (i int) engine=myisam;
insert into t1 values (1), (2);
@@ -318,8 +324,8 @@ delete from t1;
select @del_before, @del_after;
@del_before @del_after
3 3
-drop trigger t1.trg1;
-drop trigger t1.trg2;
+drop trigger trg1;
+drop trigger trg2;
drop table t1;
create table t1 (a int);
create trigger trg1 before insert on t1 for each row set new.a= 10;
@@ -336,6 +342,15 @@ create table t1 (i int);
create trigger trg1 before insert on t1 for each row set @a:= 1;
drop database mysqltest;
use test;
+create database mysqltest;
+create table mysqltest.t1 (i int);
+create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1;
+ERROR HY000: Trigger in wrong schema
+use mysqltest;
+create trigger test.trg1 before insert on t1 for each row set @a:= 1;
+ERROR HY000: Trigger in wrong schema
+drop database mysqltest;
+use test;
create table t1 (i int, j int default 10, k int not null, key (k));
create table t2 (i int);
insert into t1 (i, k) values (1, 1);
@@ -549,7 +564,7 @@ i k
1 1
2 2
alter table t1 add primary key (i);
-drop trigger t1.bi;
+drop trigger bi;
insert into t1 values (2, 4) on duplicate key update k= k + 10;
ERROR 42S22: Unknown column 'bt' in 'NEW'
select * from t1;
@@ -578,5 +593,5 @@ create trigger t1_bu before update on t1 for each row set new.col1= bug5893();
drop function bug5893;
update t1 set col2 = 4;
ERROR 42000: FUNCTION test.bug5893 does not exist
-drop trigger t1.t1_bu;
+drop trigger t1_bu;
drop table t1;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index a132279c6bc..624fae4d728 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -1245,7 +1245,7 @@ select * from v1;
s1
select * from t1;
s1
-drop trigger t1.t1_bi;
+drop trigger t1_bi;
drop view v1;
drop table t1;
create table t1 (s1 tinyint);
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
index 7c0624b67fd..08f572a593a 100644
--- a/mysql-test/t/information_schema.test
+++ b/mysql-test/t/information_schema.test
@@ -505,6 +505,41 @@ flush privileges;
#
SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA;
+
+#
+# TRIGGERS table test
+#
+create table t1 (i int, j int);
+
+delimiter |;
+create trigger trg1 before insert on t1 for each row
+begin
+ if new.j > 10 then
+ set new.j := 10;
+ end if;
+end|
+create trigger trg2 before update on t1 for each row
+begin
+ if old.i % 2 = 0 then
+ set new.j := -1;
+ end if;
+end|
+create trigger trg3 after update on t1 for each row
+begin
+ if new.j = -1 then
+ set @fired:= "Yes";
+ end if;
+end|
+delimiter ;|
+show triggers;
+select * from information_schema.triggers;
+
+drop trigger trg1;
+drop trigger trg2;
+drop trigger trg3;
+drop table t1;
+
+
#
# Bug #10964 Information Schema:Authorization check on privilege tables is improper
#
diff --git a/mysql-test/t/rpl_sp.test b/mysql-test/t/rpl_sp.test
index e2a8982ebaa..184ac4edea1 100644
--- a/mysql-test/t/rpl_sp.test
+++ b/mysql-test/t/rpl_sp.test
@@ -249,7 +249,7 @@ select * from t1;
connection master;
delete from t1;
-drop trigger t1.trg;
+drop trigger trg;
insert into t1 values (1);
select * from t1;
--replace_column 2 # 5 #
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 8a27636ed84..b36140f2ada 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -17,13 +17,13 @@ set @a:=0;
select @a;
insert into t1 values (1);
select @a;
-drop trigger t1.trg;
+drop trigger trg;
# let us test simple trigger reading some values
create trigger trg before insert on t1 for each row set @a:=new.i;
insert into t1 values (123);
select @a;
-drop trigger t1.trg;
+drop trigger trg;
drop table t1;
@@ -40,7 +40,7 @@ end|
insert into t1 (i) values (1)|
insert into t1 (i,j) values (2, 3)|
select * from t1|
-drop trigger t1.trg|
+drop trigger trg|
drop table t1|
delimiter ;|
@@ -52,7 +52,7 @@ create trigger trg after insert on t1 for each row
set @a:="";
insert into t1 values (2),(3),(4),(5);
select @a;
-drop trigger t1.trg;
+drop trigger trg;
drop table t1;
# PS doesn't work with multi-row statements
@@ -75,7 +75,7 @@ set @update_failed:=""|
update t1 set balance=1500|
select @update_failed;
select * from t1|
-drop trigger t1.trg|
+drop trigger trg|
drop table t1|
delimiter ;|
--enable_ps_protocol
@@ -88,7 +88,7 @@ create trigger trg after update on t1 for each row
set @total_change:=0;
update t1 set i=3;
select @total_change;
-drop trigger t1.trg;
+drop trigger trg;
drop table t1;
# Before delete trigger
@@ -100,7 +100,7 @@ create trigger trg before delete on t1 for each row
set @del_sum:= 0;
delete from t1 where i <= 3;
select @del_sum;
-drop trigger t1.trg;
+drop trigger trg;
drop table t1;
# After delete trigger.
@@ -111,7 +111,7 @@ create trigger trg after delete on t1 for each row set @del:= 1;
set @del:= 0;
delete from t1 where i <> 0;
select @del;
-drop trigger t1.trg;
+drop trigger trg;
drop table t1;
# Several triggers on one table
@@ -145,9 +145,9 @@ update t1 set j= 20;
select @fired;
select * from t1;
-drop trigger t1.trg1;
-drop trigger t1.trg2;
-drop trigger t1.trg3;
+drop trigger trg1;
+drop trigger trg2;
+drop trigger trg3;
drop table t1;
@@ -212,7 +212,7 @@ create trigger t1_ai after insert on t1 for each row
insert into t1 (id, data) values (1, "one"), (2, "two");
select * from t1;
select * from t2;
-drop trigger t1.t1_ai;
+drop trigger t1_ai;
# Trigger which uses couple of tables (and partially emulates FK constraint)
delimiter |;
create trigger t1_bi before insert on t1 for each row
@@ -282,6 +282,7 @@ drop table t1, t2;
# Test of wrong column specifiers in triggers
#
create table t1 (i int);
+create table t3 (i int);
--error 1363
create trigger trg before insert on t1 for each row set @a:= old.i;
@@ -301,7 +302,7 @@ create trigger trg before update on t1 for each row set @a:=old.j;
#
# Let us test various trigger creation errors
-#
+# Also quickly test table namespace (bug#5892/6182)
#
--error 1146
create trigger trg before insert on t2 for each row set @a:=1;
@@ -311,10 +312,14 @@ create trigger trg before insert on t1 for each row set @a:=1;
create trigger trg after insert on t1 for each row set @a:=1;
--error 1359
create trigger trg2 before insert on t1 for each row set @a:=1;
-drop trigger t1.trg;
+--error 1359
+create trigger trg before insert on t3 for each row set @a:=1;
+create trigger trg2 before insert on t3 for each row set @a:=1;
+drop trigger trg2;
+drop trigger trg;
--error 1360
-drop trigger t1.trg;
+drop trigger trg;
create view v1 as select * from t1;
--error 1347
@@ -322,6 +327,7 @@ create trigger trg before insert on v1 for each row set @a:=1;
drop view v1;
drop table t1;
+drop table t3;
create temporary table t1 (i int);
--error 1361
@@ -339,7 +345,7 @@ drop table t1;
create table t1 (x1col char);
create trigger tx1 before insert on t1 for each row set new.x1col = 'x';
insert into t1 values ('y');
-drop trigger t1.tx1;
+drop trigger tx1;
drop table t1;
#
@@ -355,8 +361,8 @@ create trigger trg2 after delete on t1 for each row set @del_after:= @del_after
set @del_before:=0, @del_after:= 0;
delete from t1;
select @del_before, @del_after;
-drop trigger t1.trg1;
-drop trigger t1.trg2;
+drop trigger trg1;
+drop trigger trg2;
drop table t1;
# Test for bug #5859 "DROP TABLE does not drop triggers". Trigger should not
@@ -378,6 +384,19 @@ create trigger trg1 before insert on t1 for each row set @a:= 1;
drop database mysqltest;
use test;
+# Test for bug #8791
+# "Triggers: Allowed to create triggers on a subject table in a different DB".
+create database mysqltest;
+create table mysqltest.t1 (i int);
+--error 1429
+create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1;
+use mysqltest;
+--error 1429
+create trigger test.trg1 before insert on t1 for each row set @a:= 1;
+drop database mysqltest;
+use test;
+
+
# Test for bug #5860 "Multi-table UPDATE does not activate update triggers"
# We will also test how delete triggers wor for multi-table DELETE.
create table t1 (i int, j int default 10, k int not null, key (k));
@@ -559,7 +578,7 @@ select * from t1;
# To test properly code-paths different from those that are used
# in ordinary INSERT we need to drop "before insert" trigger.
alter table t1 add primary key (i);
-drop trigger t1.bi;
+drop trigger bi;
--error 1054
insert into t1 values (2, 4) on duplicate key update k= k + 10;
select * from t1;
@@ -589,5 +608,5 @@ drop function bug5893;
--error 1305
update t1 set col2 = 4;
# This should not crash server too.
-drop trigger t1.t1_bu;
+drop trigger t1_bu;
drop table t1;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index a98ecfe232f..449bca63f0d 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -1182,7 +1182,7 @@ create view v1 as select * from t1 where s1 <> 127 with check option;
insert into v1 values (0);
select * from v1;
select * from t1;
-drop trigger t1.t1_bi;
+drop trigger t1_bi;
drop view v1;
drop table t1;
diff --git a/sql/handler.cc b/sql/handler.cc
index a61dce35501..9f8fdceb69e 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -2434,6 +2434,7 @@ TYPELIB *ha_known_exts(void)
known_extensions_id= mysys_usage_id;
found_exts.push_back((char*) triggers_file_ext);
+ found_exts.push_back((char*) trigname_file_ext);
for (types= sys_table_types; types->type; types++)
{
if (*types->value == SHOW_OPTION_YES)
diff --git a/sql/item.h b/sql/item.h
index f195557fb69..5a1cf193806 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1781,7 +1781,7 @@ public:
*/
enum trg_action_time_type
{
- TRG_ACTION_BEFORE= 0, TRG_ACTION_AFTER= 1
+ TRG_ACTION_BEFORE= 0, TRG_ACTION_AFTER= 1, TRG_ACTION_MAX
};
/*
@@ -1789,7 +1789,7 @@ enum trg_action_time_type
*/
enum trg_event_type
{
- TRG_EVENT_INSERT= 0 , TRG_EVENT_UPDATE= 1, TRG_EVENT_DELETE= 2
+ TRG_EVENT_INSERT= 0 , TRG_EVENT_UPDATE= 1, TRG_EVENT_DELETE= 2, TRG_EVENT_MAX
};
class Table_triggers_list;
diff --git a/sql/lex.h b/sql/lex.h
index aa10328ced0..122e7040c80 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -497,6 +497,7 @@ static SYMBOL symbols[] = {
{ "TRAILING", SYM(TRAILING)},
{ "TRANSACTION", SYM(TRANSACTION_SYM)},
{ "TRIGGER", SYM(TRIGGER_SYM)},
+ { "TRIGGERS", SYM(TRIGGERS_SYM)},
{ "TRUE", SYM(TRUE_SYM)},
{ "TRUNCATE", SYM(TRUNCATE_SYM)},
{ "TYPE", SYM(TYPE_SYM)},
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 49eb50be580..ea9b5c5bf7c 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1082,6 +1082,7 @@ extern const char **errmesg; /* Error messages */
extern const char *myisam_recover_options_str;
extern const char *in_left_expr_name, *in_additional_cond;
extern const char * const triggers_file_ext;
+extern const char * const trigname_file_ext;
extern Eq_creator eq_creator;
extern Ne_creator ne_creator;
extern Gt_creator gt_creator;
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 78d9af387da..bd8d81ed904 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -5722,6 +5722,7 @@ struct show_var_st status_vars[]= {
{"Com_show_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STATUS]), SHOW_LONG_STATUS},
{"Com_show_storage_engines", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STORAGE_ENGINES]), SHOW_LONG_STATUS},
{"Com_show_tables", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLES]), SHOW_LONG_STATUS},
+ {"Com_show_triggers", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TRIGGERS]), SHOW_LONG_STATUS},
{"Com_show_variables", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_VARIABLES]), SHOW_LONG_STATUS},
{"Com_show_warnings", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_WARNS]), SHOW_LONG_STATUS},
{"Com_slave_start", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SLAVE_START]), SHOW_LONG_STATUS},
diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt
index f999f17aedf..971061e22eb 100644
--- a/sql/share/errmsg.txt
+++ b/sql/share/errmsg.txt
@@ -5370,3 +5370,5 @@ ER_SCALE_BIGGER_THAN_PRECISION 42000 S1009
eng "Scale may not be larger than the precision (column '%-.64s')."
ER_WRONG_LOCK_OF_SYSTEM_TABLE
eng "You can't combine write-locking of system '%-.64s.%-.64s' table with other tables"
+ER_TRG_IN_WRONG_SCHEMA
+ eng "Trigger in wrong schema"
diff --git a/sql/sp.cc b/sql/sp.cc
index 55087f47f5e..a277c6bd253 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -1442,8 +1442,8 @@ sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
{
Sroutine_hash_entry **last_cached_routine_ptr=
(Sroutine_hash_entry **)lex->sroutines_list.next;
- for (int i= 0; i < 3; i++)
- for (int j= 0; j < 2; j++)
+ for (int i= 0; i < (int)TRG_EVENT_MAX; i++)
+ for (int j= 0; j < (int)TRG_ACTION_MAX; j++)
if (triggers->bodies[i][j])
{
(void)triggers->bodies[i][j]->add_used_tables_to_table_list(thd,
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 7021f61a052..576f5a503f0 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1740,7 +1740,7 @@ static int open_unireg_entry(THD *thd, TABLE *entry, const char *db,
!my_strcasecmp(system_charset_info, name, "proc"))
entry->s->system_table= 1;
- if (Table_triggers_list::check_n_load(thd, db, name, entry))
+ if (Table_triggers_list::check_n_load(thd, db, name, entry, 0))
goto err;
/*
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 45c8182a29c..4bba0c432c7 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -57,6 +57,7 @@ enum enum_sql_command {
SQLCOM_SHOW_PROCESSLIST, SQLCOM_SHOW_MASTER_STAT, SQLCOM_SHOW_SLAVE_STAT,
SQLCOM_SHOW_GRANTS, SQLCOM_SHOW_CREATE, SQLCOM_SHOW_CHARSETS,
SQLCOM_SHOW_COLLATIONS, SQLCOM_SHOW_CREATE_DB, SQLCOM_SHOW_TABLE_STATUS,
+ SQLCOM_SHOW_TRIGGERS,
SQLCOM_LOAD,SQLCOM_SET_OPTION,SQLCOM_LOCK_TABLES,SQLCOM_UNLOCK_TABLES,
SQLCOM_GRANT,
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 2e5cab4bb1c..4ef754d69bb 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2104,6 +2104,7 @@ int prepare_schema_table(THD *thd, LEX *lex, Table_ident *table_ident,
case SCH_TABLE_NAMES:
case SCH_TABLES:
case SCH_VIEWS:
+ case SCH_TRIGGERS:
#ifdef DONT_ALLOW_SHOW_COMMANDS
my_message(ER_NOT_ALLOWED_COMMAND,
ER(ER_NOT_ALLOWED_COMMAND), MYF(0)); /* purecov: inspected */
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 8343f9ec582..fd82529464f 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -21,6 +21,7 @@
#include "sql_select.h" // For select_describe
#include "repl_failsafe.h"
#include "sp_head.h"
+#include "sql_trigger.h"
#include <my_dir.h>
#ifdef HAVE_BERKELEY_DB
@@ -1696,6 +1697,7 @@ void get_index_field_values(LEX *lex, INDEX_FIELD_VALUES *index_field_values)
break;
case SQLCOM_SHOW_TABLES:
case SQLCOM_SHOW_TABLE_STATUS:
+ case SQLCOM_SHOW_TRIGGERS:
index_field_values->db_value= lex->current_select->db;
index_field_values->table_value= wild;
break;
@@ -2963,6 +2965,73 @@ static int get_schema_constraints_record(THD *thd, struct st_table_list *tables,
}
+static bool store_trigger(THD *thd, TABLE *table, const char *db,
+ const char *tname, LEX_STRING *trigger_name,
+ enum trg_event_type event,
+ enum trg_action_time_type timing,
+ LEX_STRING *trigger_stmt)
+{
+ CHARSET_INFO *cs= system_charset_info;
+ restore_record(table, s->default_values);
+ table->field[1]->store(db, strlen(db), cs);
+ table->field[2]->store(trigger_name->str, trigger_name->length, cs);
+ table->field[3]->store(trg_event_type_names[event].str,
+ trg_event_type_names[event].length, cs);
+ table->field[5]->store(db, strlen(db), cs);
+ table->field[6]->store(tname, strlen(tname), cs);
+ table->field[9]->store(trigger_stmt->str, trigger_stmt->length, cs);
+ table->field[10]->store("ROW", 3, cs);
+ table->field[11]->store(trg_action_time_type_names[timing].str,
+ trg_action_time_type_names[timing].length, cs);
+ table->field[14]->store("OLD", 3, cs);
+ table->field[15]->store("NEW", 3, cs);
+ return schema_table_store_record(thd, table);
+}
+
+
+static int get_schema_triggers_record(THD *thd, struct st_table_list *tables,
+ TABLE *table, bool res,
+ const char *base_name,
+ const char *file_name)
+{
+ DBUG_ENTER("get_schema_triggers_record");
+ /*
+ res can be non zero value when processed table is a view or
+ error happened during opening of processed table.
+ */
+ if (res)
+ {
+ if (!tables->view)
+ push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ thd->net.last_errno, thd->net.last_error);
+ thd->clear_error();
+ DBUG_RETURN(0);
+ }
+ if (!tables->view && tables->table->triggers)
+ {
+ Table_triggers_list *triggers= tables->table->triggers;
+ int event, timing;
+ for (event= 0; event < (int)TRG_EVENT_MAX; event++)
+ {
+ for (timing= 0; timing < (int)TRG_ACTION_MAX; timing++)
+ {
+ LEX_STRING trigger_name;
+ LEX_STRING trigger_stmt;
+ if (triggers->get_trigger_info(thd, (enum trg_event_type) event,
+ (enum trg_action_time_type)timing,
+ &trigger_name, &trigger_stmt))
+ continue;
+ if (store_trigger(thd, table, base_name, file_name, &trigger_name,
+ (enum trg_event_type) event,
+ (enum trg_action_time_type) timing, &trigger_stmt))
+ DBUG_RETURN(1);
+ }
+ }
+ }
+ DBUG_RETURN(0);
+}
+
+
void store_key_column_usage(TABLE *table, const char*db, const char *tname,
const char *key_name, uint key_len,
const char *con_type, uint con_len, longlong idx)
@@ -3847,6 +3916,29 @@ ST_FIELD_INFO open_tables_fields_info[]=
};
+ST_FIELD_INFO triggers_fields_info[]=
+{
+ {"TRIGGER_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 0},
+ {"TRIGGER_SCHEMA",NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
+ {"TRIGGER_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Trigger"},
+ {"EVENT_MANIPULATION", 6, MYSQL_TYPE_STRING, 0, 0, "Event"},
+ {"EVENT_OBJECT_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 0},
+ {"EVENT_OBJECT_SCHEMA",NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
+ {"EVENT_OBJECT_TABLE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Table"},
+ {"ACTION_ORDER", 4, MYSQL_TYPE_LONG, 0, 0, 0},
+ {"ACTION_CONDITION", 65535, MYSQL_TYPE_STRING, 0, 1, 0},
+ {"ACTION_STATEMENT", 65535, MYSQL_TYPE_STRING, 0, 0, "Statement"},
+ {"ACTION_ORIENTATION", 9, MYSQL_TYPE_STRING, 0, 0, 0},
+ {"ACTION_TIMING", 6, MYSQL_TYPE_STRING, 0, 0, "Timing"},
+ {"ACTION_REFERENCE_OLD_TABLE", NAME_LEN, MYSQL_TYPE_STRING, 0, 1, 0},
+ {"ACTION_REFERENCE_NEW_TABLE", NAME_LEN, MYSQL_TYPE_STRING, 0, 1, 0},
+ {"ACTION_REFERENCE_OLD_ROW", 3, MYSQL_TYPE_STRING, 0, 0, 0},
+ {"ACTION_REFERENCE_NEW_ROW", 3, MYSQL_TYPE_STRING, 0, 0, 0},
+ {"CREATED", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Created"},
+ {0, 0, MYSQL_TYPE_STRING, 0, 0, 0}
+};
+
+
ST_FIELD_INFO variables_fields_info[]=
{
{"Variable_name", 80, MYSQL_TYPE_STRING, 0, 0, "Variable_name"},
@@ -3897,6 +3989,8 @@ ST_SCHEMA_TABLE schema_tables[]=
fill_open_tables, make_old_format, 0, -1, -1, 1},
{"STATUS", variables_fields_info, create_schema_table, fill_status,
make_old_format, 0, -1, -1, 1},
+ {"TRIGGERS", triggers_fields_info, create_schema_table,
+ get_all_tables, make_old_format, get_schema_triggers_record, 5, 6, 0},
{"VARIABLES", variables_fields_info, create_schema_table, fill_variables,
make_old_format, 0, -1, -1, 1},
{0, 0, 0, 0, 0, 0, 0, 0, 0}
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 89282d9fcb9..e3f85f05c17 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -23,6 +23,8 @@
#include <hash.h>
#include <myisam.h>
#include <my_dir.h>
+#include "sp_head.h"
+#include "sql_trigger.h"
#ifdef __WIN__
#include <io.h>
@@ -290,16 +292,8 @@ int mysql_rm_table_part2(THD *thd, TABLE_LIST *tables, bool if_exists,
if (!(new_error=my_delete(path,MYF(MY_WME))))
{
some_tables_deleted=1;
- /*
- Destroy triggers for this table if there are any.
-
- We won't need this as soon as we will have new .FRM format,
- in which we will store trigger definitions in the same .FRM
- files as table descriptions.
- */
- strmov(end, triggers_file_ext);
- if (!access(path, F_OK))
- new_error= my_delete(path, MYF(MY_WME));
+ new_error= Table_triggers_list::drop_all_triggers(thd, db,
+ table->table_name);
}
error|= new_error;
}
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index fd79fc8b878..a7aee95197e 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -39,6 +39,45 @@ static File_option triggers_file_parameters[]=
/*
+ Structure representing contents of .TRN file which are used to support
+ database wide trigger namespace.
+*/
+
+struct st_trigname
+{
+ LEX_STRING trigger_table;
+};
+
+static const LEX_STRING trigname_file_type= {(char *)"TRIGGERNAME", 11};
+
+const char * const trigname_file_ext= ".TRN";
+
+static File_option trigname_file_parameters[]=
+{
+ {{(char*)"trigger_table", 15}, offsetof(struct st_trigname, trigger_table),
+ FILE_OPTIONS_ESTRING},
+ {{0, 0}, 0, FILE_OPTIONS_STRING}
+};
+
+
+const LEX_STRING trg_action_time_type_names[]=
+{
+ { (char *) STRING_WITH_LEN("BEFORE") },
+ { (char *) STRING_WITH_LEN("AFTER") }
+};
+
+const LEX_STRING trg_event_type_names[]=
+{
+ { (char *) STRING_WITH_LEN("INSERT") },
+ { (char *) STRING_WITH_LEN("UPDATE") },
+ { (char *) STRING_WITH_LEN("DELETE") }
+};
+
+
+static TABLE_LIST *add_table_for_trigger(THD *thd, sp_name *trig);
+
+
+/*
Create or drop trigger for table.
SYNOPSIS
@@ -69,6 +108,10 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create)
But do we want this ?
*/
+ if (!create &&
+ !(tables= add_table_for_trigger(thd, thd->lex->spname)))
+ DBUG_RETURN(TRUE);
+
/* We should have only one table in table list. */
DBUG_ASSERT(tables->next_global == 0);
@@ -174,28 +217,28 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables)
{
LEX *lex= thd->lex;
TABLE *table= tables->table;
- char dir_buff[FN_REFLEN], file_buff[FN_REFLEN];
- LEX_STRING dir, file;
+ char dir_buff[FN_REFLEN], file_buff[FN_REFLEN], trigname_buff[FN_REFLEN],
+ trigname_path[FN_REFLEN];
+ LEX_STRING dir, file, trigname_file;
LEX_STRING *trg_def, *name;
Item_trigger_field *trg_field;
- List_iterator_fast<LEX_STRING> it(names_list);
+ struct st_trigname trigname;
- /* We don't allow creation of several triggers of the same type yet */
- if (bodies[lex->trg_chistics.event][lex->trg_chistics.action_time])
+
+ /* Trigger must be in the same schema as target table. */
+ if (my_strcasecmp(system_charset_info, table->s->db,
+ lex->spname->m_db.str ? lex->spname->m_db.str :
+ thd->db))
{
- my_message(ER_TRG_ALREADY_EXISTS, ER(ER_TRG_ALREADY_EXISTS), MYF(0));
+ my_error(ER_TRG_IN_WRONG_SCHEMA, MYF(0));
return 1;
}
- /* Let us check if trigger with the same name exists */
- while ((name= it++))
+ /* We don't allow creation of several triggers of the same type yet */
+ if (bodies[lex->trg_chistics.event][lex->trg_chistics.action_time])
{
- if (my_strcasecmp(system_charset_info, lex->ident.str,
- name->str) == 0)
- {
- my_message(ER_TRG_ALREADY_EXISTS, ER(ER_TRG_ALREADY_EXISTS), MYF(0));
- return 1;
- }
+ my_message(ER_TRG_ALREADY_EXISTS, ER(ER_TRG_ALREADY_EXISTS), MYF(0));
+ return 1;
}
/*
@@ -234,6 +277,25 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables)
file.length= strxnmov(file_buff, FN_REFLEN, tables->table_name,
triggers_file_ext, NullS) - file_buff;
file.str= file_buff;
+ trigname_file.length= strxnmov(trigname_buff, FN_REFLEN,
+ lex->spname->m_name.str,
+ trigname_file_ext, NullS) - trigname_buff;
+ trigname_file.str= trigname_buff;
+ strxnmov(trigname_path, FN_REFLEN, dir_buff, trigname_buff, NullS);
+
+ /* Use the filesystem to enforce trigger namespace constraints. */
+ if (!access(trigname_path, F_OK))
+ {
+ my_error(ER_TRG_ALREADY_EXISTS, MYF(0));
+ return 1;
+ }
+
+ trigname.trigger_table.str= tables->table_name;
+ trigname.trigger_table.length= tables->table_name_length;
+
+ if (sql_create_definition_file(&dir, &trigname_file, &trigname_file_type,
+ (gptr)&trigname, trigname_file_parameters, 0))
+ return 1;
/*
Soon we will invalidate table object and thus Table_triggers_list object
@@ -246,13 +308,66 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables)
if (!(trg_def= (LEX_STRING *)alloc_root(&table->mem_root,
sizeof(LEX_STRING))) ||
definitions_list.push_back(trg_def, &table->mem_root))
- return 1;
+ goto err_with_cleanup;
trg_def->str= thd->query;
trg_def->length= thd->query_length;
- return sql_create_definition_file(&dir, &file, &triggers_file_type,
- (gptr)this, triggers_file_parameters, 3);
+ if (!sql_create_definition_file(&dir, &file, &triggers_file_type,
+ (gptr)this, triggers_file_parameters, 3))
+ return 0;
+
+err_with_cleanup:
+ my_delete(trigname_path, MYF(MY_WME));
+ return 1;
+}
+
+
+/*
+ Deletes the .TRG file for a table
+
+ SYNOPSIS
+ rm_trigger_file()
+ path - char buffer of size FN_REFLEN to be used
+ for constructing path to .TRG file.
+ db - table's database name
+ table_name - table's name
+
+ RETURN VALUE
+ False - success
+ True - error
+*/
+
+static bool rm_trigger_file(char *path, char *db, char *table_name)
+{
+ strxnmov(path, FN_REFLEN, mysql_data_home, "/", db, "/", table_name,
+ triggers_file_ext, NullS);
+ unpack_filename(path, path);
+ return my_delete(path, MYF(MY_WME));
+}
+
+
+/*
+ Deletes the .TRN file for a trigger
+
+ SYNOPSIS
+ rm_trigname_file()
+ path - char buffer of size FN_REFLEN to be used
+ for constructing path to .TRN file.
+ db - trigger's database name
+ table_name - trigger's name
+
+ RETURN VALUE
+ False - success
+ True - error
+*/
+
+static bool rm_trigname_file(char *path, char *db, char *trigger_name)
+{
+ strxnmov(path, FN_REFLEN, mysql_data_home, "/", db, "/", trigger_name,
+ trigname_file_ext, NullS);
+ unpack_filename(path, path);
+ return my_delete(path, MYF(MY_WME));
}
@@ -275,12 +390,13 @@ bool Table_triggers_list::drop_trigger(THD *thd, TABLE_LIST *tables)
LEX_STRING *name;
List_iterator_fast<LEX_STRING> it_name(names_list);
List_iterator<LEX_STRING> it_def(definitions_list);
+ char path[FN_REFLEN];
while ((name= it_name++))
{
it_def++;
- if (my_strcasecmp(system_charset_info, lex->ident.str,
+ if (my_strcasecmp(system_charset_info, lex->spname->m_name.str,
name->str) == 0)
{
/*
@@ -291,18 +407,14 @@ bool Table_triggers_list::drop_trigger(THD *thd, TABLE_LIST *tables)
if (definitions_list.is_empty())
{
- char path[FN_REFLEN];
-
/*
TODO: Probably instead of removing .TRG file we should move
to archive directory but this should be done as part of
parse_file.cc functionality (because we will need it
elsewhere).
*/
- strxnmov(path, FN_REFLEN, mysql_data_home, "/", tables->db, "/",
- tables->table_name, triggers_file_ext, NullS);
- unpack_filename(path, path);
- return my_delete(path, MYF(MY_WME));
+ if (rm_trigger_file(path, tables->db, tables->table_name))
+ return 1;
}
else
{
@@ -317,10 +429,15 @@ bool Table_triggers_list::drop_trigger(THD *thd, TABLE_LIST *tables)
triggers_file_ext, NullS) - file_buff;
file.str= file_buff;
- return sql_create_definition_file(&dir, &file, &triggers_file_type,
- (gptr)this,
- triggers_file_parameters, 3);
+ if (sql_create_definition_file(&dir, &file, &triggers_file_type,
+ (gptr)this, triggers_file_parameters,
+ 3))
+ return 1;
}
+
+ if (rm_trigname_file(path, tables->db, lex->spname->m_name.str))
+ return 1;
+ return 0;
}
}
@@ -331,8 +448,8 @@ bool Table_triggers_list::drop_trigger(THD *thd, TABLE_LIST *tables)
Table_triggers_list::~Table_triggers_list()
{
- for (int i= 0; i < 3; i++)
- for (int j= 0; j < 2; j++)
+ for (int i= 0; i < (int)TRG_EVENT_MAX; i++)
+ for (int j= 0; j < (int)TRG_ACTION_MAX; j++)
delete bodies[i][j];
if (record1_field)
@@ -389,13 +506,16 @@ bool Table_triggers_list::prepare_record1_accessors(TABLE *table)
db - table's database name
table_name - table's name
table - pointer to table object
+ names_only - stop after loading trigger names
RETURN VALUE
False - success
True - error
*/
+
bool Table_triggers_list::check_n_load(THD *thd, const char *db,
- const char *table_name, TABLE *table)
+ const char *table_name, TABLE *table,
+ bool names_only)
{
char path_buff[FN_REFLEN];
LEX_STRING path;
@@ -451,7 +571,7 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
TODO: This could be avoided if there is no triggers
for UPDATE and DELETE.
*/
- if (triggers->prepare_record1_accessors(table))
+ if (!names_only && triggers->prepare_record1_accessors(table))
DBUG_RETURN(1);
List_iterator_fast<LEX_STRING> it(triggers->definitions_list);
@@ -471,32 +591,20 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
Free lex associated resources
QQ: Do we really need all this stuff here ?
*/
- if (lex.sphead)
- {
- delete lex.sphead;
- lex.sphead= 0;
- }
+ delete lex.sphead;
goto err_with_lex_cleanup;
}
triggers->bodies[lex.trg_chistics.event]
[lex.trg_chistics.action_time]= lex.sphead;
- lex.sphead= 0;
-
- if (!(trg_name_buff= alloc_root(&table->mem_root,
- sizeof(LEX_STRING) +
- lex.ident.length + 1)))
- goto err_with_lex_cleanup;
-
- trg_name_str= (LEX_STRING *)trg_name_buff;
- trg_name_buff+= sizeof(LEX_STRING);
- memcpy(trg_name_buff, lex.ident.str,
- lex.ident.length + 1);
- trg_name_str->str= trg_name_buff;
- trg_name_str->length= lex.ident.length;
+ if (triggers->names_list.push_back(&lex.sphead->m_name, &table->mem_root))
+ goto err_with_lex_cleanup;
- if (triggers->names_list.push_back(trg_name_str, &table->mem_root))
- goto err_with_lex_cleanup;
+ if (names_only)
+ {
+ lex_end(&lex);
+ continue;
+ }
/*
Let us bind Item_trigger_field objects representing access to fields
@@ -537,3 +645,160 @@ err_with_lex_cleanup:
DBUG_RETURN(1);
}
+
+
+/*
+ Obtains and returns trigger metadata
+
+ SYNOPSIS
+ get_trigger_info()
+ thd - current thread context
+ event - trigger event type
+ time_type - trigger action time
+ name - returns name of trigger
+ stmt - returns statement of trigger
+
+ RETURN VALUE
+ False - success
+ True - error
+*/
+
+bool Table_triggers_list::get_trigger_info(THD *thd, trg_event_type event,
+ trg_action_time_type time_type,
+ LEX_STRING *trigger_name,
+ LEX_STRING *trigger_stmt)
+{
+ sp_head *body;
+ DBUG_ENTER("get_trigger_info");
+ if ((body= bodies[event][time_type]))
+ {
+ *trigger_name= body->m_name;
+ *trigger_stmt= body->m_body;
+ DBUG_RETURN(0);
+ }
+ DBUG_RETURN(1);
+}
+
+
+/*
+ Find trigger's table from trigger identifier and add it to
+ the statement table list.
+
+ SYNOPSIS
+ mysql_table_for_trigger()
+ thd - current thread context
+ trig - identifier for trigger
+
+ RETURN VALUE
+ 0 - error
+ # - pointer to TABLE_LIST object for the table
+*/
+
+static TABLE_LIST *add_table_for_trigger(THD *thd, sp_name *trig)
+{
+ const char *db= !trig->m_db.str ? thd->db : trig->m_db.str;
+ LEX *lex= thd->lex;
+ char path_buff[FN_REFLEN];
+ LEX_STRING path;
+ File_parser *parser;
+ struct st_trigname trigname;
+ DBUG_ENTER("add_table_for_trigger");
+
+ strxnmov(path_buff, FN_REFLEN, mysql_data_home, "/", db, "/",
+ trig->m_name.str, trigname_file_ext, NullS);
+ path.length= unpack_filename(path_buff, path_buff);
+ path.str= path_buff;
+
+ if (access(path_buff, F_OK))
+ {
+ my_error(ER_TRG_DOES_NOT_EXIST, MYF(0));
+ DBUG_RETURN(0);
+ }
+
+ if (!(parser= sql_parse_prepare(&path, thd->mem_root, 1)))
+ DBUG_RETURN(0);
+
+ if (strncmp(trigname_file_type.str, parser->type()->str,
+ parser->type()->length))
+ {
+ my_error(ER_WRONG_OBJECT, MYF(0), trig->m_name.str, trigname_file_ext,
+ "TRIGGERNAME");
+ DBUG_RETURN(0);
+ }
+
+ if (parser->parse((gptr)&trigname, thd->mem_root,
+ trigname_file_parameters, 1))
+ DBUG_RETURN(0);
+
+ /* We need to reset statement table list to be PS/SP friendly. */
+ lex->query_tables= 0;
+ lex->query_tables_last= &lex->query_tables;
+ DBUG_RETURN(sp_add_to_query_tables(thd, lex, db,
+ trigname.trigger_table.str, TL_WRITE));
+}
+
+
+/*
+ Drop all triggers for table.
+
+ SYNOPSIS
+ drop_all_triggers()
+ thd - current thread context
+ db - schema for table
+ name - name for table
+
+ NOTE
+ The calling thread should hold the LOCK_open mutex;
+
+ RETURN VALUE
+ False - success
+ True - error
+*/
+
+bool Table_triggers_list::drop_all_triggers(THD *thd, char *db, char *name)
+{
+ TABLE table;
+ char path[FN_REFLEN];
+ bool result= 0;
+ DBUG_ENTER("drop_all_triggers");
+
+ bzero(&table, sizeof(table));
+ init_alloc_root(&table.mem_root, 8192, 0);
+
+ safe_mutex_assert_owner(&LOCK_open);
+
+ if (Table_triggers_list::check_n_load(thd, db, name, &table, 1))
+ {
+ result= 1;
+ goto end;
+ }
+ if (table.triggers)
+ {
+ LEX_STRING *trigger;
+ List_iterator_fast<LEX_STRING> it_name(table.triggers->names_list);
+
+ while ((trigger= it_name++))
+ {
+ if (rm_trigname_file(path, db, trigger->str))
+ {
+ /*
+ Instead of immediately bailing out with error if we were unable
+ to remove .TRN file we will try to drop other files.
+ */
+ result= 1;
+ continue;
+ }
+ }
+
+ if (rm_trigger_file(path, db, name))
+ {
+ result= 1;
+ goto end;
+ }
+ }
+end:
+ if (table.triggers)
+ delete table.triggers;
+ free_root(&table.mem_root, MYF(0));
+ DBUG_RETURN(result);
+}
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index 044219d5ac9..e751741fa34 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -23,7 +23,7 @@
class Table_triggers_list: public Sql_alloc
{
/* Triggers as SPs grouped by event, action_time */
- sp_head *bodies[3][2];
+ sp_head *bodies[TRG_EVENT_MAX][TRG_ACTION_MAX];
/*
Copy of TABLE::Field array with field pointers set to TABLE::record[1]
buffer instead of TABLE::record[0] (used for OLD values in on UPDATE
@@ -121,9 +121,13 @@ public:
return res;
}
+ bool get_trigger_info(THD *thd, trg_event_type event,
+ trg_action_time_type time_type,
+ LEX_STRING *trigger_name, LEX_STRING *trigger_stmt);
static bool check_n_load(THD *thd, const char *db, const char *table_name,
- TABLE *table);
+ TABLE *table, bool names_only);
+ static bool drop_all_triggers(THD *thd, char *db, char *table_name);
bool has_delete_triggers()
{
@@ -143,3 +147,6 @@ public:
private:
bool prepare_record1_accessors(TABLE *table);
};
+
+extern const LEX_STRING trg_action_time_type_names[];
+extern const LEX_STRING trg_event_type_names[];
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 756a8a6ee3d..d1d8cf5305e 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -599,6 +599,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%token TRAILING
%token TRANSACTION_SYM
%token TRIGGER_SYM
+%token TRIGGERS_SYM
%token TRIM
%token TRUE_SYM
%token TRUNCATE_SYM
@@ -1266,7 +1267,7 @@ create:
}
opt_view_list AS select_init check_option
{}
- | CREATE TRIGGER_SYM ident trg_action_time trg_event
+ | CREATE TRIGGER_SYM sp_name trg_action_time trg_event
ON table_ident FOR_SYM EACH_SYM ROW_SYM
{
LEX *lex= Lex;
@@ -1285,6 +1286,7 @@ create:
sp->m_type= TYPE_ENUM_TRIGGER;
lex->sphead= sp;
+ lex->spname= $3;
/*
We have to turn of CLIENT_MULTI_QUERIES while parsing a
stored procedure, otherwise yylex will chop it into pieces
@@ -1295,7 +1297,7 @@ create:
bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics));
lex->sphead->m_chistics= &lex->sp_chistics;
- lex->sphead->m_body_begin= lex->tok_start;
+ lex->sphead->m_body_begin= lex->ptr;
}
sp_proc_stmt
{
@@ -1303,14 +1305,12 @@ create:
sp_head *sp= lex->sphead;
lex->sql_command= SQLCOM_CREATE_TRIGGER;
- sp->init_strings(YYTHD, lex, NULL);
+ sp->init_strings(YYTHD, lex, $3);
/* Restore flag if it was cleared above */
if (sp->m_old_cmq)
YYTHD->client_capabilities |= CLIENT_MULTI_QUERIES;
sp->restore_thd_mem_root(YYTHD);
- lex->ident= $3;
-
/*
We have to do it after parsing trigger body, because some of
sp_proc_stmt alternatives are not saving/restoring LEX, so
@@ -5919,19 +5919,11 @@ drop:
lex->sql_command= SQLCOM_DROP_VIEW;
lex->drop_if_exists= $3;
}
- | DROP TRIGGER_SYM ident '.' ident
+ | DROP TRIGGER_SYM sp_name
{
LEX *lex= Lex;
-
lex->sql_command= SQLCOM_DROP_TRIGGER;
- /* QQ: Could we loosen lock type in certain cases ? */
- if (!lex->select_lex.add_table_to_list(YYTHD,
- new Table_ident($3),
- (LEX_STRING*) 0,
- TL_OPTION_UPDATING,
- TL_WRITE))
- YYABORT;
- lex->ident= $5;
+ lex->spname= $3;
}
;
@@ -6296,6 +6288,15 @@ show_param:
if (prepare_schema_table(YYTHD, lex, 0, SCH_TABLE_NAMES))
YYABORT;
}
+ | opt_full TRIGGERS_SYM opt_db wild_and_where
+ {
+ LEX *lex= Lex;
+ lex->sql_command= SQLCOM_SELECT;
+ lex->orig_sql_command= SQLCOM_SHOW_TRIGGERS;
+ lex->select_lex.db= $3;
+ if (prepare_schema_table(YYTHD, lex, 0, SCH_TRIGGERS))
+ YYABORT;
+ }
| TABLE_SYM STATUS_SYM opt_db wild_and_where
{
LEX *lex= Lex;
@@ -7590,6 +7591,7 @@ keyword_sp:
| TEMPTABLE_SYM {}
| TEXT_SYM {}
| TRANSACTION_SYM {}
+ | TRIGGERS_SYM {}
| TIMESTAMP {}
| TIMESTAMP_ADD {}
| TIMESTAMP_DIFF {}
diff --git a/sql/table.h b/sql/table.h
index e5653a1f213..13d44766804 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -282,7 +282,7 @@ enum enum_schema_tables
SCH_COLLATION_CHARACTER_SET_APPLICABILITY, SCH_PROCEDURES, SCH_STATISTICS,
SCH_VIEWS, SCH_USER_PRIVILEGES, SCH_SCHEMA_PRIVILEGES, SCH_TABLE_PRIVILEGES,
SCH_COLUMN_PRIVILEGES, SCH_TABLE_CONSTRAINTS, SCH_KEY_COLUMN_USAGE,
- SCH_TABLE_NAMES, SCH_OPEN_TABLES, SCH_STATUS, SCH_VARIABLES
+ SCH_TABLE_NAMES, SCH_OPEN_TABLES, SCH_STATUS, SCH_TRIGGERS, SCH_VARIABLES
};
View Lorry Controller Status