What are the statements of mysql query triggers?

There are two statements for mysql query triggers: 1. "SHOW TRIGGERS [FROM database name];" statement, which can view the basic information of the current database or the specified database trigger. 2. The "SELECT * FROM information_schema.triggers WHERE trigger_name= 'trigger name';" statement is used to view the information of a specific trigger and obtain the content of the trigger and its metadata, such as the associated table name and definer.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Viewing triggers refers to viewing the definition, status and syntax information of triggers that already exist in the database.

There are two ways to view triggers in MySQL:

• SHOW TRIGGERS statement

• Query triggers under the information_schema database Data table, etc.

1. Use SHOW TRIGGERS statement to view trigger information

In MySQL, you can use SHOW TRIGGERS statement to view the basic information of the trigger, the syntax format is as follows:

SHOW TRIGGERS [FROM 数据库名];

FROM database name: is an optional statement, omit it if you want to view all triggers in the current database; Get all triggers in a specific database, do not omit them, and specify the database name.

Example 1

First create a data table account. There are two fields in the table, accnum of INT type and amount of DECIMAL type. The SQL statements and running results are as follows:

mysql> CREATE TABLE account(
    -> accnum INT(4),
    -> amount DECIMAL(10,2));
Query OK, 0 rows affected (0.49 sec)

Create a trigger named trigupdate, and insert a piece of data into the myevent data table every time the account table updates data. The SQL statement and running results to create the data table myevent are as follows:

mysql> CREATE TABLE myevent(
    -> id INT(11) DEFAULT NULL,
    -> evtname CHAR(20) DEFAULT NULL);
Query OK, 0 rows affected (0.26 sec)

The SQL code to create the trigupdate trigger is as follows:

mysql> CREATE TRIGGER trigupdate AFTER UPDATE ON account
    -> FOR EACH ROW INSERT INTO myevent VALUES(1,'after update');
Query OK, 0 rows affected (0.15 sec)

Use the SHOW TRIGGERS statement to view the trigger (add \ after the SHOW TRIGGERS command) G, displaying information in this way will be more organized), the SQL statement and running results are as follows:

mysql> SHOW TRIGGERS \G

You can see the basic information of the trigger from the running results. The description of the above displayed information is as follows:

• Trigger represents the name of the trigger, where the name of the trigger is trigupdate;

• Event represents The event that activates the trigger. The trigger event here is the update operation UPDATE;

• Table represents the operation object table that activates the trigger, here is the account table;

• Statement represents the operation performed by the trigger, here is to insert a piece of data into the myevent data table;

• Timing represents the time when the trigger is fired, here is after the update operation (AFTER );

• There are also some other information, such as the creation time of the trigger, SQL mode, trigger definition account and character set, etc., which will not be introduced one by one here.

The SHOW TRIGGERS statement is used to view information about all triggers currently created. Because this statement cannot query the specified trigger, it is convenient to use this statement when there are few triggers. If you want to view information about a specific trigger or there are many triggers in the database, you can directly search it from the triggers data table in the information_schema database.

2. View trigger information in the triggers table

In MySQL, all trigger information exists in the triggers table of the information_schema database , you can view it through the query command SELECT. The specific syntax is as follows:

SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';

Among them, 'trigger name' is used to specify the name of the trigger to be viewed and needs to be enclosed in single quotes. This method can query the specified trigger, which is more convenient and flexible to use.

This method allows you to view the contents of the trigger and its metadata, such as the associated table name and definer, which is the name of the MySQL user who created the trigger.

Example 2

The following uses the SELECT command to view the trigupdate trigger. The SQL statement is as follows:

SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME= 'trigupdate'\G

The above command uses WHERE to specify the trigger that needs to be viewed. The name of the trigger, the running result is as follows:

mysql> SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME= 'trigupdate'\G

You can see the detailed information of the trigger from the running result. The description of the above displayed information is as follows:

• TRIGGER_SCHEMA represents the database where the trigger is located;

• ##TRIGGER_NAME represents the name of the trigger;

• EVENT_OBJECT_TABLE indicates which data table the trigger is on;

• ACTION_STATEMENT indicates the specific operation performed when the trigger is triggered;

• The value of ACTION_ORIENTATION is ROW, which means it is triggered on every record;

• ACTION_TIMING means the triggering moment is AFTER;

• There are also some other information, such as the creation time of the trigger, the SQL mode, the definition account and character set of the trigger, etc., which will not be introduced one by one here.

上述 SQL 语句也可以不指定触发器名称，这样将查看所有的触发器，SQL 语句如下：

SELECT * FROM information_schema.triggers \G

这个语句会显示 triggers 数据表中所有的触发器信息。

【相关推荐：mysql视频教程】

The above is the detailed content of What are the statements of mysql query triggers?. For more information, please follow other related articles on the PHP Chinese website!