MySQL trigger trigger add, delete, modify and query operation example

This article uses examples to describe the add, delete, modify and query operations of MySQL triggers. Share with you for your reference, the details are as follows:

After we create a 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 the triggers by querying the triggers table in the information_schema database as follows:

SELECT 
  *
FROM
  information_schema.triggers
WHERE
  trigger_schema = 'database_name'
    AND trigger_name = 'trigger_name';

This statement allows you to view the contents of a 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 a specified database, you need to query data from the triggers table in the information_schema database using the following SELECT statement:

SELECT
  *
FROM
  information_schema.triggers
WHERE
  trigger_schema = 'database_name';

To find all triggers associated with a specific table:

SELECT 
  *
FROM
  information_schema.triggers
WHERE
  trigger_schema = 'database_name'
    AND event_object_table = 'table_name';

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

SELECT * FROM information_schema.triggers
WHERE trigger_schema = 'your database name'
    AND event_object_table = 'employees';

Another way to display triggers in a specific 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 view all 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. For example, to query all triggers under the database: luyaran, as shown below:

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 luyaran
WHERE `table` = 'employees';

Note here that we use backticks to wrap the table column because table is a reserved keyword in MySQL. Then, when you execute the SHOW TRIGGERS statement, MySQL returns the following columns:

• Trigger: stores the name of the trigger, such as before_employee_update trigger.
• Event: Specifies the event, for example, the INSERT, UPDATE, or DELETE that invoked the trigger.
• Table: Specifies the table that the trigger is associated with, 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 specifies the activation time of the trigger.
• Created: Records the creation time when the trigger is created.
• sql_mode: Specifies the SQL mode when the trigger is executed.
• Definer: Records the account that created the trigger.

We have to note here that to allow the above statements, we must have at least the SUPER privilege.

Now let's try to delete the existing trigger using the DROP TRIGGER statement:

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 a trigger, we must first drop it and recreate it with the new code. Because there is no ALTER TRIGGER statement in MySQL, we cannot modify triggers like we modify other database objects such as tables, views, and stored procedures.

Okay, that’s all for this record.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.