Mysql 触发器

标签：NEW.COLUMN,OLDCOLUMN

概述  

文章内容主要讲述的是insert,update,delete触发器，如果之前有使用其它产品数据库产品还是有的地方需要注意，例如sqlserver使用inserted获取新插入的值，而mysql是使用NEW.COLUMN来获取。

只能对永久表创建触发器，不能在临时表中创建触发器；同一个表不能存在两个相同类型的触发器，例如不能存在两个insert触发器，对应update触发器可以通过IF 不同的字段执行不一样的操作。

当前测试版本：mysql 5.6.21。

目录

•  概述
•  步骤
  •  INSERT触发器
  •  UPDATE触发器
  •  DELETE触发器
•  总结

步骤

 

#产品表
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

本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接，否则保留追究责任的权利。

《欢迎交流讨论》

 

---恢复内容结束---