Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to set up MySQL database triggers". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to build MySQL database triggers.

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

Create 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, commodity name, user ID), and create a trigger tq1 (when a user's order is deleted, that user's order will also be deleted)

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 delete Zhang San with the deletion of the record in the dd table

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.

Summary: triggers can replace foreign keys in some cases, but not in all cases. Foreign keys and triggers can also be used together.

At this point, I believe that everyone on the "MySQL database trigger how to build" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.