Shulou(Shulou.com)06/01 Report--

In this paper, an example is given to describe the operation of adding, deleting, modifying and searching the trigger of mysql trigger. Share with you for your reference, the details are as follows:

After we create the trigger, we can display its definition in the data folder that contains the trigger definition file. Triggers are stored as plain text files in the following database folders:

/ data_folder/database_name/table_name.trg

We can also display triggers by querying the triggers table in the information_schema database, as follows:

SELECT * FROM information_schema.triggersWHERE trigger_schema = 'database_name' AND trigger_name =' trigger_name'

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

If you want to retrieve all triggers in the specified database, you need to query the data from the triggers table in the information_schema database using the following SELECT statement:

SELECT * FROM information_schema.triggersWHERE trigger_schema = 'database_name'

To find all triggers associated with a particular table:

SELECT * FROM information_schema.triggersWHERE trigger_schema = 'database_name' AND event_object_table =' table_name'

Let's take a look at all the triggers associated with the employees table:

SELECT * FROM information_schema.triggersWHERE trigger_schema = 'your database name' AND event_object_table = 'employees'

In fact, another way to display triggers in a particular database is to use the SHOW TRIGGERS statement, the syntax is as follows:

SHOW TRIGGERS [FROM | IN] database_ name [like expr | WHERE expr]

For example, if you want to see all the triggers in the current database, you can use the SHOW TRIGGERS statement, as follows:

SHOW TRIGGERS

To get all triggers in a specific database, specify the database name in the SHOW TRIGGERS statement, such as to query all triggers under database: luyaran, as follows:

SHOW TRIGGERS FROM luyaran

To get all the triggers associated with a particular table, you can use the WHERE clause in the SHOW TRIGGERS statement. The following statement returns all triggers associated with the employees table:

SHOW TRIGGERS FROM luyaranWHERE `table` = 'employees'

Note here that when we use backquotes to wrap table columns, because table is a reserved keyword in MySQL. Then, when the SHOW TRIGGERS statement is executed, MySQL returns with the following:

Trigger: the name of the storage trigger, such as before_employee_update trigger. Event: specify the event, for example, the INSERT,UPDATE or DELETE that calls the trigger. Table: specifies the table that the trigger is associated with for example, such as the employees table. Statement: stores the statement or compound statement to be executed when the trigger is called. Timing: accepts two values: BEFORE and AFTER, which specify the activation time of the trigger. Created: records the time of creation when the trigger is created. Sql_mode: specifies the SQL mode when the trigger executes. Definer: record the account that created the trigger.

Here we have to note that to allow the above statements, we must at least have SUPER permission.

When we're done, let's try to use the DROP TRIGGER statement to delete the existing trigger:

DROP TRIGGER table_name.trigger_name

If you want to delete the before_employees_update trigger associated with the employees table, you can execute the following statement:

DROP TRIGGER employees.before_employees_update

If we want to modify the trigger, we must first delete it and recreate it with the new code. Because there is no similar: ALTER TRIGGER statement in MySQL, we cannot modify triggers like other database objects, such as tables, views, and stored procedures.

All right, that's all for this record.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection", "MySQL Database Lock related skills Summary" and "MySQL Common function Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.