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

In this paper, an example is given to illustrate the simple use of MySQL trigger. Share with you for your reference, the details are as follows:

A mysql trigger, like a stored procedure, is a program embedded in mysql. A trigger is triggered by events, including INSERT,UPDATE,DELETE, not including SELECT.

Create trigger

CREATE TRIGGER name,time,event ON table_name FOR EACH ROW trigger_stmt

For example

The copy code is as follows: CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @ sum = @ sum + NEW.amount

There are multiple triggers that execute statements

CREATE TABLE test1 (A1 INT); CREATE TABLE test2 (a2 INT); CREATE TABLE test3 (a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE test4 (A4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0); DELIMITER / / CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 where A3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE A4 = NEW.a1; END / / DELIMITER INSERT INTO test3 (A3) VALUES (NULL), (NULL); INSERT INTO test4 (A4) VALUES (0), (0), (0), (0), (0), (0), (0), (0) / / start testing INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4)

View trigger

SHOW TRIGGERS\ G; / / View all SELECT * FROM information_schema.TRIGGERS where TRIGGER_NAME = 'testref'

Delete trigger

DROP TRIGGER testref

Comprehensive case

Step 1: create the persons table

CREATE TABLE persons (name VARCHAR (40), num int)

Step 2: create a sales table sales

CREATE TABLE sales (name VARCHAR (40), sum int)

Step 3: create a trigger

CREATE TRIGGER num_sum AFTER INSERT ON personsFOR EACH ROW INSERT INTO sales VALUES (NEW.name,7*NEW.num)

Step 4: insert a record into the persons table

INSERT INTO persons VALUES ('xiaoxiao',20), (' xiaohua',69); SELECT * FROM persons;SELECT * FROM sales

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection", "MySQL Database Lock related skills Summary" and "MySQL Common function Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

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.