Fix for bugs #5892/6182/8751/8758/10994 (based on Antony's patch)

  "Triggers have the wrong namespace"
  "Triggers: duplicate names allowed"
  "Triggers: CREATE TRIGGER does not accept fully qualified names"
  "SHOW TRIGGERS"


mysql-test/r/information_schema.result:
  Added tests for new INFORMATION_SCHEMA.TRIGGERS view and SHOW TRIGGERS command.
mysql-test/r/information_schema_db.result:
  INFORMATION_SCHEMA.TRIGGERS view was added.
mysql-test/r/rpl_sp.result:
  Now DROP TRIGGER interprets first part of trigger identifier as database
  name and not as table name. Adjusted tests properly.
mysql-test/r/trigger.result:
  Now DROP TRIGGER interprets first part of trigger identifier as database
  name and not as table name. Adjusted tests properly.
  Added test checking that triggers have database wide namespace.
  Added test for bug #8791 "Triggers: Allowed to create triggers on a subject
  table in a different DB".
mysql-test/r/view.result:
  Now DROP TRIGGER interprets first part of trigger identifier as database
  name and not as table name. Adjusted tests properly.
mysql-test/t/information_schema.test:
  Added tests for new INFORMATION_SCHEMA.TRIGGERS view and SHOW TRIGGERS command.
mysql-test/t/rpl_sp.test:
  Now DROP TRIGGER interprets first part of trigger identifier as database
  name and not as table name. Adjusted tests properly.
mysql-test/t/trigger.test:
  Now DROP TRIGGER interprets first part of trigger identifier as database
  name and not as table name. Adjusted tests properly.
  Added test checking that triggers have database wide namespace.
  Added test for bug #8791 "Triggers: Allowed to create triggers on a subject
  table in a different DB".
mysql-test/t/view.test:
  Now DROP TRIGGER interprets first part of trigger identifier as database
  name and not as table name. Adjusted tests properly.
sql/handler.cc:
  Added .TRN tho the list of known file extensions assoicated with tables.
sql/item.h:
  trg_action_time_type/trg_event_type enums:
    Added TRG_ACTION_MAX/TRG_EVENT_MAX elements which should be used instead of
    magical values in various loops where we iterate through all types of trigger
    action times or/and trigger event types.
sql/lex.h:
  Added new symbol "TRIGGERS".
sql/mysql_priv.h:
  Added declaration of constant holding extension for trigger name (.TRN) files.
sql/mysqld.cc:
  Added statistical variable for SHOW TRIGGERS command.
sql/share/errmsg.txt:
  Added error message saying that one attempts to create trigger in wrong schema.
sql/sp.cc:
  Replaced magical values with TRG_EVENT_MAX/TRG_ACTION_MAX constants.
sql/sql_base.cc:
  open_unireg_entry():
    Now Table_triggers_list::check_n_load() has one more argument which
    controls whether we should prepare Table_triggers_list with fully functional
    triggers or load only their names.
sql/sql_lex.h:
  Added element for new SHOW TRIGGERS command to enum_sql_command enum.
sql/sql_parse.cc:
  prepare_schema_table():
    Added support for SHOW TRIGGERS statement.
sql/sql_show.cc:
  Added new INFORMATION_SCHEMA.TRIGGERS view and SHOW TRIGGERS command.
sql/sql_table.cc:
  mysql_rm_table_part2():
    Replaced simple deletion of .TRG file with call to
    Table_triggers_list::drop_all_triggers which will also delete .TRN files
    for all triggers associated with table.
sql/sql_trigger.cc:
  Now triggers have database wide namespace. To support it we create special .TRN
  file with same name as trigger for each trigger. This file contains name of
  trigger's table so one does not need to specify it explicitly in DROP TRIGGER.
  Moreover DROP TRIGGER treats first part of trigger identifier as database name
  now. Updated mysql_create_or_drop_trigger() routine and
  Table_triggers_list::create_trigger()/drop_trigger()/check_n_load() methods
  accordingly. Added add_table_for_trigger() routine and
  Table_triggers_list::drop_all_triggers() method.
  
  Added Table_triggers_list::get_trigger_info() for obtaining trigger metadata.
sql/sql_trigger.h:
  Table_triggers_list:
    Use TRG_EVENT_MAX, TRG_ACTION_MAX instead of magic values.
    Added get_trigger_info() method for obtaining trigger's meta-data.
    Added drop_all_triggers() method which drops all triggers for table.
    Added declarations of trg_action_time_type_names/trg_event_type_names
    arrays which hold names of triggers action time types  and event types.
sql/sql_yacc.yy:
  Changed grammar for CREATE/DROP TRIGGER to support database wide trigger
  namespace. Added new SHOW TRIGGERS statement.
sql/table.h:
  enum enum_schema_tables:
    Added constant for new INFORMATION_SCHEMA.TRIGGERS view.

16 files changed, 454 insertions, 84 deletions

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
 };