135°

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

触发器 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

全部评论: 0

    我有话说: