Shulou(Shulou.com)05/31 Report--

Today, I would like to share with you the relevant knowledge about how to use MYSQL database triggers. The content is detailed and the logic is clear. I believe most people still know too much about this, so share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

First take a look at triggers, and then discuss whether they can completely replace foreign keys. What is a trigger?

Concept: trigger is a method that SQL server provides to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by the program or started manually, but is triggered by events, such as activating it when an operation is performed on a table (insert,delete, update). Triggers are often used to strengthen data integrity constraints and business rules. Triggers can be found in the DBA_TRIGGERS, USER_TRIGGERS data dictionary. The trigger of SQL3 is a statement that can be automatically executed by the system to modify the database.

Generally speaking, a trigger is a trigger device in which there is an operation. The device has a trigger condition, which will be triggered when a certain condition is reached, and the trigger device will perform a stored operation.

How to set up a trigger in a database and establish a trigger instance (keyword: trigger)

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

Set up the user table first

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

Set up trigger

Establish the trigger named ttcreate TRIGGER tt-- trigger condition. When inserting data into the user table, start the trigger BEFORE insert on user-- to check each row in the table, and operate on the newly inserted data for EACH ROW-- to perform the operation BEGIN set new.id=UUID (); END

The trigger you 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 ID

Insert user (name) VALUE ('Zhang San'), ('Li Si'), ('Wang Wu')

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

Build a table

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

Set up trigger

Delimiter $- create a trigger named tqcreate TRIGGER tq1-- trigger condition. After the dd table deletes the data, start the trigger AFTER DELETE on user-- to check each row in the table, and operate on the newly inserted data for EACH ROW-- to perform the operation 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 follow the deletion

Delete Zhang San

Delete from user WHERE name=' Zhang San'

Can flip-flops completely replace foreign keys

Example 2 above, if the same effect can be achieved with foreign keys, does it mean that foreign keys can do it, triggers can do it, and triggers that foreign keys can't do it?

Compared with foreign keys, triggers have more flexibility and more functions, and can perform more functions to some extent, which can replace foreign keys and realize the functions of foreign keys.

Triggers can also be used to enforce referential integrity so that the relationships defined between tables are retained when rows are added, updated, or deleted in multiple tables. However, the best way to enforce referential integrity is to define primary and foreign key constraints in related tables. If you use a database diagram, you can create relationships between tables to automatically create foreign key constraints.

These are all the contents of the article "how to use MYSQL database triggers". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.