Example of adding deleting modifying and checking triggers of mysql triggers

This paper describes the operation of adding, deleting, modifying and checking triggers of mysql triggers. Share it for your reference, 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 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 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 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';

In fact, another way to display triggers in a specific database is to use the SHOW TRIGGERS statement with the following syntax:

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, for example, to query all triggers under the database: luyaran, as follows:

SHOW TRIGGERS FROM luyaran;

To get all the triggers associated with a particular table, 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 when we enclose the table column in reverse quotation marks, because table is a reserved keyword in MySQL. Then, when the SHOW TRIGGERS statement is executed, MySQL returns the following:

Trigger: Stores the name of the trigger, such as the before_employee_update trigger. Event: Specifies the event, for example, INSERT, UPDATE, or DELETE that invokes the trigger. Table: Specifies the table with which the trigger is associated, 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: Record the time of creation when triggers are created. sql_mode: Specifies the SQL mode when the trigger executes. Definer: Record the account under which the trigger was created.

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

Let's try using 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 new code. Because there is no such statement in MySQL: ALTER TRIGGER, we cannot modify triggers in the same way we modify other database objects, such as tables, views, and stored procedures.

Ok, that's all for this record.

More readers interested in MySQL can see the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Summary" and "MySQL Common Functions Summary"

I hope this article is helpful to everyone's MySQL database.