mysql 触发器使用场景_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 触发器也是同样的道理