MySQL | MySQL触发器

目录

一、创建触发器

1.2 创建只有一个执行语句的触发器

1.2 创建有多个执行语句的触发器

二、查看触发器

2.1 利用SHOW TRIGGERS语句查看触发器信息

2.2 在triggers表中查看触发器信息

三、删除触发器


一、创建触发器

触发器是一个特殊的存储过程,不同的是,执行存储过程要使用CALL语句来调用,而触发器的执行不需要CALL语句来调用,也不需要手工启动,只要当一个预定义的事件发生的时候,就会被MySQL自动调用。

1.2 创建只有一个执行语句的触发器

创建一个触发器的语法如下:

CREATE TRIGGER tigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

其中,trigger_name表示触发器名称,用户自行指定;trigger_time表示触发时机,可以指定为before或after;trigger_event表示触发事件,包括INSERT、UPDATE和DELECT;tbl_name表示建立触发器的表名,即在哪张表上建立触发器;trigger_stmt是触发器执行语句。

例:创建一个单执行语句的触发器。首先创建一个account表,表中有两个字段,分别为acct_num字段(定义为int类型)和amount字段(定义为浮点类型);其次,创建一个ins_sum的触发器,触发的条件是向数据表account插入数据之前,对新插入的amount字段值进行求和计算。代码如下:

CREATE TABLE account (acct_num INT,amount DECIMAL(10,2));

CREATE TRIGGER ins_num BEFORE INSERT ON account FOR EACH ROW SET @num=@num+NEW.amount;

SET @num=0;
INSERT INTO account VALUES(1,1.00),(2,2.00);

SELECT @num;

结果如下:

@num
3.00

  

1.2 创建有多个执行语句的触发器

创建有多个执行语句的触发器的语法如下 :

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW 
BEGIN
语句执行列表
END

例:创建一个包含多个执行语句的触发器,代码如下:

CREATE TABLE test1 (a1 INT);
CREATE TABLE test2 (s2 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),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL);

INSERT INTO test4 (a4) VALUES
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);

INSERT INTO test1 VALUES
(1),(3),(1),(7),(1),(8),(4),(4);

最后四个表的数据如下:

a1
1
3
1
7
1
8
4
4

  

a2
1
3
1
7
1
8
4
4

a3
2
5
6
9
10

a4 b4
1 3
2 0
3 1
4 2
5 0
6 0
7 1
8 1
9 0
10 0

二、查看触发器

2.1 利用SHOW TRIGGERS语句查看触发器信息

SHOW TRIGGERS;
或
SHOW TRIGGERS G

2.2 在triggers表中查看触发器信息

在MySQL中,所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGER表格中,可以通过查询SELECT查看,具体的语法如下:

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition;

例:

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='trig_update'G

也可以不指定触发器名称,这样讲查看所有的触发器,命令如下:

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS G

三、删除触发器

使用DROP TRIGGER语句可以删除MySQL中已经定义的触发器,删除触发器语句的基本语法格式如下:

DROP TRIGGER [schema_name.]trigger_name

其中,schema_name表示数据库名称,是可选的。如果省略了schema,将从当前数据库中舍弃触发程序;trigger_name是要删除的触发器的名称。

例:删除一个触发器,代码如下:

DROP TRIGGER tset_db.ins_sum;