MySQL触发器学习

简介

MySQL从5.0.2版本开始支持触发器的功能。触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

创建触发器

语法：

　　CREATE TRIGGER trigger_name trigger_time trigger_event

　　　　ON tb_name FOR EACH ROW trigger_stmt

根据语法来演示如何创建一个触发器，首先创建2张表：student 和 student_demo

-- CREATE TABLE student
CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` tinyint(4) NOT NULL,
  `gender` enum('女','男') NOT NULL,
  `address` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- CREATE TABLE student_demo
CREATE TABLE `student_demo` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现在假设在表student新增一条记录后，student_demo也需新增一条记录，根据此需求来演示触发器的创建

-- CREATE TRIGGER ins_student
CREATE TRIGGER ins_student AFTER INSERT ON student FOR EACH ROW
BEGIN
    INSERT INTO student_demo (id, name, address)
    VALUES
     (
        new.id,
        new.name,
         new.address
     );
END;

现在为student表创建了AFTER INSERT的触发器，在向student表插入数据时，student_demo也会插入相应的记录

可以看到，在向student表中新增一个名为"韩梅梅"的学生信息时，student_demo也新增了其对应信息

对于INSERT INTO...ON DUPLICATE KEY UPDATE语句，触发触发器的顺序有所不同。对student表分别创建BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE，插入记录观察结果。

 1 -- 创建表tri_test
 2 CREATE TABLE tri_test(id INT auto_increment, note VARCHAR(20), PRIMARY KEY (id))
 3 
 4 -- 创建before insert触发器
 5 CREATE TRIGGER ins_student_bef BEFORE INSERT ON student FOR EACH ROW
 6 BEGIN
 7     INSERT INTO tri_test (note)
 8 VALUES
 9     ('before insert');
10 END;
11 
12 -- 创建after insert触发器
13 CREATE TRIGGER ins_student_aft AFTER INSERT ON student FOR EACH ROW
14 BEGIN
15     INSERT INTO tri_test (note)
16 VALUES
17     ('after insert');
18 END;
19 
20 -- 创建before update触发器
21 CREATE TRIGGER upd_student_bef BEFORE UPDATE ON student FOR EACH ROW
22 BEGIN
23     INSERT INTO tri_test (note)
24 VALUES
25     ('before update');
26 END;
27 
28 -- 创建after update触发器
29 CREATE TRIGGER upd_student_aft AFTER UPDATE ON student FOR EACH ROW
30 BEGIN
31     INSERT INTO tri_test (note)
32 VALUES
33     ('after update');
34 END;

View Code

 现在student表中已有一条记录

现在对id=3的记录，插入数据

INSERT INTO student
VALUES
    (
        3,
        '李雷',
        20,
        '男',
        '河北石家庄'
    ) ON DUPLICATE KEY UPDATE NAME = 'update record'

表student和tri_test中的数据为：

对于有重复记录、需要进行UPDATE的INSERT操作，触发器出发的顺序是BEFORE INSERT、BEFORE UPDATE、AFTER UPDATE

如果是插入新的不重复的数据

INSERT INTO student
VALUES
    (
        6,
        '大卫',
        20,
        '男',
        '河北保定'
    ) ON DUPLICATE KEY UPDATE NAME = 'update record'

则表数据为

触发器的顺序为BEFORE INSERT、AFTER INSERT

 

删除触发器

一次可删除一个触发器，如果没有指定则默认为当前数据库。语法如下：

DROP TRIGGER [schema_name.]trigger_name

例如，要删除触发器ins_student，可以执行

 

查看触发器

SHOW TRIGGERS

可以通过SHOW TRIGGERS命令来查看触发器的状态、语法等信息，此命令查询的是所有触发器的信息

查询系统表

通过查询系统表中的information_schema.triggers表，可以查询指定触发器的信息

查询指定触发器"ins_student"的信息