Skip navigation links
MySQL 5.5 Reference Manual :: 19 INFORMATION_SCHEMA Tables :: 19.16 The INFORMATION_SCHEMA TRIGGERS Table
19.17 The INFORMATION_SCHEMA PLUGINS Table »
Section Navigation      [Toggle]

19.16. The INFORMATION_SCHEMA TRIGGERS Table

The TRIGGERS table provides information about triggers. You must have the TRIGGER privilege to access this table.

INFORMATION_SCHEMA Name SHOW Name Remarks
TRIGGER_CATALOG   def
TRIGGER_SCHEMA    
TRIGGER_NAME Trigger  
EVENT_MANIPULATION Event  
EVENT_OBJECT_CATALOG   def
EVENT_OBJECT_SCHEMA    
EVENT_OBJECT_TABLE Table  
ACTION_ORDER   0
ACTION_CONDITION   NULL
ACTION_STATEMENT Statement  
ACTION_ORIENTATION   ROW
ACTION_TIMING Timing  
ACTION_REFERENCE_OLD_TABLE   NULL
ACTION_REFERENCE_NEW_TABLE   NULL
ACTION_REFERENCE_OLD_ROW   OLD
ACTION_REFERENCE_NEW_ROW   NEW
CREATED   NULL (0)
SQL_MODE   MySQL extension
DEFINER   MySQL extension
CHARACTER_SET_CLIENT   MySQL extension
COLLATION_CONNECTION   MySQL extension
DATABASE_COLLATION   MySQL extension

Notes:

  • The TRIGGER_SCHEMA and TRIGGER_NAME columns contain the name of the database in which the trigger occurs and the trigger name, respectively.

  • The EVENT_MANIPULATION column contains one of the values 'INSERT', 'DELETE', or 'UPDATE'.

  • As noted in Section 18.3, “Using Triggers”, every trigger is associated with exactly one table. The EVENT_OBJECT_SCHEMA and EVENT_OBJECT_TABLE columns contain the database in which this table occurs, and the table's name.

  • The ACTION_ORDER statement contains the ordinal position of the trigger's action within the list of all similar triggers on the same table. Currently, this value is always 0, because it is not possible to have more than one trigger with the same EVENT_MANIPULATION and ACTION_TIMING on the same table.

  • The ACTION_STATEMENT column contains the statement to be executed when the trigger is invoked. This is the same as the text displayed in the Statement column of the output from SHOW TRIGGERS. Note that this text uses UTF-8 encoding.

  • The ACTION_ORIENTATION column always contains the value 'ROW'.

  • The ACTION_TIMING column contains one of the two values 'BEFORE' or 'AFTER'.

  • The columns ACTION_REFERENCE_OLD_ROW and ACTION_REFERENCE_NEW_ROW contain the old and new column identifiers, respectively. This means that ACTION_REFERENCE_OLD_ROW always contains the value 'OLD' and ACTION_REFERENCE_NEW_ROW always contains the value 'NEW'.

  • The SQL_MODE column shows the server SQL mode that was in effect at the time when the trigger was created (and thus which remains in effect for this trigger whenever it is invoked, regardless of the current server SQL mode). The possible range of values for this column is the same as that of the sql_mode system variable. See Section 5.1.8, “Server SQL Modes”.

  • The DEFINER column indicates who defined the trigger.

  • CHARACTER_SET_CLIENT is the session value of the character_set_client system variable when the trigger was created. COLLATION_CONNECTION is the session value of the collation_connection system variable when the trigger was created. DATABASE_COLLATION is the collation of the database with which the trigger is associated.

  • The following columns currently always contain NULL: ACTION_CONDITION, ACTION_REFERENCE_OLD_TABLE, ACTION_REFERENCE_NEW_TABLE, and CREATED.

Example, using the ins_sum trigger defined in Section 18.3, “Using Triggers”: 

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: ins_sum
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: account
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: SET @sum = @sum + NEW.amount
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE:
                   DEFINER: me@localhost

See also Section 12.4.5.39, “SHOW TRIGGERS Syntax”.


User Comments

Posted by tommaso dd on April 4 2006 3:59pm[Delete] [Edit]

This is one of the most important help thank you

Posted by tommaso dd on April 4 2006 4:05pm[Delete] [Edit]

--copy and paste in your prompt of mysql>
show triggers;
--for see all the triggers create
describe INFORMATION_SCHEMA.TRIGGERS;
--for see the columns of INFORMATION_SCHEMA table
describe INFORMATION_SCHEMA.COLUMNS;

SELECT * FROM INFORMATION_SCHEMA.COLUMNS;
--for see all the columns creates :)Dangerous

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS;
--for see all the triggers informations


see you !!!

Add your own comment.