maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #08147
MDEV-7286 TRIGGER: CREATE OR REPLACE, CREATE IF NOT EXISTS
Hi Serg,
Please review a patch for MDEV-7286.
Thanks.
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index ec70dba..e90dba0 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -2120,6 +2120,11 @@ CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
UPDATE A SET `pk`=1 WHERE `pk`=0 ;
END ;|
+ERROR HY000: Trigger already exists
+CREATE TRIGGER f1 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+UPDATE A SET `pk`=1 WHERE `pk`=0 ;
+END ;|
ERROR 42000: This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'
DROP TABLE t1;
DROP TABLE B;
diff --git a/mysql-test/r/create_drop_binlog.result b/mysql-test/r/create_drop_binlog.result
index 4b1db84..ad6c260 100644
--- a/mysql-test/r/create_drop_binlog.result
+++ b/mysql-test/r/create_drop_binlog.result
@@ -243,3 +243,40 @@ Log_name Pos Event_type Server_id End_log_pos Info
# # Gtid 1 # GTID #-#-#
# # Query 1 # use `test`; DROP USER IF EXISTS u1@localhost
RESET MASTER;
+CREATE TABLE t1 (a INT);
+CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=10;
+CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=11;
+DROP TRIGGER tr1;
+CREATE TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=20;
+CREATE TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=21;
+Warnings:
+Note 1359 Trigger already exists
+DROP TRIGGER IF EXISTS tr1;
+DROP TRIGGER IF EXISTS tr1;
+Warnings:
+Note 1360 Trigger does not exist
+DROP TABLE t1;
+SHOW BINLOG EVENTS;
+Log_name Pos Event_type Server_id End_log_pos Info
+# # Format_desc 1 # VER
+# # Gtid_list 1 # []
+# # Binlog_checkpoint 1 # master-bin.000001
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE TABLE t1 (a INT)
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE OR REPLACE DEFINER=`root`@`localhost` TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=10
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE OR REPLACE DEFINER=`root`@`localhost` TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=11
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP TRIGGER tr1
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=20
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=21
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP TRIGGER IF EXISTS tr1
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP TRIGGER IF EXISTS tr1
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP TABLE `t1` /* generated by server */
+RESET MASTER;
diff --git a/mysql-test/r/create_drop_trigger.result b/mysql-test/r/create_drop_trigger.result
new file mode 100644
index 0000000..a215838
--- /dev/null
+++ b/mysql-test/r/create_drop_trigger.result
@@ -0,0 +1,37 @@
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (val INT);
+CREATE TRIGGER IF NOT EXISTS val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val;
+SET @sum=0;
+INSERT INTO t1 VALUES (10), (20), (30);
+SELECT @sum;
+@sum
+60
+CREATE TRIGGER IF NOT EXISTS val_sum_new BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val;
+ERROR 42000: This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'
+CREATE TRIGGER IF NOT EXISTS val_sum AFTER INSERT ON t1 FOR EACH ROW SET @sum = @sum + 1 + NEW.val;
+Warnings:
+Note 1359 Trigger already exists
+CREATE OR REPLACE TRIGGER IF NOT EXISTS val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + 2 + NEW.val;
+ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT
+SET @sum = @sum + NEW.val
+CREATE OR REPLACE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + 3 + NEW.val;
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT
+SET @sum = @sum + 3 + NEW.val
+CREATE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + 4 + NEW.val;
+ERROR HY000: Trigger already exists
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT
+SET @sum = @sum + 3 + NEW.val
+# Clearing up
+DROP TRIGGER IF EXISTS val_sum;
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT
+DROP TRIGGER IF EXISTS val_sum;
+Warnings:
+Note 1360 Trigger does not exist
+DROP TABLE t1;
+DROP DATABASE db1;
diff --git a/mysql-test/r/create_or_replace.result b/mysql-test/r/create_or_replace.result
index ff8170b..87c6136 100644
--- a/mysql-test/r/create_or_replace.result
+++ b/mysql-test/r/create_or_replace.result
@@ -6,8 +6,6 @@ INSERT INTO t2 VALUES(1),(2),(3);
#
CREATE OR REPLACE TABLE IF NOT EXISTS t1 (a int);
ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
-create or replace trigger trg before insert on t1 for each row set @a:=1;
-ERROR HY000: Incorrect usage of OR REPLACE and TRIGGERS / SP / EVENT
create or replace table mysql.general_log (a int);
ERROR HY000: You cannot 'CREATE OR REPLACE' a log table if logging is enabled
create or replace table mysql.slow_log (a int);
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 8bf3176..e71090b 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -1957,6 +1957,8 @@ drop table if exists t1;
create table t1 (i int, j int);
create trigger t1_bi before insert on t1 for each row begin end;
create trigger t1_bi before insert on t1 for each row begin end;
+ERROR HY000: Trigger already exists
+create trigger t1_bi2 before insert on t1 for each row begin end;
ERROR 42000: This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'
drop trigger t1_bi;
drop trigger t1_bi;
diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_trigger.result b/mysql-test/suite/rpl/r/rpl_create_drop_trigger.result
new file mode 100644
index 0000000..8cb8061
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_create_drop_trigger.result
@@ -0,0 +1,48 @@
+include/master-slave.inc
+[connection master]
+# Part 1 - initial creation
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (val INT);
+CREATE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val + 1;
+SET @sum=0;
+INSERT INTO t1 VALUES (10), (20), (30);
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Master FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT_Master
+SET @sum = @sum + NEW.val + 1
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Slave FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT_Slave
+SET @sum = @sum + NEW.val + 1
+# Part 2 - CREATE IF NOT EXISTS (on a existing trigger)
+CREATE TRIGGER IF NOT EXISTS val_sum AFTER INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val + 2;
+Warnings:
+Note 1359 Trigger already exists
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Master FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT_Master
+SET @sum = @sum + NEW.val + 1
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Slave FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT_Slave
+SET @sum = @sum + NEW.val + 1
+# Part 3 - CREATE OR REPLACE (on a existing trigger)
+CREATE OR REPLACE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val + 3;
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Master FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT_Master
+SET @sum = @sum + NEW.val + 3
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Slave FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT_Slave
+SET @sum = @sum + NEW.val + 3
+# Clearing up
+DROP TRIGGER val_sum;
+DROP TABLE t1;
+DROP TRIGGER IF EXISTS val_sum;
+Warnings:
+Note 1360 Trigger does not exist
+DROP TRIGGER random_trigger;
+ERROR HY000: Trigger does not exist
+DROP DATABASE db1;
+DROP TRIGGER IF EXISTS val_sum;
+ERROR 3D000: No database selected
+# Syncing slave with master
+DROP TRIGGER val_sum;
+ERROR HY000: Trigger does not exist
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_create_drop_trigger.test b/mysql-test/suite/rpl/t/rpl_create_drop_trigger.test
new file mode 100644
index 0000000..568d4e2
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_create_drop_trigger.test
@@ -0,0 +1,48 @@
+--source include/master-slave.inc
+
+--echo # Part 1 - initial creation
+connection master;
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (val INT);
+CREATE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val + 1;
+SET @sum=0;
+INSERT INTO t1 VALUES (10), (20), (30);
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Master FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+sync_slave_with_master;
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Slave FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+
+--echo # Part 2 - CREATE IF NOT EXISTS (on a existing trigger)
+connection master;
+CREATE TRIGGER IF NOT EXISTS val_sum AFTER INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val + 2;
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Master FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+sync_slave_with_master;
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Slave FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+
+--echo # Part 3 - CREATE OR REPLACE (on a existing trigger)
+connection master;
+CREATE OR REPLACE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val + 3;
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Master FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+sync_slave_with_master;
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Slave FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+
+--echo # Clearing up
+connection master;
+DROP TRIGGER val_sum;
+DROP TABLE t1;
+DROP TRIGGER IF EXISTS val_sum;
+
+--error ER_TRG_DOES_NOT_EXIST
+DROP TRIGGER random_trigger;
+DROP DATABASE db1;
+
+--error ER_NO_DB_ERROR
+DROP TRIGGER IF EXISTS val_sum;
+
+--echo # Syncing slave with master
+sync_slave_with_master;
+
+--error ER_TRG_DOES_NOT_EXIST
+DROP TRIGGER val_sum;
+
+--source include/rpl_end.inc
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 4d57a5a..30c93f5 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -1622,12 +1622,18 @@ END ; |
INSERT INTO t1 (pk, int_key) SELECT `pk` , `int_key` FROM B ;
--delimiter |
---error ER_NOT_SUPPORTED_YET
+--error ER_TRG_ALREADY_EXISTS
CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
UPDATE A SET `pk`=1 WHERE `pk`=0 ;
END ;|
+--error ER_NOT_SUPPORTED_YET
+CREATE TRIGGER f1 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+ UPDATE A SET `pk`=1 WHERE `pk`=0 ;
+END ;|
+
--delimiter ;
DROP TABLE t1;
diff --git a/mysql-test/t/create_drop_binlog.test b/mysql-test/t/create_drop_binlog.test
index 5bcd783..e6f4853 100644
--- a/mysql-test/t/create_drop_binlog.test
+++ b/mysql-test/t/create_drop_binlog.test
@@ -119,3 +119,18 @@ DROP USER IF EXISTS u1@localhost;
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
SHOW BINLOG EVENTS;
RESET MASTER;
+
+
+CREATE TABLE t1 (a INT);
+CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=10;
+CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=11;
+DROP TRIGGER tr1;
+CREATE TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=20;
+CREATE TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=21;
+DROP TRIGGER IF EXISTS tr1;
+DROP TRIGGER IF EXISTS tr1;
+DROP TABLE t1;
+--replace_column 1 # 2 # 5 #
+--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
+SHOW BINLOG EVENTS;
+RESET MASTER;
diff --git a/mysql-test/t/create_drop_trigger.test b/mysql-test/t/create_drop_trigger.test
new file mode 100644
index 0000000..a8afc87
--- /dev/null
+++ b/mysql-test/t/create_drop_trigger.test
@@ -0,0 +1,31 @@
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (val INT);
+CREATE TRIGGER IF NOT EXISTS val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val;
+SET @sum=0;
+INSERT INTO t1 VALUES (10), (20), (30);
+SELECT @sum;
+
+--error ER_NOT_SUPPORTED_YET
+CREATE TRIGGER IF NOT EXISTS val_sum_new BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val;
+
+CREATE TRIGGER IF NOT EXISTS val_sum AFTER INSERT ON t1 FOR EACH ROW SET @sum = @sum + 1 + NEW.val;
+
+--error ER_WRONG_USAGE
+CREATE OR REPLACE TRIGGER IF NOT EXISTS val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + 2 + NEW.val;
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+
+CREATE OR REPLACE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + 3 + NEW.val;
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+
+--error ER_TRG_ALREADY_EXISTS
+CREATE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + 4 + NEW.val;
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+
+--echo # Clearing up
+DROP TRIGGER IF EXISTS val_sum;
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+DROP TRIGGER IF EXISTS val_sum;
+
+DROP TABLE t1;
+DROP DATABASE db1;
diff --git a/mysql-test/t/create_or_replace.test b/mysql-test/t/create_or_replace.test
index 9e37950..3e37910 100644
--- a/mysql-test/t/create_or_replace.test
+++ b/mysql-test/t/create_or_replace.test
@@ -21,8 +21,6 @@ INSERT INTO t2 VALUES(1),(2),(3);
--error ER_WRONG_USAGE
CREATE OR REPLACE TABLE IF NOT EXISTS t1 (a int);
---error ER_WRONG_USAGE
-create or replace trigger trg before insert on t1 for each row set @a:=1;
# check that we don't try to create a log table in use
--error ER_BAD_LOG_STATEMENT
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 4a1a3a6..384dd6c 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -2237,8 +2237,10 @@ drop table if exists t1;
create table t1 (i int, j int);
create trigger t1_bi before insert on t1 for each row begin end;
---error ER_NOT_SUPPORTED_YET
+--error ER_TRG_ALREADY_EXISTS
create trigger t1_bi before insert on t1 for each row begin end;
+--error ER_NOT_SUPPORTED_YET
+create trigger t1_bi2 before insert on t1 for each row begin end;
drop trigger t1_bi;
--error ER_TRG_DOES_NOT_EXIST
drop trigger t1_bi;
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 4b20813..d3713cc 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -608,6 +608,67 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create)
DBUG_RETURN(result);
}
+/**
+ Build stmt_query to write it in the bin-log
+ and get the trigger definer.
+
+ @param thd current thread context (including trigger definition in
+ LEX)
+ @param tables table list containing one open table for which the
+ trigger is created.
+ @param[out] stmt_query after successful return, this string contains
+ well-formed statement for creation this trigger.
+
+ @param[out] trg_definer The triggger definer.
+ @param[out] trg_definer_holder Used as a buffer for definer.
+
+ @note
+ - Assumes that trigger name is fully qualified.
+ - NULL-string means the following LEX_STRING instance:
+ { str = 0; length = 0 }.
+ - In other words, definer_user and definer_host should contain
+ simultaneously NULL-strings (non-SUID/old trigger) or valid strings
+ (SUID/new trigger).
+*/
+static void build_trig_stmt_query(THD *thd, TABLE_LIST *tables,
+ String *stmt_query,
+ LEX_STRING *trg_definer,
+ char trg_definer_holder[])
+{
+ LEX *lex= thd->lex;
+
+ /*
+ Create well-formed trigger definition query. Original query is not
+ appropriated, because definer-clause can be not truncated.
+ */
+ stmt_query->append(STRING_WITH_LEN("CREATE "));
+
+ if (lex->create_info.or_replace())
+ stmt_query->append(STRING_WITH_LEN("OR REPLACE "));
+
+ if (lex->sphead->m_chistics->suid != SP_IS_NOT_SUID)
+ {
+ /* SUID trigger */
+ lex->definer->set_lex_string(trg_definer, trg_definer_holder);
+ /*
+ Append definer-clause if the trigger is SUID (a usual trigger in
+ new MySQL versions).
+ */
+ append_definer(thd, stmt_query, &lex->definer->user, &lex->definer->host);
+ }
+ else
+ {
+ *trg_definer= empty_lex_str;
+ }
+
+ LEX_STRING stmt_definition;
+ stmt_definition.str= (char*) thd->lex->stmt_definition_begin;
+ stmt_definition.length= thd->lex->stmt_definition_end -
+ thd->lex->stmt_definition_begin;
+ trim_whitespace(thd->charset(), &stmt_definition);
+ stmt_query->append(stmt_definition.str, stmt_definition.length);
+}
+
/**
Create trigger for table.
@@ -640,8 +701,6 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
char file_buff[FN_REFLEN], trigname_buff[FN_REFLEN];
LEX_STRING file, trigname_file;
LEX_STRING *trg_def;
- LEX_STRING definer_user;
- LEX_STRING definer_host;
ulonglong *trg_sql_mode;
char trg_definer_holder[USER_HOST_BUFF_SIZE];
LEX_STRING *trg_definer;
@@ -650,6 +709,8 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
LEX_STRING *trg_client_cs_name;
LEX_STRING *trg_connection_cl_name;
LEX_STRING *trg_db_cl_name;
+ sp_head *trg_body= bodies[lex->trg_chistics.event]
+ [lex->trg_chistics.action_time];
if (check_for_broken_triggers())
return true;
@@ -659,20 +720,31 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
lex->spname->m_db.str))
{
my_error(ER_TRG_IN_WRONG_SCHEMA, MYF(0));
- return 1;
+ return true;
}
- /* We don't allow creation of several triggers of the same type yet */
- if (bodies[lex->trg_chistics.event][lex->trg_chistics.action_time] != 0)
+ /*
+ We don't allow creation of several triggers of the same type yet.
+ If a trigger with the same type already exists:
+ a. Throw a ER_NOT_SUPPORTED_YET error,
+ if the old and the new trigger names are different;
+ b. Or continue, if the old and the new trigger names are the same:
+ - either to recreate the trigger on "CREATE OR REPLACE"
+ - or send a "already exists" warning on "CREATE IF NOT EXISTS"
+ - or send an "alredy exists" error on normal CREATE.
+ */
+ if (trg_body != 0 &&
+ my_strcasecmp(table_alias_charset,
+ trg_body->m_name.str, lex->spname->m_name.str))
{
my_error(ER_NOT_SUPPORTED_YET, MYF(0),
"multiple triggers with the same action time"
" and event for one table");
- return 1;
+ return true;
}
if (sp_process_definer(thd))
- return 1;
+ return true;
/*
Let us check if all references to fields in old/new versions of row in
@@ -701,7 +773,7 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
if (!trg_field->fixed &&
trg_field->fix_fields(thd, (Item **)0))
- return 1;
+ return true;
}
/*
@@ -722,8 +794,29 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
/* Use the filesystem to enforce trigger namespace constraints. */
if (!access(trigname_buff, F_OK))
{
- my_error(ER_TRG_ALREADY_EXISTS, MYF(0));
- return 1;
+ if (lex->create_info.or_replace())
+ {
+ String drop_trg_query;
+ drop_trg_query.append("DROP TRIGGER ");
+ drop_trg_query.append(lex->spname->m_name.str);
+ if (drop_trigger(thd, tables, &drop_trg_query))
+ return 1;
+ }
+ else if (lex->create_info.if_not_exists())
+ {
+ push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+ ER_TRG_ALREADY_EXISTS, ER(ER_TRG_ALREADY_EXISTS),
+ trigname_buff);
+ LEX_STRING trg_definer_tmp;
+ build_trig_stmt_query(thd, tables, stmt_query,
+ &trg_definer_tmp, trg_definer_holder);
+ return false;
+ }
+ else
+ {
+ my_error(ER_TRG_ALREADY_EXISTS, MYF(0));
+ return true;
+ }
}
trigname.trigger_table.str= tables->table_name;
@@ -731,7 +824,7 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
if (sql_create_definition_file(NULL, &trigname_file, &trigname_file_type,
(uchar*)&trigname, trigname_file_parameters))
- return 1;
+ return true;
/*
Soon we will invalidate table object and thus Table_triggers_list object
@@ -764,29 +857,6 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
*trg_sql_mode= thd->variables.sql_mode;
- if (lex->sphead->m_chistics->suid != SP_IS_NOT_SUID)
- {
- /* SUID trigger. */
-
- definer_user= lex->definer->user;
- definer_host= lex->definer->host;
-
- lex->definer->set_lex_string(trg_definer, trg_definer_holder);
- }
- else
- {
- /* non-SUID trigger. */
-
- definer_user.str= 0;
- definer_user.length= 0;
-
- definer_host.str= 0;
- definer_host.length= 0;
-
- trg_definer->str= (char*) "";
- trg_definer->length= 0;
- }
-
/*
Fill character set information:
- client character set contains charset info only;
@@ -802,30 +872,8 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
lex_string_set(trg_db_cl_name,
get_default_db_collation(thd, tables->db)->name);
- /*
- Create well-formed trigger definition query. Original query is not
- appropriated, because definer-clause can be not truncated.
- */
-
- stmt_query->append(STRING_WITH_LEN("CREATE "));
-
- if (lex->sphead->m_chistics->suid != SP_IS_NOT_SUID)
- {
- /*
- Append definer-clause if the trigger is SUID (a usual trigger in
- new MySQL versions).
- */
-
- append_definer(thd, stmt_query, &definer_user, &definer_host);
- }
-
- LEX_STRING stmt_definition;
- stmt_definition.str= (char*) thd->lex->stmt_definition_begin;
- stmt_definition.length= thd->lex->stmt_definition_end
- - thd->lex->stmt_definition_begin;
- trim_whitespace(thd->charset(), & stmt_definition);
-
- stmt_query->append(stmt_definition.str, stmt_definition.length);
+ build_trig_stmt_query(thd, tables, stmt_query,
+ trg_definer, trg_definer_holder);
trg_def->str= stmt_query->c_ptr_safe();
trg_def->length= stmt_query->length();
@@ -834,11 +882,11 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
if (!sql_create_definition_file(NULL, &file, &triggers_file_type,
(uchar*)this, triggers_file_parameters))
- return 0;
+ return false;
err_with_cleanup:
mysql_file_delete(key_file_trn, trigname_buff, MYF(MY_WME));
- return 1;
+ return true;
}
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 8025340..6ccc294 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -2563,6 +2563,7 @@ create:
{
// TODO: remove this when "MDEV-5359 CREATE OR REPLACE..." is done
if ($1.or_replace() &&
+ Lex->sql_command != SQLCOM_CREATE_TRIGGER &&
Lex->sql_command != SQLCOM_CREATE_VIEW &&
Lex->sql_command != SQLCOM_CREATE_FUNCTION &&
Lex->sql_command != SQLCOM_CREATE_SPFUNCTION &&
@@ -16157,23 +16158,28 @@ view_check_option:
trigger_tail:
TRIGGER_SYM
remember_name
+ opt_if_not_exists
+ {
+ if (Lex->add_create_options_with_check($3))
+ MYSQL_YYABORT;
+ }
sp_name
trg_action_time
trg_event
ON
- remember_name /* $7 */
- { /* $8 */
+ remember_name /* $9 */
+ { /* $10 */
Lex->raw_trg_on_table_name_begin= YYLIP->get_tok_start();
}
- table_ident /* $9 */
+ table_ident /* $11 */
FOR_SYM
- remember_name /* $11 */
- { /* $12 */
+ remember_name /* $13 */
+ { /* $14 */
Lex->raw_trg_on_table_name_end= YYLIP->get_tok_start();
}
EACH_SYM
ROW_SYM
- { /* $15 */
+ { /* $17 */
LEX *lex= thd->lex;
Lex_input_stream *lip= YYLIP;
@@ -16184,17 +16190,17 @@ trigger_tail:
}
lex->stmt_definition_begin= $2;
- lex->ident.str= $7;
- lex->ident.length= $11 - $7;
- lex->spname= $3;
+ lex->ident.str= $9;
+ lex->ident.length= $13 - $9;
+ lex->spname= $5;
- if (!make_sp_head(thd, $3, TYPE_ENUM_TRIGGER))
+ if (!make_sp_head(thd, $5, TYPE_ENUM_TRIGGER))
MYSQL_YYABORT;
lex->sphead->set_body_start(thd, lip->get_cpp_ptr());
}
- sp_proc_stmt /* $16 */
- { /* $17 */
+ sp_proc_stmt /* $18 */
+ { /* $19 */
LEX *lex= Lex;
sp_head *sp= lex->sphead;
@@ -16210,7 +16216,7 @@ trigger_tail:
sp_proc_stmt alternatives are not saving/restoring LEX, so
lex->query_tables can be wiped out.
*/
- if (!lex->select_lex.add_table_to_list(thd, $9,
+ if (!lex->select_lex.add_table_to_list(thd, $11,
(LEX_STRING*) 0,
TL_OPTION_UPDATING,
TL_READ_NO_INSERT,
Follow ups
