Mysql 触发器

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

标签: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,如需转载请自行联系原作者

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
关系型数据库 MySQL
Mysql基础第二十八天,使用触发器
Mysql基础第二十八天,使用触发器
28 0
Mysql基础第二十八天,使用触发器
|
1月前
|
存储 SQL 关系型数据库
【MySQL 数据库】10、MySQL 的触发器
【MySQL 数据库】10、MySQL 的触发器
20 0
|
2月前
|
SQL 存储 关系型数据库
MySQL技能完整学习列表8、触发器、视图和事务——1、触发器(Triggers)的使用——2、视图(Views)的创建和使用——3、事务(Transactions)的管理
MySQL技能完整学习列表8、触发器、视图和事务——1、触发器(Triggers)的使用——2、视图(Views)的创建和使用——3、事务(Transactions)的管理
34 0
|
3月前
|
SQL 关系型数据库 MySQL
MySQL触发器 使用案例
MySQL触发器 使用案例
29 0
|
4月前
|
SQL 关系型数据库 MySQL
MySQL之如何使用触发器
MySQL之如何使用触发器
40 1
|
4月前
|
SQL 关系型数据库 MySQL
⑩⑥ 【MySQL】详解 触发器TRIGGER,协助 确保数据的完整性,日志记录,数据校验等操作。
⑩⑥ 【MySQL】详解 触发器TRIGGER,协助 确保数据的完整性,日志记录,数据校验等操作。
37 0
|
4月前
|
SQL 关系型数据库 MySQL
My Sql 触发器
My Sql 触发器
|
7月前
|
存储 SQL 关系型数据库
MySQL存储过程和触发器
MySQL存储过程和触发器
78 0
|
7月前
|
存储 关系型数据库 MySQL
使用MySQL视图与触发器
使用MySQL视图与触发器。
41 4
|
7月前
|
关系型数据库 MySQL 数据库
记录一下mysql触发器的一个小知识点
记录一下mysql触发器的一个小知识点
记录一下mysql触发器的一个小知识点