标签：NEW.COLUMN,OLDCOLUMN
概述
文章内容主要讲述的是insert,update,delete触发器，如果之前有使用其它产品数据库产品还是有的地方需要注意，例如sqlserver使用inserted获取新插入的值，而mysql是使用NEW.COLUMN来获取。
只能对永久表创建触发器，不能在临时表中创建触发器；同一个表不能存在两个相同类型的触发器，例如不能存在两个insert触发器，对应update触发器可以通过IF 不同的字段执行不一样的操作。
当前测试版本：mysql 5.6.21。
目录
步骤
#产品表 CREATE TABLE Product (proID INT AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '商品表主键', price DECIMAL(10,2) NOT NULL COMMENT '商品价格', type INT NOT NULL COMMENT '商品类别(0生鲜,1食品,2生活)', dtime DATETIME NOT NULL COMMENT '创建时间' )AUTO_INCREMENT=1 COMMENT='商品表'; #商品类别汇总表 CREATE TABLE ProductType (ID INT NOT NULL COMMENT '商品类别(0生鲜,1食品,2生活)', amount INT NOT NULL COMMENT '每种类别商品总金额', PRIMARY KEY (ID) )COMMENT='商品类别资金汇总表'; #产品价格变动表 CREATE TABLE Product_log (ID INT AUTO_INCREMENT NOT NULL COMMENT '主键', productid INT NOT NULL COMMENT '产品id', newprice DECIMAL(10,2) COMMENT '更改后的价格', oldprice DECIMAL(10,2) COMMENT '更改前的价格', PRIMARY KEY(ID) )AUTO_INCREMENT=1 COMMENT='产品价格变动表'; #插入测试数据 INSERT INTO ProductType VALUES(1,0.00),(2,0.00),(3,0.00);
INSERT触发器
insert触发器只有NEW.Column.
在Product表中建立INSERT触发器，当往Product表中插入产品时，更新ProductType表对应的分类商品价格。
DELIMITER $$ CREATE TRIGGER TR_Product_insert AFTER INSERT ON Product FOR EACH ROW BEGIN UPDATE ProductType SET amount=amount+NEW.price WHERE ID=NEW.type; END $$ DELIMITER ;
插入测试数据
INSERT INTO Product(price,type,dtime) VALUES(10.00,1,NOW()),(10.00,1,NOW()),(10.00,2,NOW()),(10.00,3,NOW()); SELECT * FROM Product; SELECT * FROM ProductType;
UPDATE触发器
update触发器中NEW.column代表更新后的值，OLD.column代表更新前的值。
#UPDATE触发器 DELIMITER $$ CREATE TRIGGER TR_Product_updat AFTER UPDATE ON Product FOR EACH ROW BEGIN IF NEW.price<>OLD.price THEN#当价格发生变化时生成一条价格变动的日志信息插入Product_log表 INSERT INTO Product_log(productid,newprice,oldprice) VALUES(NEW.proID,NEW.price,OLD.price); ELSE IF NEW.type<>OLD.type THEN#当产品类型发生改变时更新ProductType表对应的类别 UPDATE ProductType SET amount=amount+(SELECT price FROM Product WHERE proID=NEW.proid) WHERE ID=NEW.type; UPDATE ProductType SET amount=amount-(SELECT price FROM Product WHERE proID=NEW.proid) WHERE ID=OLD.type; END IF; END IF; END $$ DELIMITER ;
测试数据
UPDATE Product SET price=40.00 WHERE proid=4;
UPDATE Product SET type=2 WHERE proid=4;
DELETE触发器
DELIMITER $$ CREATE TRIGGER TR_product_delete BEFORE DELETE ON product FOR EACH row BEGIN UPDATE producttype SET amount=amount-(SELECT price FROM product WHERE proID=OLD.proID) WHERE ID=OLD.type; END $$ DELIMITER ;
测试数据
DELETE FROM product WHERE proID=4;
完善语句
#NEW.Column:获取新插入的列值，OLD.column:获取更新列的旧值 #产品表 CREATE TABLE Product (proID INT AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '商品表主键', price DECIMAL(10,2) NOT NULL COMMENT '商品价格', type INT NOT NULL COMMENT '商品类别(0生鲜,1食品,2生活)', dtime DATETIME NOT NULL COMMENT '创建时间' )AUTO_INCREMENT=1 COMMENT='商品表'; #商品类别汇总表 CREATE TABLE ProductType (ID INT NOT NULL COMMENT '商品类别(0生鲜,1食品,2生活)', amount INT NOT NULL COMMENT '每种类别商品总金额', PRIMARY KEY (ID) )COMMENT='商品类别资金汇总表'; #产品价格变动表 CREATE TABLE Product_log (ID INT AUTO_INCREMENT NOT NULL COMMENT '主键', productid INT NOT NULL COMMENT '产品id', newprice DECIMAL(10,2) NOT NULL COMMENT '更改后的价格', oldprice DECIMAL(10,2) NOT NULL COMMENT '更改前的价格', remark VARCHAR(20) NOT NULL COMMENT '价格更改备注', dtime DATETIME NOT NULL DEFAULT current_timestamp COMMENT '记录插入的时间', PRIMARY KEY(ID) )AUTO_INCREMENT=1 COMMENT='产品价格变动表'; #插入测试数据 INSERT INTO ProductType VALUES(1,0.00),(2,0.00),(3,0.00); DELIMITER $$ CREATE TRIGGER TR_Product_insert AFTER INSERT ON Product FOR EACH ROW BEGIN UPDATE ProductType SET amount=amount+NEW.price WHERE ID=NEW.type; END $$ DELIMITER ; #UPDATE触发器 DROP TRIGGER IF EXISTS TR_Product_updat; DELIMITER $$ CREATE TRIGGER TR_Product_updat AFTER UPDATE ON Product FOR EACH ROW BEGIN IF NEW.price>OLD.price THEN#当价格发生变化时生成一条价格变动的日志信息插入Product_log表 INSERT INTO Product_log(productid,newprice,oldprice,remark) VALUES(NEW.proID,NEW.price,OLD.price,'加价'); UPDATE ProductType SET amount=amount+NEW.price-OLD.price WHERE ID=NEW.type; ELSE IF NEW.price<OLD.price THEN INSERT INTO Product_log(productid,newprice,oldprice,remark) VALUES(NEW.proID,NEW.price,OLD.price,'减价'); UPDATE ProductType SET amount=amount-OLD.price+NEW.price WHERE ID=NEW.type; END IF; END IF; IF NEW.type<>OLD.type THEN#当产品类型发生改变时更新ProductType表对应的类别 INSERT INTO Product_log(productid,newprice,oldprice,remark) VALUES(NEW.proID,NEW.price,OLD.price,'产品类型变更'); UPDATE ProductType SET amount=amount+NEW.price WHERE ID=NEW.type; UPDATE ProductType SET amount=amount-NEW.price WHERE ID=OLD.type; END IF; END $$ DELIMITER ; #DELETE触发器 DELIMITER $$ CREATE TRIGGER TR_product_delete BEFORE DELETE ON product FOR EACH row BEGIN UPDATE producttype SET amount=amount-(SELECT price FROM product WHERE proID=OLD.proID) WHERE ID=OLD.type; END $$ DELIMITER ; INSERT INTO product(price,type,dtime) VALUES(10.00,1,NOW()),(10.00,1,NOW()),(10.00,1,NOW()),(10.00,2,NOW()),(10.00,3,NOW()),(10.00,4,NOW()); #测试数据 UPDATE product SET price=30 WHERE proID=5; #测试价格变动 UPDATE product SET price=20 WHERE proID=5; #测试产品类型变动 UPDATE product SET type=2 WHERE proID=5;
总结
文章在INSTER和UPDATE触发器中用的是AFTER方式的触发，AFTER触发就是在执行命令操作之后执行触发操作；在DELETE触发器中用到的是BEFORE触发，BEFORE触发就是在执行操作命令之前执行触发操作。
文章如果对大家有帮助，请帮忙推荐，谢谢！！！
备注： 作者：pursuer.chen 博客：http://www.cnblogs.com/chenmh 本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接，否则保留追究责任的权利。 《欢迎交流讨论》 |
---恢复内容结束---
本文转自pursuer.chen(陈敏华)博客园博客，原文链接：http://www.cnblogs.com/chenmh/p/4978153.html，如需转载请自行联系原作者
