MySQL的触发器使用总结
触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。
在MySQL中，创建触发器语法如下：
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
其中：
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。
由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER 、DELETE。
另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。
一、MySQL触发器的应用案例-1
/*创建account表*/
CREATE TABLE account
(
acct_num INT,
amount DECIMAL(10,2)
);
/*创建myname表*/
CREATE TABLE myname(
id int(11) DEFAULT NULL,
name char(20) DEFAULT NULL
);
要求： 在向表account新增数据后，便会触发myname表新增一条记录1 ,'after insert'
CREATE TRIGGER trig_insert AFTER INSERT ON account
FOR EACH ROW INSERT INTO myname VALUES (1,'after insert');
二、MySQL触发器的应用案例-2
有如下2张表：
CREATE TABLE `tab1` (
`tab1_id` varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
insert into `tab1`(`tab1_id`) values ('aaa'),('bbb'),('222'),('333');
CREATE TABLE `tab2` (
`tab2_id` varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
insert into `tab2`(`tab2_id`) values ('222'),('333');
要求：删除tab1表记录后自动将tab2表中对应的记录删去
DELIMITER //
CREATE TRIGGER test1
AFTER DELETE ON tab1
FOR EACH ROW
BEGIN
DELETE FROM tab2 WHERE tab2_id=old.tab1_id;
END
//
DELIMITER ;
三、MySQL触发器的应用案例-3
建立一个商品表和商品日志表，在删除商品的同时在商品日志表新增一条删除的记录
CREATE TABLE `commodity` (
`c_id` varchar(20) DEFAULT NULL,
`c_name` varchar(20) DEFAULT NULL,
`c_num` int(11) DEFAULT NULL,
`c_price` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
CREATE TABLE `commodity_log` (
`c_id` varchar(20) DEFAULT NULL,
`c_name` varchar(20) DEFAULT NULL,
`c_num` int(11) DEFAULT NULL,
`c_price` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
CREATE TRIGGER trigger_commodity AFTER DELETE ON commodity
FOR EACH ROW
INSERT INTO commodity_log VALUES(old.c_id,old.c_name,old.c_num,old.c_price);
在执行删除操作
DELETE FROM commodity
查看日志表
SELECT * FROM commodity_log
四、MySQL触发器的应用案例-4
有如下表：
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
要求：创建一个单执行语句的触发器
/*创建触发器*/
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
/*调用触发器*/
SET @sum =0;
INSERT INTO account VALUES(1,1.00), (2,2.00);
/*查看触发器调用后的结果*/
SELECT @sum;
五、MySQL触发器的应用案例-5
设有4个测试数据表：
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 ;
/*分别向test3和test4表中插入测试数据*/
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);
/*向test1表中插入数据，激活触发器的调用事件调用触发器*/
INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);
/*查看触发器执行后的结果*/
SELECT * FROM test1;
SELECT * FROM test2;
SELECT * FROM test3;
SELECT * FROM test4;
六、MySQL触发器的应用案例-6
通过SHOW TRIGGERS命令查看一个触发器
/*创建myname表*/
CREATE TABLE myname(
id int(11) DEFAULT NULL,
name char(20) DEFAULT NULL
);
/*创建触发器*/
CREATE TRIGGER trig_update AFTER UPDATE ON account
FOR EACH ROW INSERT INTO myname VALUES (1,'after update');
/*查看触发器信息*/
SHOW TRIGGERS \G;
七、MySQL触发器的应用案例-7
创建一个在account表更新之后，更新myname数据表的触发器
/*创建触发器*/
CREATE TRIGGER trig_insert AFTER INSERT ON account
FOR EACH ROW INSERT INTO myname VALUES (2,'after insert');
/*激活触发器*/
INSERT INTO account VALUES (1,1.00), (2,2.00);
/*查看触发器执行后的结果*/
SELECT * FROM myname;