List:Internals« Previous MessageNext Message »
From:Marc Alff Date:June 28 2006 11:39pm
Subject:Contribution for Bug#18161 (show create trigger / drop trigger if
exist / show trigger code)
View as plain text  
Hi All

Please find attached a contribution, as a proposed implementation for bug
#18161 (SHOW CREATE TRIGGER, SHOW TRIGGER STATUS, DROP TRIGGER IF EXIST).

Functionality changed by this patch :
- the "drop trigger" syntax can now have an "if exists" clause (as
reported),
- a new command "show create trigger <name>" is implemented (as reported),
- a new command "show trigger code <name> is also implemented,
  and available in DEBUG builds (extra, was not reported in Bug#18161)

Also, note that the proposed command "show trigger status" is not
implemented,
as the existing "show triggers <wild>" command in fact already provides all
the information needed.

What was done :

- Lot of analysis, and learning of the code base so that the change should
"fit" in the existing architecture.

- The patch itself is up to date with the latest code publicly available
(bk://mysql.bkbits.net/mysql-5.1 as of 2006-06-28).
As such, it's aligned with the recent code cleanup done for show commands.

- The code complies (I hope) with the coding styles guidelines found
in the internal documentation.

- The implementation uses internally the "information_schema.TRIGGERS"
table.
As such, it's fairly independent of how triggers are physically represented.

- Minor fixes in the "show status" command, where commands were missing.

- Minor renaming of constants (to fit the general naming scheme) :
    SQLCOM_SHOW_STATUS_PROC --> SQLCOM_SHOW_PROC_STATUS
    SQLCOM_SHOW_STATUS_FUNC --> SQLCOM_SHOW_FUNC_STATUS

- New test cases, in the mysqltest format, are provided.

- Build and tests on Linux Gentoo / AMD64.

- All the tests involving triggers have been reviewed to evaluate
if existing 'drop trigger' statements should be replaced by
'drop trigger if exist'.
Since all the times, the drop is just after a create trigger,
no changes are needed.

- Running the basic tests (mysql-test-run.pl --force) :
Failed 1/541 tests, 99.82% were successful.
The failed test is 'federated', which is an unrelated issue (see Bug#20326).

- New files are GPL'ed MySQL AB. Please contact me is paperwork is needed.

What needs to be done :

- Code review

- Cross platform build (I don't expect any surprises).

- Run the test cases with Purify (I would, but don't have access to it).

- If the patch is approved, there is some documentation impact
(MySQL manual, Chapter 21).

As this is my very first patch on MySQL, I did my best but certainly
missed things.
Please send me any question / comments / concerns; I will make
adjustments to the code as needed.

Regards,
Marc Alff.



diff -Naur mysql-5.1-BK/libmysqld/Makefile.am mysql-5.1-trig/libmysqld/Makefile.am
--- mysql-5.1-BK/libmysqld/Makefile.am	2006-06-27 16:16:01.000000000 +0000
+++ mysql-5.1-trig/libmysqld/Makefile.am	2006-06-28 22:29:32.000000000 +0000
@@ -71,7 +71,7 @@
 	event_scheduler.cc events.cc event_timed.cc \
 	rpl_filter.cc sql_partition.cc sql_builtin.cc sql_plugin.cc \
 	sql_tablespace.cc \
-	rpl_injector.cc my_user.c partition_info.cc
+	rpl_injector.cc my_user.c partition_info.cc sql_show_trigger.cc
 
 libmysqld_int_a_SOURCES= $(libmysqld_sources) $(libmysqlsources) $(sqlsources)
 libmysqld_a_SOURCES=
diff -Naur mysql-5.1-BK/mysql-test/r/trigger-51.result
mysql-5.1-trig/mysql-test/r/trigger-51.result
--- mysql-5.1-BK/mysql-test/r/trigger-51.result	1970-01-01 00:00:00.000000000 +0000
+++ mysql-5.1-trig/mysql-test/r/trigger-51.result	2006-06-28 22:29:31.000000000 +0000
@@ -0,0 +1,126 @@
+drop trigger if exists empty;
+drop trigger if exists t1_bi;
+drop trigger if exists t1_ai;
+drop trigger if exists t1_bu;
+drop trigger if exists t1_au;
+drop trigger if exists t1_bd;
+drop trigger if exists t1_ad;
+drop trigger if exists t2_code;
+drop table if exists t1;
+drop table if exists t2;
+create table t1 (i int);
+create table t2 (i int);
+show create trigger not_a_trigger;
+ERROR HY000: Trigger does not exist
+drop trigger not_a_trigger;
+ERROR HY000: Trigger does not exist
+drop trigger if exists not_a_trigger;
+Warnings:
+Note	1360	Trigger does not exist
+create trigger empty before insert on t2
+for each row
+begin
+end;
+show create trigger empty;
+Db	Name	Create Trigger
+test	empty	CREATE DEFINER = 'root'@'localhost' TRIGGER `test`.`empty` BEFORE INSERT ON
`test`.`t2` FOR EACH ROW begin
+end
+drop trigger if exists empty;
+show create trigger empty;
+ERROR HY000: Trigger does not exist
+create trigger t1_bi before insert on t1
+for each row
+begin
+set @was_in := "t1_bi";
+end
+//
+show create trigger t1_bi //
+Db	Name	Create Trigger
+test	t1_bi	CREATE DEFINER = 'root'@'localhost' TRIGGER `test`.`t1_bi` BEFORE INSERT ON
`test`.`t1` FOR EACH ROW begin
+set @was_in := "t1_bi";
+end
+create trigger t1_ai after insert on t1
+for each row
+begin
+set @was_in := "t1_ai";
+end
+//
+show create trigger t1_ai //
+Db	Name	Create Trigger
+test	t1_ai	CREATE DEFINER = 'root'@'localhost' TRIGGER `test`.`t1_ai` AFTER INSERT ON
`test`.`t1` FOR EACH ROW begin
+set @was_in := "t1_ai";
+end
+create trigger t1_bu before update on t1
+for each row
+begin
+set @was_in := "t1_bu";
+end
+//
+show create trigger t1_bu //
+Db	Name	Create Trigger
+test	t1_bu	CREATE DEFINER = 'root'@'localhost' TRIGGER `test`.`t1_bu` BEFORE UPDATE ON
`test`.`t1` FOR EACH ROW begin
+set @was_in := "t1_bu";
+end
+create trigger t1_au after update on t1
+for each row
+begin
+set @was_in := "t1_au";
+end
+//
+show create trigger t1_au //
+Db	Name	Create Trigger
+test	t1_au	CREATE DEFINER = 'root'@'localhost' TRIGGER `test`.`t1_au` AFTER UPDATE ON
`test`.`t1` FOR EACH ROW begin
+set @was_in := "t1_au";
+end
+create trigger t1_bd before delete on t1
+for each row
+begin
+set @was_in := "t1_bd";
+end
+//
+show create trigger t1_bd //
+Db	Name	Create Trigger
+test	t1_bd	CREATE DEFINER = 'root'@'localhost' TRIGGER `test`.`t1_bd` BEFORE DELETE ON
`test`.`t1` FOR EACH ROW begin
+set @was_in := "t1_bd";
+end
+create trigger t1_ad after delete on t1
+for each row
+begin
+set @was_in := "t1_ad";
+end
+//
+show create trigger t1_ad //
+Db	Name	Create Trigger
+test	t1_ad	CREATE DEFINER = 'root'@'localhost' TRIGGER `test`.`t1_ad` AFTER DELETE ON
`test`.`t1` FOR EACH ROW begin
+set @was_in := "t1_ad";
+end
+create trigger t2_code before update on t2
+for each row
+begin
+if (old.i = 1)
+then
+set new.i = 2 + new.i;
+else
+set new.i = 3 + new.i;
+end if;
+end
+//
+show create trigger t2_code //
+Db	Name	Create Trigger
+test	t2_code	CREATE DEFINER = 'root'@'localhost' TRIGGER `test`.`t2_code` BEFORE UPDATE
ON `test`.`t2` FOR EACH ROW begin
+if (old.i = 1)
+then
+set new.i = 2 + new.i;
+else
+set new.i = 3 + new.i;
+end if;
+end
+drop trigger t1_bi;
+drop trigger t1_ai;
+drop trigger t1_bu;
+drop trigger t1_au;
+drop trigger t1_bd;
+drop trigger t1_ad;
+drop trigger t2_code;
+drop table t1;
+drop table t2;
diff -Naur mysql-5.1-BK/mysql-test/r/trigger-code.result
mysql-5.1-trig/mysql-test/r/trigger-code.result
--- mysql-5.1-BK/mysql-test/r/trigger-code.result	1970-01-01 00:00:00.000000000 +0000
+++ mysql-5.1-trig/mysql-test/r/trigger-code.result	2006-06-28 22:29:31.000000000 +0000
@@ -0,0 +1,101 @@
+drop trigger if exists empty;
+drop trigger if exists t1_bi;
+drop trigger if exists t1_ai;
+drop trigger if exists t1_bu;
+drop trigger if exists t1_au;
+drop trigger if exists t1_bd;
+drop trigger if exists t1_ad;
+drop trigger if exists t2_code;
+drop table if exists t1;
+drop table if exists t2;
+create table t1 (i int);
+create table t2 (i int);
+show trigger code not_a_trigger;
+ERROR HY000: Trigger does not exist
+create trigger empty before insert on t2
+for each row
+begin
+end;
+show trigger code empty;
+Pos	Instruction
+create trigger t1_bi before insert on t1
+for each row
+begin
+set @was_in := "t1_bi";
+end
+//
+show trigger code t1_bi //
+Pos	Instruction
+0	stmt 31 "SET @was_in := "t1_bi""
+create trigger t1_ai after insert on t1
+for each row
+begin
+set @was_in := "t1_ai";
+end
+//
+show trigger code t1_ai //
+Pos	Instruction
+0	stmt 31 "SET @was_in := "t1_ai""
+create trigger t1_bu before update on t1
+for each row
+begin
+set @was_in := "t1_bu";
+end
+//
+show trigger code t1_bu //
+Pos	Instruction
+0	stmt 31 "SET @was_in := "t1_bu""
+create trigger t1_au after update on t1
+for each row
+begin
+set @was_in := "t1_au";
+end
+//
+show trigger code t1_au //
+Pos	Instruction
+0	stmt 31 "SET @was_in := "t1_au""
+create trigger t1_bd before delete on t1
+for each row
+begin
+set @was_in := "t1_bd";
+end
+//
+show trigger code t1_bd //
+Pos	Instruction
+0	stmt 31 "SET @was_in := "t1_bd""
+create trigger t1_ad after delete on t1
+for each row
+begin
+set @was_in := "t1_ad";
+end
+//
+show trigger code t1_ad //
+Pos	Instruction
+0	stmt 31 "SET @was_in := "t1_ad""
+create trigger t2_code before update on t2
+for each row
+begin
+if (old.i = 1)
+then
+set new.i = 2 + new.i;
+else
+set new.i = 3 + new.i;
+end if;
+end
+//
+show trigger code t2_code //
+Pos	Instruction
+0	jump_if_not 3(4) (OLD.i = 1)
+1	set_trigger_field NEW.i:=(2 + NEW.i)
+2	jump 4
+3	set_trigger_field NEW.i:=(3 + NEW.i)
+drop trigger empty;
+drop trigger t1_bi;
+drop trigger t1_ai;
+drop trigger t1_bu;
+drop trigger t1_au;
+drop trigger t1_bd;
+drop trigger t1_ad;
+drop trigger t2_code;
+drop table t1;
+drop table t2;
diff -Naur mysql-5.1-BK/mysql-test/t/trigger-51.test
mysql-5.1-trig/mysql-test/t/trigger-51.test
--- mysql-5.1-BK/mysql-test/t/trigger-51.test	1970-01-01 00:00:00.000000000 +0000
+++ mysql-5.1-trig/mysql-test/t/trigger-51.test	2006-06-28 22:29:30.000000000 +0000
@@ -0,0 +1,152 @@
+##============================================================================
+## This file is part of MySQL.
+## Copyright (c) 2006, MySQL AB.
+##
+## This program is free software; you can redistribute it and/or
+## modify it under the terms of the GNU General Public License
+## as published by the Free Software Foundation; either version 2
+## of the License, or (at your option) any later version.
+##
+## This program is distributed in the hope that it will be useful,
+## but WITHOUT ANY WARRANTY; without even the implied warranty of
+## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+## GNU General Public License for more details.
+##
+## You should have received a copy of the GNU General Public License
+## along with this program; if not, write to the Free Software
+## Foundation, Inc., 51 Franklin Street, Fifth Floor,
+## Boston, MA 02110-1301, USA.
+##============================================================================
+
+## Written by Marc Alff.
+
+# Test the features:
+# - "show create trigger <name>" 
+# - "drop trigger if exists <name>"
+# which have been introduced in 5.1.11
+
+--disable_warnings
+drop trigger if exists empty;
+drop trigger if exists t1_bi;
+drop trigger if exists t1_ai;
+drop trigger if exists t1_bu;
+drop trigger if exists t1_au;
+drop trigger if exists t1_bd;
+drop trigger if exists t1_ad;
+drop trigger if exists t2_code;
+drop table if exists t1;
+drop table if exists t2;
+--enable_warnings
+
+create table t1 (i int);
+create table t2 (i int);
+
+# trigger do not exist
+
+-- error ER_TRG_DOES_NOT_EXIST
+show create trigger not_a_trigger;
+
+-- error ER_TRG_DOES_NOT_EXIST
+drop trigger not_a_trigger;
+
+drop trigger if exists not_a_trigger;
+
+# trigger does exist
+
+create trigger empty before insert on t2
+for each row
+begin
+end;
+
+show create trigger empty;
+drop trigger if exists empty;
+
+-- error ER_TRG_DOES_NOT_EXIST
+show create trigger empty;
+
+delimiter //;
+
+# trigger for each action/timing combinations
+
+create trigger t1_bi before insert on t1
+for each row
+begin
+  set @was_in := "t1_bi";
+end
+//
+
+show create trigger t1_bi //
+
+create trigger t1_ai after insert on t1
+for each row
+begin
+  set @was_in := "t1_ai";
+end
+//
+
+show create trigger t1_ai //
+
+create trigger t1_bu before update on t1
+for each row
+begin
+  set @was_in := "t1_bu";
+end
+//
+
+show create trigger t1_bu //
+
+create trigger t1_au after update on t1
+for each row
+begin
+  set @was_in := "t1_au";
+end
+//
+
+show create trigger t1_au //
+
+create trigger t1_bd before delete on t1
+for each row
+begin
+  set @was_in := "t1_bd";
+end
+//
+
+show create trigger t1_bd //
+
+create trigger t1_ad after delete on t1
+for each row
+begin
+  set @was_in := "t1_ad";
+end
+//
+
+show create trigger t1_ad //
+
+# trigger using old and new variables
+
+create trigger t2_code before update on t2
+for each row
+begin
+  if (old.i = 1)
+  then
+    set new.i = 2 + new.i;
+  else
+    set new.i = 3 + new.i;
+  end if;
+end
+//
+
+show create trigger t2_code //
+
+delimiter ;//
+
+drop trigger t1_bi;
+drop trigger t1_ai;
+drop trigger t1_bu;
+drop trigger t1_au;
+drop trigger t1_bd;
+drop trigger t1_ad;
+drop trigger t2_code;
+drop table t1;
+drop table t2;
+
diff -Naur mysql-5.1-BK/mysql-test/t/trigger-code.test
mysql-5.1-trig/mysql-test/t/trigger-code.test
--- mysql-5.1-BK/mysql-test/t/trigger-code.test	1970-01-01 00:00:00.000000000 +0000
+++ mysql-5.1-trig/mysql-test/t/trigger-code.test	2006-06-28 22:29:30.000000000 +0000
@@ -0,0 +1,145 @@
+##============================================================================
+## This file is part of MySQL.
+## Copyright (c) 2006, MySQL AB.
+##
+## This program is free software; you can redistribute it and/or
+## modify it under the terms of the GNU General Public License
+## as published by the Free Software Foundation; either version 2
+## of the License, or (at your option) any later version.
+##
+## This program is distributed in the hope that it will be useful,
+## but WITHOUT ANY WARRANTY; without even the implied warranty of
+## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+## GNU General Public License for more details.
+##
+## You should have received a copy of the GNU General Public License
+## along with this program; if not, write to the Free Software
+## Foundation, Inc., 51 Franklin Street, Fifth Floor,
+## Boston, MA 02110-1301, USA.
+##============================================================================
+
+## Written by Marc Alff.
+
+#
+# Test the debugging feature "show trigger code <name>" 
+#
+
+-- source include/is_debug_build.inc
+
+--disable_warnings
+drop trigger if exists empty;
+drop trigger if exists t1_bi;
+drop trigger if exists t1_ai;
+drop trigger if exists t1_bu;
+drop trigger if exists t1_au;
+drop trigger if exists t1_bd;
+drop trigger if exists t1_ad;
+drop trigger if exists t2_code;
+drop table if exists t1;
+drop table if exists t2;
+--enable_warnings
+
+create table t1 (i int);
+create table t2 (i int);
+
+# trigger do not exist
+
+-- error ER_TRG_DOES_NOT_EXIST
+show trigger code not_a_trigger;
+
+create trigger empty before insert on t2
+for each row
+begin
+end;
+
+# trigger has no code
+
+show trigger code empty;
+
+delimiter //;
+
+# trigger for each action/timing combinations
+
+create trigger t1_bi before insert on t1
+for each row
+begin
+  set @was_in := "t1_bi";
+end
+//
+
+show trigger code t1_bi //
+
+create trigger t1_ai after insert on t1
+for each row
+begin
+  set @was_in := "t1_ai";
+end
+//
+
+show trigger code t1_ai //
+
+create trigger t1_bu before update on t1
+for each row
+begin
+  set @was_in := "t1_bu";
+end
+//
+
+show trigger code t1_bu //
+
+create trigger t1_au after update on t1
+for each row
+begin
+  set @was_in := "t1_au";
+end
+//
+
+show trigger code t1_au //
+
+create trigger t1_bd before delete on t1
+for each row
+begin
+  set @was_in := "t1_bd";
+end
+//
+
+show trigger code t1_bd //
+
+create trigger t1_ad after delete on t1
+for each row
+begin
+  set @was_in := "t1_ad";
+end
+//
+
+show trigger code t1_ad //
+
+# trigger using new and old variables
+
+create trigger t2_code before update on t2
+for each row
+begin
+  if (old.i = 1)
+  then
+    set new.i = 2 + new.i;
+  else
+    set new.i = 3 + new.i;
+  end if;
+end
+//
+
+show trigger code t2_code //
+
+delimiter ;//
+
+drop trigger empty;
+drop trigger t1_bi;
+drop trigger t1_ai;
+drop trigger t1_bu;
+drop trigger t1_au;
+drop trigger t1_bd;
+drop trigger t1_ad;
+drop trigger t2_code;
+drop table t1;
+drop table t2;
+
diff -Naur mysql-5.1-BK/sql/Makefile.am mysql-5.1-trig/sql/Makefile.am
--- mysql-5.1-BK/sql/Makefile.am	2006-06-27 16:16:01.000000000 +0000
+++ mysql-5.1-trig/sql/Makefile.am	2006-06-28 22:29:31.000000000 +0000
@@ -67,7 +67,7 @@
 			sql_array.h sql_cursor.h events.h events_priv.h \
 			sql_plugin.h authors.h sql_partition.h event_timed.h \
 			partition_info.h partition_element.h event_scheduler.h \
-			contributors.h
+			contributors.h sql_show_trigger.h sql_schema.h
 mysqld_SOURCES =	sql_lex.cc sql_handler.cc sql_partition.cc \
 			item.cc item_sum.cc item_buff.cc item_func.cc \
 			item_cmpfunc.cc item_strfunc.cc item_timefunc.cc \
@@ -106,7 +106,8 @@
 			sp_cache.cc parse_file.cc sql_trigger.cc \
                         event_scheduler.cc events.cc event_timed.cc \
 			sql_plugin.cc sql_binlog.cc \
-			sql_builtin.cc sql_tablespace.cc partition_info.cc
+			sql_builtin.cc sql_tablespace.cc partition_info.cc \
+			sql_show_trigger.cc
 
 
 gen_lex_hash_SOURCES =	gen_lex_hash.cc
diff -Naur mysql-5.1-BK/sql/mysqld.cc mysql-5.1-trig/sql/mysqld.cc
--- mysql-5.1-BK/sql/mysqld.cc	2006-06-27 16:16:01.000000000 +0000
+++ mysql-5.1-trig/sql/mysqld.cc	2006-06-28 22:29:32.000000000 +0000
@@ -6716,7 +6716,10 @@
   {"Com_show_column_types",    (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_COLUMN_TYPES]), SHOW_LONG_STATUS},
   {"Com_show_create_db",       (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_CREATE_DB]), SHOW_LONG_STATUS},
   {"Com_show_create_event",    (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_CREATE_EVENT]), SHOW_LONG_STATUS},
+  {"Com_show_create_function", (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_CREATE_FUNC]), SHOW_LONG_STATUS},
+  {"Com_show_create_procedure",(char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_CREATE_PROC]), SHOW_LONG_STATUS},
   {"Com_show_create_table",    (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_CREATE]), SHOW_LONG_STATUS},
+  {"Com_show_create_trigger",  (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_CREATE_TRIG]), SHOW_LONG_STATUS},
   {"Com_show_databases",       (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_DATABASES]), SHOW_LONG_STATUS},
   {"Com_show_engine_logs",     (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_ENGINE_LOGS]), SHOW_LONG_STATUS},
   {"Com_show_engine_mutex",    (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_ENGINE_MUTEX]), SHOW_LONG_STATUS},
@@ -6724,6 +6727,7 @@
   {"Com_show_events",          (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_EVENTS]), SHOW_LONG_STATUS},
   {"Com_show_errors",	       (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_ERRORS]), SHOW_LONG_STATUS},
   {"Com_show_fields",	       (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_FIELDS]), SHOW_LONG_STATUS},
+  {"Com_show_function_status", (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_FUNC_STATUS]), SHOW_LONG_STATUS},
   {"Com_show_grants",	       (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_GRANTS]), SHOW_LONG_STATUS},
   {"Com_show_keys",	       (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_KEYS]), SHOW_LONG_STATUS},
   {"Com_show_master_status",   (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_MASTER_STAT]), SHOW_LONG_STATUS},
@@ -6731,6 +6735,7 @@
   {"Com_show_open_tables",     (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_OPEN_TABLES]), SHOW_LONG_STATUS},
   {"Com_show_plugins",         (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_PLUGINS]), SHOW_LONG_STATUS},
   {"Com_show_privileges",      (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_PRIVILEGES]), SHOW_LONG_STATUS},
+  {"Com_show_procedure_status",(char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_PROC_STATUS]), SHOW_LONG_STATUS},
   {"Com_show_processlist",     (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_PROCESSLIST]), SHOW_LONG_STATUS},
   {"Com_show_slave_hosts",     (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_SLAVE_HOSTS]), SHOW_LONG_STATUS},
   {"Com_show_slave_status",    (char*) offsetof(STATUS_VAR, com_stat[(uint)
SQLCOM_SHOW_SLAVE_STAT]), SHOW_LONG_STATUS},
diff -Naur mysql-5.1-BK/sql/sp_head.cc mysql-5.1-trig/sql/sp_head.cc
--- mysql-5.1-BK/sql/sp_head.cc	2006-06-27 16:16:01.000000000 +0000
+++ mysql-5.1-trig/sql/sp_head.cc	2006-06-28 22:29:32.000000000 +0000
@@ -193,8 +193,8 @@
   case SQLCOM_SHOW_SLAVE_HOSTS:
   case SQLCOM_SHOW_SLAVE_STAT:
   case SQLCOM_SHOW_STATUS:
-  case SQLCOM_SHOW_STATUS_FUNC:
-  case SQLCOM_SHOW_STATUS_PROC:
+  case SQLCOM_SHOW_FUNC_STATUS:
+  case SQLCOM_SHOW_PROC_STATUS:
   case SQLCOM_SHOW_STORAGE_ENGINES:
   case SQLCOM_SHOW_TABLES:
   case SQLCOM_SHOW_VARIABLES:
diff -Naur mysql-5.1-BK/sql/sql_lex.h mysql-5.1-trig/sql/sql_lex.h
--- mysql-5.1-BK/sql/sql_lex.h	2006-06-26 03:56:16.000000000 +0000
+++ mysql-5.1-trig/sql/sql_lex.h	2006-06-28 22:29:32.000000000 +0000
@@ -99,13 +99,15 @@
   SQLCOM_CREATE_PROCEDURE, SQLCOM_CREATE_SPFUNCTION, SQLCOM_CALL,
   SQLCOM_DROP_PROCEDURE, SQLCOM_ALTER_PROCEDURE,SQLCOM_ALTER_FUNCTION,
   SQLCOM_SHOW_CREATE_PROC, SQLCOM_SHOW_CREATE_FUNC,
-  SQLCOM_SHOW_STATUS_PROC, SQLCOM_SHOW_STATUS_FUNC,
+  SQLCOM_SHOW_CREATE_TRIG,
+  SQLCOM_SHOW_PROC_STATUS, SQLCOM_SHOW_FUNC_STATUS,
   SQLCOM_PREPARE, SQLCOM_EXECUTE, SQLCOM_DEALLOCATE_PREPARE,
   SQLCOM_CREATE_VIEW, SQLCOM_DROP_VIEW,
   SQLCOM_CREATE_TRIGGER, SQLCOM_DROP_TRIGGER,
   SQLCOM_XA_START, SQLCOM_XA_END, SQLCOM_XA_PREPARE,
   SQLCOM_XA_COMMIT, SQLCOM_XA_ROLLBACK, SQLCOM_XA_RECOVER,
   SQLCOM_SHOW_PROC_CODE, SQLCOM_SHOW_FUNC_CODE,
+  SQLCOM_SHOW_TRIG_CODE,
   SQLCOM_ALTER_TABLESPACE,
   SQLCOM_INSTALL_PLUGIN, SQLCOM_UNINSTALL_PLUGIN,
   SQLCOM_SHOW_AUTHORS, SQLCOM_BINLOG_BASE64_EVENT,
diff -Naur mysql-5.1-BK/sql/sql_parse.cc mysql-5.1-trig/sql/sql_parse.cc
--- mysql-5.1-BK/sql/sql_parse.cc	2006-06-27 16:16:01.000000000 +0000
+++ mysql-5.1-trig/sql/sql_parse.cc	2006-06-28 22:29:32.000000000 +0000
@@ -28,6 +28,7 @@
 #include "sp_cache.h"
 #include "events.h"
 #include "event_timed.h"
+#include "sql_show_trigger.h"
 
 #ifdef HAVE_OPENSSL
 /*
@@ -666,8 +667,8 @@
   sql_command_flags[SQLCOM_REPLACE]=        CF_CHANGES_DATA | CF_HAS_ROW_COUNT;
   sql_command_flags[SQLCOM_REPLACE_SELECT]= CF_CHANGES_DATA | CF_HAS_ROW_COUNT;
 
-  sql_command_flags[SQLCOM_SHOW_STATUS_PROC]= CF_STATUS_COMMAND;
-  sql_command_flags[SQLCOM_SHOW_STATUS_FUNC]= CF_STATUS_COMMAND;
+  sql_command_flags[SQLCOM_SHOW_PROC_STATUS]= CF_STATUS_COMMAND;
+  sql_command_flags[SQLCOM_SHOW_FUNC_STATUS]= CF_STATUS_COMMAND;
   sql_command_flags[SQLCOM_SHOW_STATUS]=      CF_STATUS_COMMAND;
   sql_command_flags[SQLCOM_SHOW_DATABASES]=   CF_STATUS_COMMAND;
   sql_command_flags[SQLCOM_SHOW_TRIGGERS]=    CF_STATUS_COMMAND;
@@ -679,7 +680,6 @@
   sql_command_flags[SQLCOM_SHOW_VARIABLES]=   CF_STATUS_COMMAND;
   sql_command_flags[SQLCOM_SHOW_CHARSETS]=    CF_STATUS_COMMAND;
   sql_command_flags[SQLCOM_SHOW_COLLATIONS]=  CF_STATUS_COMMAND;
-  sql_command_flags[SQLCOM_SHOW_STATUS_PROC]= CF_STATUS_COMMAND;
 
   sql_command_flags[SQLCOM_SHOW_TABLES]=       (CF_STATUS_COMMAND |
                                                 CF_SHOW_TABLE_COMMAND);
@@ -2514,8 +2514,8 @@
                            is_schema_db(thd->lex->select_lex.db))))
       break;
     /* fall through */
-  case SQLCOM_SHOW_STATUS_PROC:
-  case SQLCOM_SHOW_STATUS_FUNC:
+  case SQLCOM_SHOW_PROC_STATUS:
+  case SQLCOM_SHOW_FUNC_STATUS:
     res= execute_sqlcom_select(thd, all_tables);
     break;
   case SQLCOM_SHOW_STATUS:
@@ -4844,6 +4844,25 @@
       }
       break;
     }
+  case SQLCOM_SHOW_CREATE_TRIG:
+    {
+      if (lex->spname->m_name.length > NAME_LEN)
+      {
+        my_error(ER_TOO_LONG_IDENT, MYF(0), lex->spname->m_name.str);
+        goto error;
+      }
+
+      if (check_table_access(thd, SELECT_ACL, all_tables, 0))
+        goto error;
+
+      if (mysql_show_create_trigger(thd, all_tables, lex->spname))
+      {
+        /* We don't distinguish between errors for now */
+        my_error(ER_TRG_DOES_NOT_EXIST, MYF(0));
+        goto error;
+      }
+      break;
+    }
 #ifdef NOT_USED
   case SQLCOM_SHOW_STATUS_PROC:
     {
@@ -4884,6 +4903,24 @@
       }
       break;
     }
+  case SQLCOM_SHOW_TRIG_CODE:
+    {
+      if (lex->spname->m_name.length > NAME_LEN)
+      {
+        my_error(ER_TOO_LONG_IDENT, MYF(0), lex->spname->m_name.str);
+        goto error;
+      }
+
+      // DEBUG tool : no check_table_access()
+
+      if (mysql_show_trigger_code(thd, all_tables, lex->spname))
+      {
+        /* We don't distinguish between errors for now */
+        my_error(ER_TRG_DOES_NOT_EXIST, MYF(0));
+        goto error;
+      }
+      break;
+    }
 #endif // ifndef DBUG_OFF
   case SQLCOM_CREATE_VIEW:
     {
diff -Naur mysql-5.1-BK/sql/sql_schema.h mysql-5.1-trig/sql/sql_schema.h
--- mysql-5.1-BK/sql/sql_schema.h	1970-01-01 00:00:00.000000000 +0000
+++ mysql-5.1-trig/sql/sql_schema.h	2006-06-28 22:29:29.000000000 +0000
@@ -0,0 +1,64 @@
+//============================================================================
+// This file is part of MySQL.
+// Copyright (c) 2006, MySQL AB.
+//
+// This program is free software; you can redistribute it and/or
+// modify it under the terms of the GNU General Public License
+// as published by the Free Software Foundation; either version 2
+// of the License, or (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+// GNU General Public License for more details.
+//
+// You should have received a copy of the GNU General Public License
+// along with this program; if not, write to the Free Software
+// Foundation, Inc., 51 Franklin Street, Fifth Floor,
+// Boston, MA 02110-1301, USA.
+//============================================================================
+
+/*
+ * Written by Marc Alff.
+ */
+
+#ifndef MYSQL_INFORMATION_SCHEMA_H
+#define MYSQL_INFORMATION_SCHEMA_H
+
+/**
+ * Index of the fields for the table information_schema.TRIGGERS.
+ * The fields description is in sql_show.cc, triggers_fields_info[].
+ */
+enum e_is_trigger_field
+{
+  IS_TRIGGER_FIELD_TRIGGER_CATALOG = 0,
+  IS_TRIGGER_FIELD_TRIGGER_SCHEMA = 1,
+  IS_TRIGGER_FIELD_TRIGGER_NAME = 2,
+  IS_TRIGGER_FIELD_EVENT_MANIPULATION = 3,
+  IS_TRIGGER_FIELD_EVENT_OBJECT_CATALOG = 4,
+  IS_TRIGGER_FIELD_EVENT_OBJECT_SCHEMA = 5,
+  IS_TRIGGER_FIELD_EVENT_OBJECT_TABLE = 6,
+  IS_TRIGGER_FIELD_ACTION_ORDER = 7,
+  IS_TRIGGER_FIELD_ACTION_CONDITION = 8,
+  IS_TRIGGER_FIELD_ACTION_STATEMENT = 9,
+  IS_TRIGGER_FIELD_ACTION_ORIENTATION = 10,
+  IS_TRIGGER_FIELD_ACTION_TIMING = 11,
+  IS_TRIGGER_FIELD_ACTION_REFERENCE_OLD_TABLE = 12,
+  IS_TRIGGER_FIELD_ACTION_REFERENCE_NEW_TABLE = 13,
+  IS_TRIGGER_FIELD_ACTION_REFERENCE_OLD_ROW = 14,
+  IS_TRIGGER_FIELD_ACTION_REFERENCE_NEW_ROW = 15,
+  IS_TRIGGER_FIELD_CREATED = 16,
+  IS_TRIGGER_FIELD_SQL_MODE = 17,
+  IS_TRIGGER_FIELD_DEFINER = 18
+};
+typedef enum e_is_trigger_field IS_TRIGGER_FIELD;
+const int IS_TRIGGER_COUNT_FIELD = 19;
+
+// TODO: suggested code cleanup
+// 1) describe the other information_schema / internal tables,
+// 2) use the following grep in the code base : grep "field\[[0-9]*\]" *.cc
+// 3) replace the hard coded field offsets by the appropriate enum
+// The resulting code will be searchable and more resistant to table changes.
+
+#endif
+
diff -Naur mysql-5.1-BK/sql/sql_show.cc mysql-5.1-trig/sql/sql_show.cc
--- mysql-5.1-BK/sql/sql_show.cc	2006-06-27 16:16:01.000000000 +0000
+++ mysql-5.1-trig/sql/sql_show.cc	2006-06-28 22:29:32.000000000 +0000
@@ -3283,9 +3283,9 @@
                                                 TYPE_ENUM_PROCEDURE))
     return 0;
 
-  if (lex->sql_command == SQLCOM_SHOW_STATUS_PROC &&
+  if (lex->sql_command == SQLCOM_SHOW_PROC_STATUS &&
       proc_table->field[2]->val_int() == TYPE_ENUM_PROCEDURE ||
-      lex->sql_command == SQLCOM_SHOW_STATUS_FUNC &&
+      lex->sql_command == SQLCOM_SHOW_FUNC_STATUS &&
       proc_table->field[2]->val_int() == TYPE_ENUM_FUNCTION ||
       (sql_command_flags[lex->sql_command] & CF_STATUS_COMMAND) == 0)
   {
diff -Naur mysql-5.1-BK/sql/sql_show_trigger.cc mysql-5.1-trig/sql/sql_show_trigger.cc
--- mysql-5.1-BK/sql/sql_show_trigger.cc	1970-01-01 00:00:00.000000000 +0000
+++ mysql-5.1-trig/sql/sql_show_trigger.cc	2006-06-28 22:29:29.000000000 +0000
@@ -0,0 +1,557 @@
+//============================================================================
+// This file is part of MySQL.
+// Copyright (c) 2006, MySQL AB.
+//
+// This program is free software; you can redistribute it and/or
+// modify it under the terms of the GNU General Public License
+// as published by the Free Software Foundation; either version 2
+// of the License, or (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+// GNU General Public License for more details.
+//
+// You should have received a copy of the GNU General Public License
+// along with this program; if not, write to the Free Software
+// Foundation, Inc., 51 Franklin Street, Fifth Floor,
+// Boston, MA 02110-1301, USA.
+//============================================================================
+
+/*
+ * Written by Marc Alff.
+ */
+
+#include "mysql_priv.h"
+#include "sql_schema.h"
+#include "sp_head.h"
+#include "sql_trigger.h"
+#include "sql_show_trigger.h"
+
+//===========================================================================
+// Helpers
+//===========================================================================
+
+/**
+ * Converts a trigger action time from the string representation to an enum
+ * @param str_action Input action time string
+ * @param trg_action Output action time enum
+ * @return 0 on success
+ */
+int
+String_to_trg_action_time_type(
+  const char* str_action,
+  enum trg_action_time_type * trg_action)
+{
+  int index;
+
+  for (index=0; index < TRG_ACTION_MAX; index++)
+  {
+    if (strcmp(trg_action_time_type_names[index].str, str_action) == 0)
+    {
+      * trg_action= (enum trg_action_time_type) index;
+      return 0;
+    }
+  }
+
+  return 1;
+}
+
+
+/**
+ * Converts a trigger event from the string representation to an enum
+ * @param str_event Input event string
+ * @param trg_event Output event enum
+ * @return 0 on success
+ */
+int
+String_to_trg_event_type(
+  const char* str_event,
+  enum trg_event_type * trg_event)
+{
+  int index;
+
+  for (index=0; index < TRG_EVENT_MAX; index++)
+  {
+    if (strcmp(trg_event_type_names[index].str, str_event) == 0)
+    {
+      * trg_event= (enum trg_event_type) index;
+      return 0;
+    }
+  }
+
+  return 1;
+}
+
+
+/**
+ * Convert user@host, to 'user'@'host',
+ * and append it to the given buffer.
+ * @param definer Input definer string in user@host format
+ * @param buffer Output buffer to which 'user'@'host' is appended
+ */
+static void escape_definer(const char* definer, String * buffer)
+{
+  const char* ptr= definer;
+  buffer->append('\'');
+  while (*ptr != '\0' && *ptr != '@')
+  {
+    buffer->append(*ptr);
+    ptr++;
+  }
+  buffer->append('\'');
+
+  if (*ptr == '@')
+  {
+    ptr++;
+    buffer->append('@');
+    buffer->append('\'');
+    buffer->append(ptr);
+    buffer->append('\'');
+  }
+}
+
+
+/**
+ * Generates a 'CREATE TRIGGER' statement.
+ * @param definer the trigger definer
+ * @param trigger_db the trigger db
+ * @param trigger_name the trigger name
+ * @param table_db the table db
+ * @param table_name the table name
+ * @param time the trigger action time
+ * @param event the trigger event
+ * @param orientation the trigger orientation
+ * @param statement the trigger statement
+ * @param buffer Output buffer the 'create trigger'
+ *   formatted statement is appended to
+ * @return 0 on success
+ */
+static void format_create_trigger(
+  const char* definer,
+  const char* trigger_db,
+  const char* trigger_name,
+  const char* table_db,
+  const char* table_name,
+  const char* time,
+  const char* event,
+  const char* orientation,
+  const char* statement,
+  String * buffer)
+{
+  // REFERENCE : http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html
+  buffer->append("CREATE DEFINER = ");
+  escape_definer(definer, buffer);
+  buffer->append(" TRIGGER `");
+  buffer->append(trigger_db);
+  buffer->append("`.`");
+  buffer->append(trigger_name);
+  buffer->append("` ");
+  buffer->append(time);
+  buffer->append(" ");
+  buffer->append(event);
+  buffer->append(" ON `");
+  buffer->append(table_db);
+  buffer->append("`.`");
+  buffer->append(table_name);
+  buffer->append("` FOR EACH ");
+  buffer->append(orientation);
+  buffer->append(" ");
+  buffer->append(statement);
+}
+
+
+//===========================================================================
+// DB access : information_schema.TRIGGERS
+//===========================================================================
+
+/**
+ * Open the information_schema.TRIGGERS table.
+ * Note that the table list must contain information_schema.TRIGGERS,
+ * which is typically done by using
<code>prepare_schema_table(SCH_TRIGGERS)</code>.
+ * @param thd the current thread
+ * @param tables the list of tables for this request.
+ * @return a TABLE handle on information_schema.TRIGGERS
+ */
+TABLE* open_is_triggers_table(THD *thd, TABLE_LIST *tables)
+{
+  TABLE *table= NULL;
+
+  DBUG_ENTER("open_is_triggers_table");
+
+  if (open_and_lock_tables(thd, tables))
+    DBUG_RETURN(NULL);
+
+  if (tables->schema_table && !tables->is_schema_table_processed)
+  {
+    tables->table->file->stats.records= 0;
+    if(tables->schema_table->fill_table(thd, tables, NULL))
+      DBUG_RETURN(NULL);
+
+    tables->is_schema_table_processed= TRUE;
+  }
+
+  table= tables->table;
+
+  if (table)
+    table->use_all_columns();
+
+  DBUG_RETURN(table);
+}
+
+
+#ifdef LATER
+int db_fetch_trigger(THD *thd, TABLE *table, sp_name *name)
+{
+  byte key[MAX_KEY_LENGTH];
+  int rc= 0;
+  int idx= 0;
+  int fetch_rc= 0;
+
+  DBUG_ENTER("db_fetch_trigger");
+
+  idx= IS_TRIGGER_FIELD_TRIGGER_SCHEMA;
+  table->field[idx]->store(name->m_db.str, name->m_db.length,
system_charset_info);
+
+  idx= IS_TRIGGER_FIELD_TRIGGER_NAME;
+  table->field[idx]->store(name->m_name.str, name->m_name.length,
system_charset_info);
+
+  /*
+   * This code assumes that (TRIGGER_SCHAME, TRIGGER_NAME) is the primary key
+   * of the table information_schema.TRIGGERS.
+   * Unfortunately, there is no such PK in 5.1 (as of 2006-06-23).
+   */
+  key_copy(key, table->record[0], table->key_info,
table->key_info->key_length);
+
+  rc= table->file->index_read_idx(
+    table->record[0],
+    0,
+    key, table->key_info->key_length,
+    HA_READ_KEY_EXACT);
+
+  DBUG_RETURN(rc);
+}
+#endif
+
+
+/**
+ * Fetch a trigger by name in information_schema.TRIGGERS.
+ * @param thd the current thread
+ * @param TABLE a handle on information_schema.TRIGGERS
+ * @param name the trigger name to fetch
+ * @return 0 if a record is found
+ */
+int db_fetch_trigger(THD *thd, TABLE *table, sp_name *name)
+{
+  int rc= 0;
+  int result= 1;
+  int idx= 0;
+  int fetch_rc= 0;
+  const char* rec_trigger_db= NULL;
+  const char* rec_trigger_name= NULL;
+  const char* pk_db= NULL;
+  const char* pk_name= NULL;
+
+  DBUG_ENTER("db_fetch_trigger");
+
+  pk_db= name->m_db.str;
+  pk_name= name->m_name.str;
+
+  // Full table scan
+  rc= table->file->ha_rnd_init(1);
+  if (rc == 0)
+  {
+    rc= table->file->rnd_next(table->record[0]);
+    while (rc == 0)
+    {
+      rec_trigger_db= get_field(thd->mem_root,
+        table->field[IS_TRIGGER_FIELD_TRIGGER_SCHEMA]);
+
+      rec_trigger_name= get_field(thd->mem_root,
+        table->field[IS_TRIGGER_FIELD_TRIGGER_NAME]);
+
+      if (   (strcmp(rec_trigger_db, pk_db) == 0)
+          && (strcmp(rec_trigger_name, pk_name) == 0))
+      {
+        // Found (TRIGGER_DB, TRIGGER_NAME) == (name.m_db, name.m_name)
+        (void) table->file->ha_rnd_end();
+        DBUG_RETURN(0);
+      }
+
+      rc= table->file->rnd_next(table->record[0]);
+    }
+
+    (void) table->file->ha_rnd_end();
+  }
+
+  DBUG_RETURN(1);
+}
+
+//===========================================================================
+// SHOW CREATE TRIGGER
+//===========================================================================
+
+/**
+ * Implement the 'SHOW CREATE TRIGGER' command.
+ * @param thd the current tread
+ * @param tables the table list for this request
+ * @param name the trigger to show
+ * @return 0 on success
+ */
+int mysql_show_create_trigger(
+  THD *thd,
+  TABLE_LIST *tables,
+  sp_name *name)
+{
+  TABLE *table= NULL;
+  List<Item> field_list;
+  Protocol *protocol= thd->protocol;
+  int rc= 1;
+  const char* definer= NULL;
+  const char* trigger_db= NULL;
+  const char* trigger_name= NULL;
+  const char* table_db= NULL;
+  const char* table_name= NULL;
+  const char* event= NULL;
+  const char* time= NULL;
+  const char* orientation= NULL;
+  const char* statement= NULL;
+  CHARSET_INFO *cs= system_charset_info;
+  String buffer;
+
+  DBUG_ENTER("mysql_show_create_trigger");
+
+  buffer.set_charset(cs);
+  buffer.reserve(512);
+
+  table= open_is_triggers_table(thd, tables);
+  if (! table)
+    goto end;
+
+  if (db_fetch_trigger(thd, table, name))
+    goto end;
+
+  // Headers
+  field_list.push_back(new Item_empty_string("Db", 64));
+  field_list.push_back(new Item_empty_string("Name", 64));
+  field_list.push_back(new Item_empty_string("Create Trigger", 1024));
+
+  if (protocol->send_fields(&field_list,
+                            Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
+    goto end;
+
+  // Data
+  trigger_db= get_field(thd->mem_root,
+    table->field[IS_TRIGGER_FIELD_TRIGGER_SCHEMA]);
+
+  trigger_name= get_field(thd->mem_root,
+    table->field[IS_TRIGGER_FIELD_TRIGGER_NAME]);
+
+  table_db= get_field(thd->mem_root,
+    table->field[IS_TRIGGER_FIELD_EVENT_OBJECT_SCHEMA]);
+
+  table_name= get_field(thd->mem_root,
+    table->field[IS_TRIGGER_FIELD_EVENT_OBJECT_TABLE]);
+
+  time= get_field(thd->mem_root,
+    table->field[IS_TRIGGER_FIELD_ACTION_TIMING]);
+
+  event= get_field(thd->mem_root,
+    table->field[IS_TRIGGER_FIELD_EVENT_MANIPULATION]);
+
+  orientation= get_field(thd->mem_root,
+    table->field[IS_TRIGGER_FIELD_ACTION_ORIENTATION]);
+
+  statement= get_field(thd->mem_root,
+    table->field[IS_TRIGGER_FIELD_ACTION_STATEMENT]);
+
+  definer= get_field(thd->mem_root,
+    table->field[IS_TRIGGER_FIELD_DEFINER]);
+
+  format_create_trigger(
+    definer,
+    trigger_db,
+    trigger_name,
+    table_db,
+    table_name,
+    time,
+    event,
+    orientation,
+    statement,
+    & buffer);
+
+  protocol->prepare_for_resend();
+  protocol->store(trigger_db, cs);
+  protocol->store(trigger_name, cs);
+  protocol->store(buffer.ptr(), buffer.length(), buffer.charset());
+  if (protocol->write())
+    goto end;
+
+  send_eof(thd);
+  rc= 0;
+
+end:
+  close_thread_tables(thd);
+
+  DBUG_RETURN(rc);
+}
+
+//===========================================================================
+// SHOW TRIGGER CODE
+//===========================================================================
+
+#ifndef DBUG_OFF
+/**
+ * Implements 'SHOW TRIGGER CODE', by table/action/event.
+ * @param thd the current thread
+ * @param db the table db
+ * @param table_name the table name
+ * @param trg_action the trigger action
+ * @pram trg_event the trigger event
+ * @return 0 on success
+ */
+int show_trigger_code(
+  THD *thd,
+  const char* db,
+  const char* table_name,
+  enum trg_action_time_type trg_action,
+  enum trg_event_type trg_event)
+{
+  TABLE_LIST tables;
+  TABLE *table= NULL;
+  Table_triggers_list *triggers= NULL;
+  sp_head *sp= NULL;
+  int rc= 1;
+  DBUG_ENTER("show_trigger_code");
+
+  memset(& tables, 0, sizeof(tables));
+  tables.db= (char*) db;
+  tables.table_name= tables.alias= (char*) table_name;
+
+  table= open_ltable(thd, & tables, TL_READ);
+  if (! table)
+    goto end;
+
+  triggers= table->triggers;
+  if (! triggers)
+    goto end;
+
+  rc= triggers->show_trigger_code(thd, trg_event, trg_action);
+
+end:
+  close_thread_tables(thd);
+
+  DBUG_RETURN(rc);
+}
+#endif
+
+/**
+ * Fetch a trigger by name, and find what table/action/event
+ * the trigger is attached to.
+ * Note that output parameters are undefined on failure.
+ * @param thd the current thread
+ * @param tables the table list for this request (must contain
+ *   information_schema.TRIGGERS)
+ * @param table_db Output table db for the trigger
+ * @param table_name Output table name for the trigger
+ * @param trg_action Output action for the trigger
+ * @param trg_event Output event for the trigger
+ * @return 0 on success
+ */
+int fetch_trigger_table(
+  THD *thd,
+  TABLE_LIST *tables,
+  sp_name * name,
+  String * table_db,
+  String * table_name,
+  enum trg_action_time_type * trg_action,
+  enum trg_event_type * trg_event)
+{
+  int rc= 1;
+  const char* action= NULL;
+  const char* event= NULL;
+  TABLE *table= NULL;
+
+  DBUG_ENTER("fetch_trigger_table");
+
+  table= open_is_triggers_table(thd, tables);
+  if (! table)
+    goto end;
+
+  if (db_fetch_trigger(thd, table, name))
+    goto end;
+
+  // Data
+  get_field(thd->mem_root,
+    table->field[IS_TRIGGER_FIELD_EVENT_OBJECT_SCHEMA],
+    table_db);
+
+  get_field(thd->mem_root,
+    table->field[IS_TRIGGER_FIELD_EVENT_OBJECT_TABLE],
+    table_name);
+
+  action= get_field(thd->mem_root,
+    table->field[IS_TRIGGER_FIELD_ACTION_TIMING]);
+
+  if (String_to_trg_action_time_type(action, trg_action))
+    goto end;
+
+  event= get_field(thd->mem_root,
+    table->field[IS_TRIGGER_FIELD_EVENT_MANIPULATION]);
+
+  if (String_to_trg_event_type(event, trg_event))
+    goto end;
+
+  // LATER : IS_TRIGGER_FIELD_ACTION_ORDER
+
+  // Success
+  rc= 0;
+
+end:
+  close_thread_tables(thd);
+
+  DBUG_RETURN(rc);
+}
+
+#ifndef DBUG_OFF
+/**
+ * Implements the 'SHOW TRIGGER CODE' (DEBUG ONLY) command.
+ * @param thd the current thread
+ * @param tables the table list for this request (must contain
+ *   information_schema.TRIGGERS)
+ * @param name the trigger name
+ * @return 0 on success
+ */
+int mysql_show_trigger_code(THD *thd, TABLE_LIST *tables, sp_name *name)
+{
+  int rc= 1;
+  String table_db;
+  String table_name;
+  enum trg_action_time_type trg_action;
+  enum trg_event_type trg_event;
+
+  DBUG_ENTER("mysql_show_trigger_code");
+
+  rc= fetch_trigger_table(
+    thd,
+    tables,
+    name,
+    & table_db,
+    & table_name,
+    & trg_action,
+    & trg_event);
+
+  if (rc == 0)
+  {
+    rc= show_trigger_code(
+      thd,
+      table_db.ptr(),
+      table_name.ptr(),
+      trg_action,
+      trg_event);
+  }
+
+  DBUG_RETURN(rc);
+}
+#endif
+
diff -Naur mysql-5.1-BK/sql/sql_show_trigger.h mysql-5.1-trig/sql/sql_show_trigger.h
--- mysql-5.1-BK/sql/sql_show_trigger.h	1970-01-01 00:00:00.000000000 +0000
+++ mysql-5.1-trig/sql/sql_show_trigger.h	2006-06-28 22:29:29.000000000 +0000
@@ -0,0 +1,41 @@
+//============================================================================
+// This file is part of MySQL.
+// Copyright (c) 2006, MySQL AB.
+//
+// This program is free software; you can redistribute it and/or
+// modify it under the terms of the GNU General Public License
+// as published by the Free Software Foundation; either version 2
+// of the License, or (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+// GNU General Public License for more details.
+//
+// You should have received a copy of the GNU General Public License
+// along with this program; if not, write to the Free Software
+// Foundation, Inc., 51 Franklin Street, Fifth Floor,
+// Boston, MA 02110-1301, USA.
+//============================================================================
+
+/*
+ * Written by Marc Alff.
+ */
+
+#ifndef MYSQL_SHOW_TRIGGER_H
+#define MYSQL_SHOW_TRIGGER_H
+
+int mysql_show_create_trigger(
+  THD *thd,
+  TABLE_LIST *tables,
+  sp_name *name);
+
+#ifndef DBUG_OFF
+int mysql_show_trigger_code(
+  THD *thd,
+  TABLE_LIST *tables,
+  sp_name *name);
+#endif
+
+#endif
+
diff -Naur mysql-5.1-BK/sql/sql_trigger.cc mysql-5.1-trig/sql/sql_trigger.cc
--- mysql-5.1-BK/sql/sql_trigger.cc	2006-06-26 03:56:17.000000000 +0000
+++ mysql-5.1-trig/sql/sql_trigger.cc	2006-06-28 22:29:32.000000000 +0000
@@ -107,6 +107,7 @@
 };
 
 
+static bool trigger_exist(THD *thd, sp_name *trig);
 static TABLE_LIST *add_table_for_trigger(THD *thd, sp_name *trig);
 
 class Handle_old_incorrect_sql_modes_hook: public Unknown_key_hook
@@ -179,9 +180,29 @@
     DBUG_RETURN(TRUE);
   }
 
-  if (!create &&
-      !(tables= add_table_for_trigger(thd, thd->lex->spname)))
-    DBUG_RETURN(TRUE);
+  if (!create)
+  {
+    if (thd->lex->drop_if_exists && ! trigger_exist(thd,
thd->lex->spname))
+    {
+      push_warning_printf(thd,
+                          MYSQL_ERROR::WARN_LEVEL_NOTE,
+                          ER_TRG_DOES_NOT_EXIST,
+                          ER(ER_TRG_DOES_NOT_EXIST));
+
+      // Since the trigger does not exist, there is no associated table,
+      // and therefore :
+      // - no TRIGGER privileges to check,
+      // - no trigger to drop
+      // - no table to lock/modify, etc
+      // so the drop statement is successful.
+      result= FALSE;
+      // Still, we need to log the query ...
+      goto end_no_LOCK_open;
+    }
+
+    if ( !(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);
@@ -266,6 +287,7 @@
 
 end:
   VOID(pthread_mutex_unlock(&LOCK_open));
+end_no_LOCK_open:
   start_waiting_global_read_lock(thd);
 
   if (!result)
@@ -688,7 +710,8 @@
     }
   }
 
-  my_message(ER_TRG_DOES_NOT_EXIST, ER(ER_TRG_DOES_NOT_EXIST), MYF(0));
+  // Internal error, should not come this far.
+  my_error(ER_TRG_DOES_NOT_EXIST, MYF(0));
   return 1;
 }
 
@@ -762,6 +785,33 @@
   }
 }
 
+#ifndef DBUG_OFF
+/**
+ * Show a trigger code (DEBUG only).
+ * @param thd the current thread
+ * @param event the trigger event
+ * @param time_type the trigger action time
+ * @returns 0 on success
+ */
+int Table_triggers_list::show_trigger_code(
+  THD *thd,
+  trg_event_type event,
+  trg_action_time_type time_type)
+{
+  int rc= 1;
+  sp_head *sp= bodies[event][time_type];
+
+  DBUG_ENTER("Table_triggers_list::show_trigger_code");
+
+  if (sp)
+  {
+    rc= sp->show_routine_code(thd);
+  }
+
+  DBUG_RETURN(rc);
+}
+#endif
+
 
 /*
   Check whenever .TRG file for table exist and load all triggers it contains.
@@ -1114,6 +1164,40 @@
   DBUG_RETURN(1);
 }
 
+/**
+ * Check if a trigger exists.
+ * @param thd the current thread
+ * @param trig the trigger name
+ * @return true if the trigger exists
+ */
+static bool trigger_exist(THD *thd, sp_name *trig)
+{
+  char path_buff[FN_REFLEN];
+  LEX_STRING path;
+
+  /*
+   * Implementation notes:
+   * This code assumes that triggers are physically stored using a .TRN file,
+   * which can be looked up knowing the trigger name only.
+   * If/When the implementation changes this code will need to lookup
+   * the information_schema.TRIGGERS table instead (more costly).
+   * See fetch_trigger_table()
+   */
+
+  DBUG_ENTER("trigger_exist");
+
+  path.length= build_table_filename(path_buff, FN_REFLEN-1,
+                                    trig->m_db.str, trig->m_name.str,
+                                    trigname_file_ext);
+  path.str= path_buff;
+
+  if (access(path_buff, F_OK))
+  {
+    DBUG_RETURN(FALSE);
+  }
+
+  DBUG_RETURN(TRUE);
+}
 
 /*
   Find trigger's table from trigger identifier and add it to
@@ -1624,3 +1708,4 @@
   }
   DBUG_RETURN(FALSE);
 }
+
diff -Naur mysql-5.1-BK/sql/sql_trigger.h mysql-5.1-trig/sql/sql_trigger.h
--- mysql-5.1-BK/sql/sql_trigger.h	2006-06-26 03:56:17.000000000 +0000
+++ mysql-5.1-trig/sql/sql_trigger.h	2006-06-28 22:29:32.000000000 +0000
@@ -119,6 +119,12 @@
 
   void set_table(TABLE *new_table);
 
+#ifndef DBUG_OFF
+  int show_trigger_code(THD *thd,
+                        trg_event_type event,
+                        trg_action_time_type time_type);
+#endif
+
   friend class Item_trigger_field;
   friend int sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
                                                             TABLE_LIST *table);
@@ -140,3 +146,4 @@
 
 extern const LEX_STRING trg_action_time_type_names[];
 extern const LEX_STRING trg_event_type_names[];
+
diff -Naur mysql-5.1-BK/sql/sql_yacc.yy mysql-5.1-trig/sql/sql_yacc.yy
--- mysql-5.1-BK/sql/sql_yacc.yy	2006-06-27 16:16:01.000000000 +0000
+++ mysql-5.1-trig/sql/sql_yacc.yy	2006-06-28 22:29:32.000000000 +0000
@@ -39,6 +39,7 @@
 #include "sp_rcontext.h"
 #include "sp.h"
 #include "event_timed.h"
+#include "sql_show_trigger.h"
 #include <myisam.h>
 #include <myisammrg.h>
 
@@ -7688,11 +7689,12 @@
             lex->sql_command = SQLCOM_DROP_EVENT;
             lex->drop_if_exists= $3;
           }
-        | DROP TRIGGER_SYM sp_name
+        | DROP TRIGGER_SYM if_exists sp_name
           {
             LEX *lex= Lex;
             lex->sql_command= SQLCOM_DROP_TRIGGER;
-            lex->spname= $3;
+            lex->drop_if_exists= $3;
+            lex->spname= $4;
 	  }
         | DROP TABLESPACE tablespace_name opt_ts_engine opt_ts_wait
           {
@@ -8376,10 +8378,19 @@
 	    lex->sql_command = SQLCOM_SHOW_CREATE_FUNC;
 	    lex->spname= $3;
 	  }
+        | CREATE TRIGGER_SYM sp_name
+          {
+            LEX *lex= Lex;
+
+            lex->sql_command = SQLCOM_SHOW_CREATE_TRIG;
+            lex->spname= $3;
+            if (prepare_schema_table(YYTHD, lex, 0, SCH_TRIGGERS))
+              YYABORT;
+          }
 	| PROCEDURE STATUS_SYM wild_and_where
 	  {
             LEX *lex= Lex;
-            lex->sql_command= SQLCOM_SHOW_STATUS_PROC;
+            lex->sql_command= SQLCOM_SHOW_PROC_STATUS;
 	    if (!sp_add_to_query_tables(YYTHD, lex, "mysql", "proc", TL_READ))
 	      YYABORT;
             if (prepare_schema_table(YYTHD, lex, 0, SCH_PROCEDURES))
@@ -8388,7 +8399,7 @@
 	| FUNCTION_SYM STATUS_SYM wild_and_where
 	  {
             LEX *lex= Lex;
-            lex->sql_command= SQLCOM_SHOW_STATUS_FUNC;
+            lex->sql_command= SQLCOM_SHOW_FUNC_STATUS;
 	    if (!sp_add_to_query_tables(YYTHD, lex, "mysql", "proc", TL_READ))
 	      YYABORT;
             if (prepare_schema_table(YYTHD, lex, 0, SCH_PROCEDURES))
@@ -8414,6 +8425,19 @@
 	    Lex->spname= $3;
 #endif
           }
+        | TRIGGER_SYM CODE_SYM sp_name
+          {
+#ifdef DBUG_OFF
+            yyerror(ER(ER_SYNTAX_ERROR));
+            YYABORT;
+#else
+            LEX *lex= Lex;
+            lex->sql_command= SQLCOM_SHOW_TRIG_CODE;
+            lex->spname= $3;
+            if (prepare_schema_table(YYTHD, lex, 0, SCH_TRIGGERS))
+              YYABORT;
+#endif
+          }
         | CREATE EVENT_SYM sp_name
           {
             Lex->sql_command = SQLCOM_SHOW_CREATE_EVENT;

Thread
Contribution for Bug#18161 (show create trigger / drop trigger ifexist / show trigger code)Marc Alff29 Jun
  • Re: Contribution for Bug#18161 (show create trigger / drop triggerif exist / show trigger code)Stewart Smith29 Jun
    • Re: Contribution for Bug#18161 (show create trigger / drop triggerif exist / show trigger code)Marc Alff30 Jun
      • Re: Contribution for Bug#18161 (show create trigger / drop triggerif exist / show trigger code)Stewart Smith30 Jun
        • Re: Contribution for Bug#18161 (show create trigger / drop triggerif exist / show trigger code)Marc Alff2 Jul