MySQL触发器使用及总结

触发器 TRIGGER 
1、触发器的定义
2、使用的场景
3、掌握触发器的创建语法
4、理解触发器的触发机制

什么叫做触发器？
当一个表中的数据发生改变的时候，会引起其他表中相关数据改变，
编制一个小程序附着在表上，把这种改变自动化执行，成为触发器。

触发器的类型？
在进行insert、update、delete操作时，触发相关的insert、update、delete
触发器触发。分为：insert、update、delete触发器。

==============================
USE bigdata2;

CREATE TABLE goods
(
gid INT NOT NULL PRIMARY KEY,
gname VARCHAR(20),
gnum  INT
);

INSERT INTO goods VALUES (119,'香猪',80);
INSERT INTO goods VALUES (120,'臭狗',25);
INSERT INTO goods VALUES (121,'贼猫',34);
SELECT * FROM goods;

CREATE TABLE ords
(oid INT NOT NULL PRIMARY KEY,
gid INT,
gname VARCHAR(20),
onum INT
);

SELECT * FROM ords;

#定义一个insert触发器
DELIMITER $$
CREATE TRIGGER tri_ords_insert 
AFTER INSERT
ON ords
FOR EACH ROW
BEGIN
   UPDATE goods SET gnum=gnum-2 WHERE gid=120;
END$$
DELIMITER ;

SELECT * FROM goods;
SELECT * FROM ords;
INSERT INTO ords VALUES(1,120,'臭狗',2);

SELECT * FROM goods;
SELECT * FROM ords;

INSERT INTO ords VALUES(2,119,'香猪',30);

====================================
DROP TRIGGER tri_ords_insert;

#优化insert触发器
DELIMITER $$
CREATE TRIGGER tri_kz
AFTER INSERT
ON ords
FOR EACH ROW
BEGIN
  UPDATE goods SET gnum=gnum - new.onum WHERE gid=new.gid;
END$$
DELIMITER ;

SELECT * FROM ords;
SELECT * FROM goods;

INSERT INTO ords VALUES(5,121,'贼猫',12);

===================
#定义一个delete触发器
DELIMITER $$
CREATE TRIGGER tri_kz_delete
AFTER DELETE
ON ords
FOR EACH ROW
BEGIN
  UPDATE goods SET gnum=gnum+old.onum WHERE gid=old.gid;
END$$
DELIMITER ;

SELECT * FROM goods;
SELECT * FROM ords;

DELETE FROM ords WHERE oid=5;

======================================
#定义一个update触发器
#注意使用new  old
DELIMITER $$
CREATE TRIGGER tri_kz_updatetrigger
AFTER UPDATE
ON ords
FOR EACH ROW
BEGIN
   IF new.onum>old.onum THEN
        UPDATE goods SET gnum=gnum - (new.onum-old.onum) 
        WHERE gid=new.gid;
   ELSE
        UPDATE goods SET gnum=gnum + (new.onum-old.onum) 
        WHERE gid=new.gid;
   END IF;
END$$
DELIMITER ;

SELECT * FROM goods;
SELECT * FROM ords;
UPDATE ords SET onum=35 WHERE gid=119;

DROP TRIGGER tri_kz_updatetrigger;

=====================================
#定义一个日志记录表
CREATE TABLE klog
(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    kuser VARCHAR(20),
    kdate DATETIME,
    kdolist VARCHAR(50)
);

#定义一个insert 触发器在goods表上
DELIMITER $$
CREATE TRIGGER tri_goods_insert
AFTER INSERT
ON goods
FOR EACH ROW
BEGIN
  INSERT INTO klog(kuser,kdate,kdolist) 
  VALUES(USER(),NOW(),CONCAT('插入：',new.gid,new.gname,new.gnum));
END$$
DELIMITER ;

#定义一个delete 触发器在goods表上
DELIMITER $$
CREATE TRIGGER tri_goods_delete
AFTER DELETE
ON goods
FOR EACH ROW
BEGIN
  INSERT INTO klog(kuser,kdate,kdolist) 
  VALUES(USER(),NOW(),'删除一条记录....');
END$$
DELIMITER ;

#定义一个udpate触发器在goods表上
DELIMITER $$
CREATE TRIGGER tri_goods_udpate
AFTER UPDATE
ON goods
FOR EACH ROW
BEGIN
  INSERT INTO klog(kuser,kdate,kdolist) 
  VALUES(USER(),NOW(),'update一条记录....');
END$$
DELIMITER ;

SELECT * FROM klog;
SELECT * FROM goods;

#插入一条数据，触发插入触发器
INSERT INTO goods VALUES(130,'土贼',130);
SELECT * FROM goods;
SELECT * FROM klog;

#更改一条数据，触发update触发器
UPDATE goods SET gname='土狗土狗' WHERE gid=130;
SELECT * FROM goods;
SELECT * FROM klog;

#删除一条数据，触发delete触发器
DELETE FROM goods WHERE gid=130;
SELECT * FROM goods;
SELECT * FROM klog;

INSERT INTO goods VALUES(139,'丧尸',23);
SELECT * FROM goods;
SELECT * FROM klog;

#总结：
#1、触发器是定义在表上，被数据操作语句触发执行
#2、在insert 触发器上有new 对象的使用
#在delete 触发器上有old 对象的使用
#在update 触发器上有new 、 old 对象的使用
#3、每个表上，只有一个insert的触发器，不可能有两个insert 的触发器，
#对应的delete 、update 触发器也是同样的道理

转载于:https://my.oschina.net/alkz/blog/3060328