{"id":8457,"date":"2019-09-06T03:06:10","date_gmt":"2019-09-06T10:06:10","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=8457"},"modified":"2023-10-06T18:44:01","modified_gmt":"2023-10-07T01:44:01","slug":"mysql-show-triggers","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-triggers\/mysql-show-triggers\/","title":{"rendered":"MySQL SHOW TRIGGERS"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL SHOW TRIGGERS<\/code> statement to show all triggers in a MySQL Server.<\/p>\n\n\n\n

Introduction to MySQL SHOW TRIGGER statement<\/h2>\n\n\n\n

The SHOW TRIGGERS<\/code> statement list triggers defined for tables in the current database. The following illustrates the basic syntax of the SHOW TRIGGERS<\/code> statement:<\/p>\n\n\n

SHOW<\/span> TRIGGERS<\/span>\n[{FROM<\/span> | IN<\/span>} database_name]\n[LIKE<\/span> 'pattern'<\/span> | WHERE<\/span> search_condition];<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, if you don’t use the last two clauses, the SHOW TRIGGERS<\/code> returns all triggers in all databases:<\/p>\n\n\n
SHOW<\/span> TRIGGERS<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To show all triggers in a specific database, you specify the database name after the FROM<\/code> or IN<\/code> keyword like this:<\/p>\n\n\n
SHOW<\/span> TRIGGERS<\/span> \nFROM<\/span> database_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
or<\/p>\n\n\n
SHOW<\/span> TRIGGERS<\/span>\nIN<\/span> database_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To list triggers according to a pattern, you use the LIKE<\/code> clause:<\/p>\n\n\n
SHOW<\/span> TRIGGERS<\/span> \nLIKE<\/span> 'pattern'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
or<\/p>\n\n\n
SHOW<\/span> TRIGGERS<\/span> \nFROM<\/span> database_name\nLIKE<\/span> 'pattern'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The meaning of the LIKE<\/a><\/code> clause is the same as in the SELECT<\/a><\/code> statement.<\/p>\n\n\n\n
To find triggers that match a condition, you use the WHERE<\/code> clause:<\/p>\n\n\n
SHOW<\/span> TRIGGERS<\/span> \nWHERE<\/span> search_condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
or<\/p>\n\n\n
SHOW<\/span> TRIGGERS<\/span> \nFROM<\/span> database_name\nWHERE<\/span> search_condition;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The SHOW TRIGGERS<\/code> statement returns a result set that includes the following columns:<\/p>\n\n\n\n
    \n
  • trigger<\/strong>: the name of the trigger<\/li>\n\n\n\n
  • event<\/strong>: the event that invokes the trigger e.g., INSERT<\/code>, UPDATE<\/code>, or DELETE<\/code>.<\/li>\n\n\n\n
  • table<\/strong>: the table to which the trigger belongs.<\/li>\n\n\n\n
  • statement<\/strong>: the body of the trigger.<\/li>\n\n\n\n
  • timing<\/strong>: the activation time of the trigger, either BEFORE<\/code> or AFTER<\/code>.<\/li>\n\n\n\n
  • created<\/strong>: the created time of the trigger.<\/li>\n\n\n\n
  • sql_mode<\/strong>: the SQL_MODE<\/code> when the trigger executes.<\/li>\n\n\n\n
  • definer<\/strong>: the user account that created the trigger.<\/li>\n\n\n\n
  • character_set_client<\/strong><\/li>\n\n\n\n
  • collation_connection<\/strong><\/li>\n\n\n\n
  • database collation<\/li>\n<\/ul>\n\n\n\n
    Notice that to execute the SHOW TRIGGERS<\/code> statement, you need to have the SUPER<\/code> privilege.<\/p>\n\n\n\n
    MySQL SHOW TRIGGER statement examples<\/h2>\n\n\n\n
    The following example uses the SHOW TRIGGERS<\/code> statement to get all the triggers in all databases in the current MySQL Server:<\/p>\n\n\n
    SHOW<\/span> TRIGGERS<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The following example shows all triggers in the classicmodels<\/code> database:<\/p>\n\n\n
    SHOW<\/span> TRIGGERS<\/span>\nFROM<\/span> classicmodels;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The following statement list all the triggers associated with the employees<\/code> table:<\/p>\n\n\n
    SHOW<\/span> TRIGGERS<\/span>\nFROM<\/span> classicmodels\nWHERE<\/span> table<\/span> = 'employees'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the SHOW TRIGGERS<\/code> statement to get all the triggers<\/li>\n\n\n\n
    • Use the SHOW TRIGGERS FROM<\/code> statement to get all triggers in the database.<\/li>\n\n\n\n
    • Use the SHOW TRIGGERS FROM ... WHERE ...<\/code> to get all triggers associated with a table. <\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t