MySQL触发器及用户自定义函数

最新推荐文章于 2024-07-26 09:40:31 发布

weixin_34268843

最新推荐文章于 2024-07-26 09:40:31 发布

阅读量171

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/alkz/blog/3058389

版权

为什么80%的码农都做不了架构师？>>>

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

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

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

USE wlw;

CREATE TABLE goods
(
   gid INT NOT NULL PRIMARY KEY COMMENT '商品编号',
   gname VARCHAR(20) COMMENT '商品名称',
   kcnum DECIMAL(4,2) COMMENT '库存数量'
);

INSERT INTO goods VALUES(112,'肉猪',25);
INSERT INTO goods VALUES(113,'臭狗',56);
INSERT INTO goods VALUES(114,'瞎马',78);

SELECT * FROM goods;

CREATE TABLE dindans
(
ddh INT NOT NULL PRIMARY KEY COMMENT '订单号',
gid INT COMMENT '商品编号',
gname VARCHAR(20) COMMENT '商品名称',
ddnum DECIMAL(4,2) COMMENT '订单数量'
);

SELECT * FROM dindans;
=====================================
#insert触发器
DELIMITER $$
CREATE TRIGGER tri_insert
AFTER INSERT    #在插入之后
ON dindans        #在某个表上创建触发器
FOR EACH ROW   #insert的每一行
BEGIN
UPDATE goods
SET kcnum = kcnum - new.ddnum #new代表着新插入的这一行，new是一个行对象，相当于dindans表的一行，new.id就可以取得新插入行的id
WHERE gid=new.gid;
END$$
DELIMITER ;

DROP TRIGGER tri_insert;

SELECT * FROM goods;
SELECT * FROM dindans;

#插入数据后，就会触发触发器，在goods表中减少相应的数量
INSERT INTO dindans VALUES(1,114,'瞎马',5);

SELECT * FROM goods;
SELECT * FROM dindans;

INSERT INTO dindans VALUES(2,113,'臭狗',25);

============================
#delete触发器
DELIMITER $$
CREATE TRIGGER tri_del
AFTER DELETE
ON dindans
FOR EACH ROW
BEGIN
UPDATE goods SET kcnum=kcnum + old.ddnum #old代表着刚刚删除的这一行，old是一个行对象，old.id可以取得新删除这一行的id
WHERE gid=old.gid;
END$$

DELIMITER ;

SELECT * FROM dindans;
SELECT * FROM goods;
#用delete语句触发delete触发器，在删除之后，增加goods表的数量
DELETE FROM dindans WHERE gid=113;

========================
SELECT * FROM goods;
SELECT * FROM dindans;

INSERT INTO dindans VALUES(2,112,'肉猪',20);

DROP TRIGGER tri_insert;

#当订单的数量大于库存量的时候，我们在触发器中要有解决问题的
#办法，处理此类情况
DELIMITER $$
CREATE TRIGGER tri_insert
AFTER INSERT
ON dindans
FOR EACH ROW
BEGIN
DECLARE sl INT;
SELECT kcnum INTO sl FROM goods WHERE gid=new.gid;
IF sl<=new.ddnum THEN
UPDATE goods SET kcnum=0 WHERE gid=new.gid;
ELSE
UPDATE goods SET kcnum=kcnum-new.ddnum WHERE gid=new.gid;
END IF;
END$$
DELIMITER ;

SELECT * FROM goods;
SELECT * FROM dindans;
DROP TRIGGER tri_insert;
INSERT INTO dindans VALUES(3,112,'肉猪',5);

================================
#当更改订单表中的数据的时候，会触发订单表的数据，从而导致
#goods表的数据发生改变

SELECT * FROM goods;
SELECT * FROM dindans;

DELIMITER $$
CREATE TRIGGER tri_update
AFTER UPDATE
ON dindans
FOR EACH ROW
BEGIN
   IF old.ddnum >= new.ddnum THEN
   UPDATE goods SET kcnum=kcnum+old.ddnum - new.ddnum WHERE gid=new.gid;
   ELSE
   UPDATE goods SET kcnum =0;
   END IF;
END$$
DELIMITER ;

SELECT * FROM goods;
SELECT * FROM dindans;

UPDATE dindans SET ddnum=8 WHERE ddh=2;

====================================
做一个日志应用，使用触发器
SELECT * FROM goods;

DROP TABLE klog;
CREATE TABLE klog
(
   id INT PRIMARY KEY AUTO_INCREMENT,
   kusr VARCHAR(50),
   kcz VARCHAR(20),
   kgoodname VARCHAR(60),
   ktime DATETIME
);

#当对goods插入数据时，即可在klog中生成一条记录
DELIMITER $$
CREATE TRIGGER g_insert
AFTER INSERT
ON goods
FOR EACH ROW
BEGIN
INSERT INTO klog(kusr,kcz,kgoodname,ktime) VALUES(USER(),'正在insert...',new.gname,NOW());
END$$
DELIMITER ;

#当对goods删除数据时，即可在klog中生成一条记录
DELIMITER $$
CREATE TRIGGER g_delete
AFTER DELETE
ON goods
FOR EACH ROW
BEGIN
INSERT INTO klog(kusr,kcz,kgoodname,ktime) VALUES(USER(),'正在delete...',old.gname,NOW());
END$$
DELIMITER ;

#当对goods更新数据时，即可在klog中生成一条记录
DELIMITER $$
CREATE TRIGGER g_udpate
AFTER UPDATE
ON goods
FOR EACH ROW
BEGIN
INSERT INTO klog(kusr,kcz,kgoodname,ktime) VALUES(USER(),'正在update...',old.gname,NOW());
END$$
DELIMITER ;

SELECT * FROM goods;

INSERT INTO goods VALUES(115,'骆驼',13);
SELECT * FROM goods;
SELECT * FROM klog;
INSERT INTO goods VALUES(116,'孔雀',63);
INSERT INTO goods VALUES(117,'羊驼',234);

DELETE FROM goods WHERE gid=114;

UPDATE goods
SET kcnum=400
WHERE gid=117;

SELECT * FROM goods;

==================================
用户自定义函数

1、系统函数
SELECT NOW();
SELECT USER();

2、数据库函数
SELECT COUNT(*) FROM klog;

3、用户自定义函数
（1）定义
CREATE FUNCTION 函数名（）
RETURNS 返回值类型
BEGIN
RETURN 类型；
END;

定义函数
DELIMITER $$
CREATE FUNCTION hello()
RETURNS VARCHAR(20)
BEGIN
RETURN 'hello 你好！';
END$$
DELIMITER ;

调用函数
SELECT kusr,kcz,hello() FROM klog;

============================
DELIMITER $$
CREATE FUNCTION hellohaha(xm VARCHAR(20))
RETURNS VARCHAR(20)
BEGIN
RETURN CONCAT('你好！',xm);
END$$
DELIMITER ;

SELECT kusr,kcz,hellohaha(kusr) FROM klog;

SELECT xy,hellohaha(xm) FROM test.xuesheng;

SELECT * FROM emp;

SELECT empid,hellohaha(empname),income FROM emp;

==============================
DELIMITER $$
CREATE FUNCTION find_xh(arga VARCHAR(20))
RETURNS VARCHAR(20)
BEGIN
DECLARE xxh VARCHAR(20);
SELECT xh INTO xxh FROM test.xuesheng WHERE xm=arga;
IF ISNULL(xxh) THEN
       RETURN '没找到';
ELSE
       RETURN xxh;
END IF;

END$$
DELIMITER ;

DROP FUNCTION find_xh;

SELECT find_xh('王余昌');

SELECT find_xh('宋有国');

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