Bug #9586 information_schema doesn't contain table with trigger information
Submitted: 2 Apr 2005 19:49 Modified: 20 Jul 2005 17:23
Reporter: Niels G Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3 OS:Any (All)
Assigned to: Sergey Glukhov

[2 Apr 2005 19:49] Niels G
Description:
The information_schema database contains information about the schema objects in all databases, including tables, columns, routines.  But it does not contain information about triggers.  As a developer of a 3rd party client tool for MySQL, I need to query the database for a list of triggers and for the trigger body so that I can generate a CREATE DDL for the trigger.

How to repeat:
There is no way to get this information as I can tell from the tables in information_schema, or from the current documentation.

Suggested fix:
Add a table to the information_schema database called "TRIGGERS" which will contain information about the trigger. Example ... 

CREATE TABLE TRIGGERS ( 
        TRIGGER_CATALOG   	varchar(512) NULL,
        TRIGGER_SCHEMA    	varchar(64) NOT NULL,
        TRIGGER_NAME      	varchar(64) NOT NULL,
        TRIGGER_TYPE      	varchar(9) NOT NULL,
        TRIGGER_DEFINITION	longtext NOT NULL,
        CREATED           	datetime NOT NULL DEFAULT 0000-00-00 00:00:00,
        LAST_ALTERED      	datetime NOT NULL DEFAULT 0000-00-00 00:00:00,
        ROUTINE_COMMENT   	varchar(64) NOT NULL,
        DEFINER           	varchar(77) NOT NULL 
        )
[4 Apr 2005 12:44] Miguel Solorzano
According the Manual:

http://dev.mysql.com/doc/mysql/en/other-information-schema-tables.html

21.1.16. Other INFORMATION_SCHEMA Tables

We will add more INFORMATION_SCHEMA tables soon. Particularly, we acknowledge the need for INFORMATION_SCHEMA.PARAMETERS and for INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS and for INFORMATION_SCHEMA.TRIGGERS.
[3 Jun 2005 8:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25556
[19 Jul 2005 20:42] Dmitry Lenev
Fixed in 5.0.10
[20 Jul 2005 17:23] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Noted bugfix in 5.0.10 changelog; added basic description of new table to Information Schema chapter of Manual.