How to create and delete MySQL triggers

1. Why do we need triggers?

Some tables are related to each other, such as the product table and inventory table. We operate on the data of the product table, then The corresponding inventory table must also be changed to ensure the integrity of the data. It would be more troublesome if we maintained it manually ourselves.
At this time, we can use triggers to create a trigger so that the insertion operation of product information data automatically triggers the insertion operation of inventory data, etc., so that we do not need to worry about data loss due to forgetting to add inventory data.

2. Trigger overview

• MySQL supports triggers starting from version 5.0.2

• MySQL trigger The trigger, like the stored procedure, is a program embedded in the MySQL server

• The trigger is an event that triggers an operation. These events include insert, delete, and update events

• If a trigger program is defined, when the database executes these statements, it is equivalent to an event occurring, and the trigger will be automatically triggered to perform the corresponding operation

• If we need to automatically execute some database logic when inserting data into tables in the database, we can use triggers to achieve this.

3. Creation of triggers

3.1 Syntax

Triggers act on tables. For example, we want to add a new item to table A. The execution of the trigger is triggered when recording, and you also need to choose whether the trigger is executed before or after the insert statement is executed.

• For each row indicates that each time an event (insert, update or delete) is executed, a trigger will be triggered

CREATE TRIGGER 触发器名称 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
FOR EACH ROW 触发器执行的语句块;

Description:

• Table name: Indicates the object monitored by the trigger.

• BEFORE|AFTER: Indicates the trigger time. BEFORE means triggering before the event; AFTER means triggering after the event.

• INSERT|UPDATE|DELETE: Indicates the triggered event.

• INSERT means it triggers when a record is inserted;

• UPDATE means it triggers when a record is updated;

• DELETE indicates that it is triggered when a record is deleted.

• Statement block executed by trigger: It can be a single SQL statement or a compound statement block composed of BEGIN...END structure.

3.2 Case Demonstration

Prepare the table first

CREATE TABLE test_trigger (id INT PRIMARY KEY AUTO_INCREMENT,t_note VARCHAR(30));CREATE TABLE test_trigger_log (id INT PRIMARY KEY AUTO_INCREMENT,t_log VARCHAR(30));

Requirements: Create a trigger: Create a trigger named before_insert and insert it into the test_trigger data table Before data, insert the log information of before_insert into the test_trigger_log data table.

4. View triggers

• View triggers are to view the definition of triggers that already exist in the database. Status and syntax information, etc.

• Method 1: View the definitions of all triggers in the current database

show triggers\G 注意，在SQLyog中，不能加上\G

• Method 2: View the definition of a trigger in the current database

show create trigger 触发器名

• Method 3 :Query the "salary_check_trigger" trigger information from the TRIGGERS table of the system library information_schema

SELECT * FROM information_schema.TRIGGERS;

5. Delete the trigger

Triggers are also database objects, as are triggers Use drop statement to delete

drop trigger if exists 触发器名;

The above is the detailed content of How to create and delete MySQL triggers. For more information, please follow other related articles on the PHP Chinese website!