Let's talk about MYSQL database triggers

This article brings you relevant knowledge about mysql, which mainly introduces related issues about triggers, including what is a trigger, how to create a trigger in the database, and trigger Whether the device can replace foreign keys, let's take a look at it. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

First understand the trigger, and then discuss whether it can be completely Instead of foreign keys

#What is a trigger?

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

• In layman terms, a trigger is a trigger device, and there is an operation in the device. This device has a trigger condition. When a certain condition is met, the device will be triggered, and the trigger device will execute a stored operation.

How to create a trigger in the database

Create a trigger instance (keyword: trigger)

Example 1: Create a user table (user ID, user name) and 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 and 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 a user is deleted, the The user's order 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 ;

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 follow Delete
Delete Zhang San

delete from user WHERE name='张三'

##Can triggers completely replace foreign keys

If the above example 2 can achieve the same effect using foreign keys, does it mean that triggers can do everything foreign keys can do, and triggers that foreign keys cannot do 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 also be used to enforce referential integrity so that when rows are added, updated, or deleted in multiple tables, the relationships defined between those tables are preserved. However, the best way to enforce referential integrity is to define primary key and foreign key constraints in the related tables. If you use a database diagram, you can create relationships between tables to automatically create foreign key constraints.

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

Recommended learning:

mysql video tutorial

The above is the detailed content of Let's talk about MYSQL database triggers. For more information, please follow other related articles on the PHP Chinese website!