头歌数据库实验七:函数与触发器

第1关：定义、调用参数函数

use demo;

#代码开始
#定义、调用参数函数（func_sqty）：查询返回指定供应商的供应零件总数量。
#DROP FUNCTION func_sqty;

#SET GLOBAL log_bin_trust_function_creators = TRUE;
DELIMITER //
CREATE FUNCTION func_sqty (
    p_sno CHAR ( 2 )) RETURNS INT READS SQL DATA BEGIN
    DECLARE
        sumqty INT;
    SELECT
        sum( qty ) INTO sumqty 
    FROM
        spj 
    WHERE
        sno = p_sno;
    RETURN sumqty;
    
END//


#代码结束

select func_sqty('S1');
select func_sqty('S2');
select func_sqty('S3');

第2关：触发器应用（1）

use demo;

#定义一个触发器（tr_spj_insert），完成向SPJ表新增数据时，及时更新所有供应商供应零件总数量。
#代码开始
DELIMITER //
CREATE TRIGGER tr_spj_insert AFTER INSERT ON spj FOR EACH ROW
BEGIN
    DECLARE
        sumqty INT;
    SELECT
        sum( qty ) INTO sumqty 
    FROM
        spj 
    WHERE
        sno = new.sno;
    UPDATE s 
    SET sqty = sumqty 
    WHERE
        sno = new.sno;
    
END//





#代码结束

#以下代码不要改动或删除，将会对创建的触发器进行测试
insert into spj values
('S1','P1','J1',200),
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S2','P5','J1',400),
('S2','P5','J2',100),
('S3','P1','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',100),
('S4','P6','J3',300),
('S4','P6','J4',200),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',500); 

第3关：触发器应用（2）

use demo;

#定义一个触发器（tr_spj_delete），完成向SPJ表删除数据时，及时更新所有供应商供应零件总数量。
#代码开始
DELIMITER //
CREATE TRIGGER tr_spj_delete AFTER DELETE ON spj FOR EACH ROW
BEGIN
        DECLARE
        sumqty INT;
    SELECT
        sum( qty ) INTO sumqty 
    FROM
        spj 
    WHERE
        sno = old.sno;
    update s 
    SET sqty = sumqty 
    WHERE
        sno = old.sno;
    END//
#代码结束

#以下代码不要改动或删除，将会对创建的触发器进行测试
DELETE FROM spj WHERE sno='S1' AND pno='P1' AND jno='J1';

第4关：触发器应用（3）

use demo;

#定义一个触发器（tr_spj_update），完成向SPJ表更新数据时，及时更新所有供应商供应零件总数量。
#代码开始
DELIMITER //
CREATE TRIGGER tr_spj_update AFTER UPDATE ON spj FOR EACH ROW
BEGIN
        DECLARE
        sumqty INT;
    SELECT
        sum( qty ) INTO sumqty 
    FROM
        spj 
    WHERE
        sno = old.sno;
    update s 
    SET sqty = sumqty 
    WHERE
        sno = old.sno;
    SELECT
        sum( qty ) INTO sumqty 
    FROM
        spj 
    WHERE
        sno = new.sno;
    update s 
    SET sqty = sumqty 
    WHERE
        sno = new.sno;
    END//

#代码结束

#以下代码不要改动或删除，将会对创建的触发器进行测试
UPDATE spj SET sno='S1' WHERE sno='S2' AND pno='P3' AND jno='J1';