# SHOW TRIGGERS

## Syntax

```sql
SHOW TRIGGERS [FROM db_name]
    [LIKE 'pattern' | WHERE expr]
```

## Description

{% tabs %}
{% tab title="Current" %}
`SHOW TRIGGERS` lists the triggers currently defined for tables in a database (the default database unless a `FROM` clause is given). This statement requires the [TRIGGER](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-privileges) privilege.
{% endtab %}

{% tab title="< 5.1.22" %}
`SHOW TRIGGERS` lists the triggers currently defined for tables in a database (the default database unless a `FROM` clause is given). This statement requires the `SUPER` privilege.
{% endtab %}
{% endtabs %}

The `LIKE` clause, if present on its own, indicates which table names to match and causes the statement to display triggers for those tables. The `WHERE` and `LIKE` clauses can be given to select rows using more general conditions, as discussed in [Extended SHOW](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/extended-show).

Similar information is stored in the [information\_schema.TRIGGERS](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-triggers-table) table.

If there are multiple triggers for the same action, then the triggers are shown in action order.

## Examples

For the trigger defined at [Trigger Overview](https://mariadb.com/docs/server/server-usage/triggers-events/triggers/trigger-overview):

```sql
SHOW triggers Like 'animals' \G
*************************** 1. row ***************************
             Trigger: the_mooses_are_loose
               Event: INSERT
               Table: animals
           Statement: BEGIN
 IF NEW.name = 'Moose' THEN
  UPDATE animal_count SET animal_count.animals = animal_count.animals+100;
 ELSE 
  UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
 END IF;
END
              Timing: AFTER
             Created: 2016-09-29 13:53:34.35
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
```

Listing all triggers associated with a certain table:

```sql
SHOW TRIGGERS FROM test WHERE `Table` = 'user' \G
*************************** 1. row ***************************
             Trigger: user_ai
               Event: INSERT
               Table: user
           Statement: BEGIN END
              Timing: AFTER
             Created:  2016-09-29 13:53:34.35
            sql_mode: 
             Definer: root@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
```

```sql
SHOW triggers WHERE Event Like 'Insert' \G
*************************** 1. row ***************************
             Trigger: the_mooses_are_loose
               Event: INSERT
               Table: animals
           Statement: BEGIN
 IF NEW.name = 'Moose' THEN
  UPDATE animal_count SET animal_count.animals = animal_count.animals+100;
 ELSE 
  UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
 END IF;
END
              Timing: AFTER
             Created: 2016-09-29 13:53:34.35
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
```

{% tabs %}
{% tab title="Current" %}
`character_set_client` is the session value of the [character\_set\_client](https://github.com/mariadb-corporation/mariadb-docs/blob/main/server-usage/replication-cluster-multi-master/optimization-and-tuning/system-variables/server-system-variables.md#character_set_client) system variable when the trigger was created.

`collation_connection` is the session value of the [collation\_connection](https://github.com/mariadb-corporation/mariadb-docs/blob/main/server-usage/replication-cluster-multi-master/optimization-and-tuning/system-variables/server-system-variables.md#collation_connection) system variable when the trigger was created.

`Database Collation` is the collation of the database with which the trigger is associated.
{% endtab %}

{% tab title="< 5.1.21" %}
`character_set_client`, `collation_connection`, and`Database Collation` are not available.
{% endtab %}
{% endtabs %}

{% hint style="info" %}
Old triggers created before MySQL 5.7 and MariaDB 10.2.3 have NULL in the `Created` column.
{% endhint %}

## See also

* [Trigger Overview](https://mariadb.com/docs/server/server-usage/triggers-events/triggers/trigger-overview)
* [CREATE TRIGGER](https://mariadb.com/docs/server/server-usage/triggers-events/triggers/create-trigger)
* [DROP TRIGGER](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-trigger)
* [information\_schema.TRIGGERS](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-triggers-table) table
* [SHOW CREATE TRIGGER](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-trigger)
* [Trigger Limitations](https://mariadb.com/docs/server/server-usage/triggers-events/triggers/trigger-limitations)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}