How to create a MySQL database trigger

What is a trigger?

Concept: Trigger is a method provided by SQL server to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not It is called by the program and is not started manually, but is triggered by events. For example, when a table is operated (insert, delete, update), its execution will be activated. Triggers are often used to enforce data integrity constraints and business rules. By querying the data dictionary DBA_TRIGGERS and USER_TRIGGERS, you can obtain trigger-related information. A SQL3 trigger is a statement that can be automatically executed by the system to modify the database.

In layman's terms, a trigger is a trigger device, and there is an operation in the device. Once certain conditions are reached, the device will be triggered and perform the actions stored within it.

How to create a trigger in the database

Create a trigger instance (keyword: trigger)

Example 1: Create a user table (user ID, user name), Create a trigger (when data is inserted into the user table, a globally unique ID is automatically generated)

Create the user table first

create table user(
id int PRIMARY KEY,
name varchar(20)
);

Create a trigger

-- 建立触发器名为tt
create TRIGGER tt
-- 触发条件，向user表中插入数据时启动触发器
BEFORE insert on user
-- 检查表中每一行，对新插入的数据进行操作
for EACH ROW
-- 执行操作
BEGIN 
set new.id=UUID();
END

The trigger just created (view the code show triggers of all triggers under the current database)

Effect: Insert three user names into the table , automatically generate three IDs

insert user(name) VALUE('张三'),('李四'),('王五')

Example 2: Create an order table DD (order ID, product name, user ID), and create a trigger tq1 (when deleted When there is a user, the user's orders will also be deleted) Create table

create table DD(
ddid int PRIMARY KEY,
ddname VARCHAR(20),
userid VARCHAR(50)
)

Create trigger

delimiter $
-- 建立触发器名为tq
create TRIGGER tq1
-- 触发条件，再dd表删除数据之后启动触发器
AFTER DELETE on user
-- 检查表中每一行，对新插入的数据进行操作
for EACH ROW
-- 执行操作
BEGIN  
DELETE FROM dd WHERE old.id=userid;
END $ 
delimiter ;

To Add two pieces of data to the table

Effect: Delete the user in the user table, and the records in the dd table will also be deleted. Delete Zhang San

delete from user WHERE name='张三'

Can triggers completely replace foreign keys?

• The above example 2 can also achieve the same effect if foreign keys are used. , so does it mean that triggers can do anything that foreign keys can do, and triggers that cannot be done by foreign keys can also do it?

• Compared with foreign keys, triggers have higher flexibility and more functions, and can perform more functions. To a certain extent, they can replace foreign keys and realize the functions of foreign keys.

• Triggers can be used to enforce referential integrity, ensuring that when rows are added, updated, or deleted in multiple tables, the relationships defined between these tables are maintained. Defining primary key and foreign key constraints is the best way to ensure referential integrity. Using relationship diagrams in a database, foreign key constraints can be automatically created by establishing relationships between tables.

Summary: Triggers can replace foreign keys at certain times, but not in all situations. Foreign keys and triggers can also be used together

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