Complete mastery of MySQL triggers

This article brings you relevant knowledge about mysql, which mainly organizes issues related to triggers, including why triggers are needed, an overview of triggers, the creation of triggers, etc. Let’s take a look at the content below, I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

1. Why triggers are needed

Some tables are mutually exclusive Related, such as product table and inventory table, if we operate the data of the product table, the corresponding inventory table must be changed, so as 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. Overview of triggers

• MySQL supports triggers starting from version 5.0.2
• MySQL triggers are the same as stored procedures. It is a program embedded in the MySQL server.
• A trigger is a certain event that triggers an operation. These events include insert, delete, and update events.
• If a trigger program is defined, then the database When these statements are executed, it is equivalent to the occurrence of an event, which will automatically trigger the trigger to perform the corresponding operation.
• If you insert data into the table in the database, you need to automatically execute some database operations. When it comes to logic, we can use triggers to implement it.

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 indicates that it is triggered when a record is inserted;
  • UPDATE indicates that it is triggered 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, status and syntax of triggers that already exist in the database Information, etc.
• Method 1: View the definitions of all triggers in the current database

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

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

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 triggers

Triggers are also database objects, and triggers are also deleted using drop statements

drop trigger if exists 触发器名;

Recommended learning: mysql video tutorial

The above is the detailed content of Complete mastery of MySQL triggers. For more information, please follow other related articles on the PHP Chinese website!