- 20 INFORMATION_SCHEMA Tables
- 20.1 The INFORMATION_SCHEMA SCHEMATA Table
- 20.2 The INFORMATION_SCHEMA TABLES Table
- 20.3 The INFORMATION_SCHEMA COLUMNS Table
- 20.4 The INFORMATION_SCHEMA STATISTICS Table
- 20.5 The INFORMATION_SCHEMA USER_PRIVILEGES Table
- 20.6 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
- 20.7 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
- 20.8 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
- 20.9 The INFORMATION_SCHEMA CHARACTER_SETS Table
- 20.10 The INFORMATION_SCHEMA COLLATIONS Table
- 20.11 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
- 20.12 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
- 20.13 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
- 20.14 The INFORMATION_SCHEMA ROUTINES Table
- 20.15 The INFORMATION_SCHEMA VIEWS Table
- 20.16 The INFORMATION_SCHEMA TRIGGERS Table
- 20.17 The INFORMATION_SCHEMA PLUGINS Table
- 20.18 The INFORMATION_SCHEMA ENGINES Table
- 20.19 The INFORMATION_SCHEMA PARTITIONS Table
- 20.20 The INFORMATION_SCHEMA EVENTS Table
- 20.21 The INFORMATION_SCHEMA FILES Table
- 20.22 The INFORMATION_SCHEMA PROCESSLIST Table
- 20.23 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
- 20.24 The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables
- 20.25 The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables
- 20.26 The INFORMATION_SCHEMA PROFILING Table
- 20.27 Other INFORMATION_SCHEMA Tables
- 20.28 Extensions to SHOW Statements
The TRIGGERS table provides
information about triggers. You must have the
TRIGGER privilege to access this
table (prior to MySQL 5.1.22, you must have the
SUPER privilege).
INFORMATION_SCHEMA
Name |
SHOW
Name |
Remarks |
TRIGGER_CATALOG |
NULL |
|
TRIGGER_SCHEMA |
||
TRIGGER_NAME |
Trigger |
|
EVENT_MANIPULATION |
Event |
|
EVENT_OBJECT_CATALOG |
NULL |
|
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_SCHEMAandTRIGGER_NAMEcolumns contain the name of the database in which the trigger occurs and the trigger name, respectively.The
EVENT_MANIPULATIONcolumn contains one of the values'INSERT','DELETE', or'UPDATE'.As noted in Section 19.3, “Using Triggers”, every trigger is associated with exactly one table. The
EVENT_OBJECT_SCHEMAandEVENT_OBJECT_TABLEcolumns contain the database in which this table occurs, and the table's name.The
ACTION_ORDERstatement contains the ordinal position of the trigger's action within the list of all similar triggers on the same table. Currently, this value is always0, because it is not possible to have more than one trigger with the sameEVENT_MANIPULATIONandACTION_TIMINGon the same table.The
ACTION_STATEMENTcolumn contains the statement to be executed when the trigger is invoked. This is the same as the text displayed in theStatementcolumn of the output fromSHOW TRIGGERS. Note that this text uses UTF-8 encoding.The
ACTION_ORIENTATIONcolumn always contains the value'ROW'.The
ACTION_TIMINGcolumn contains one of the two values'BEFORE'or'AFTER'.The columns
ACTION_REFERENCE_OLD_ROWandACTION_REFERENCE_NEW_ROWcontain the old and new column identifiers, respectively. This means thatACTION_REFERENCE_OLD_ROWalways contains the value'OLD'andACTION_REFERENCE_NEW_ROWalways contains the value'NEW'.The
SQL_MODEcolumn 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 thesql_modesystem variable. See Section 5.1.8, “Server SQL Modes”.The
DEFINERcolumn was added in MySQL 5.1.2.DEFINERindicates who defined the trigger.CHARACTER_SET_CLIENTis the session value of thecharacter_set_clientsystem variable when the trigger was created.COLLATION_CONNECTIONis the session value of thecollation_connectionsystem variable when the trigger was created.DATABASE_COLLATIONis the collation of the database with which the trigger is associated. These columns were added in MySQL 5.1.21.The following columns currently always contain
NULL:TRIGGER_CATALOG,EVENT_OBJECT_CATALOG,ACTION_CONDITION,ACTION_REFERENCE_OLD_TABLE,ACTION_REFERENCE_NEW_TABLE, andCREATED.
Example, using the ins_sum trigger defined in
Section 19.3, “Using Triggers”:
mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: test
TRIGGER_NAME: ins_sum
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
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
User Comments
This is one of the most important help thank you
--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.