数据库作业五

订单表中增加订单数量后，商品表商品数量同步减少对应的商品订单出数量
DELIMITER //
CREATE TRIGGER update_goods_num_after_insert_orders
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE goods
    SET num = num - NEW.onum
    WHERE gid = NEW.gid;
END //
DELIMITER ;

 客户取消订单，恢复商品表对应商品的数量
DELIMITER //
CREATE TRIGGER restore_goods_num_after_cancel_order
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    UPDATE goods
    SET num = num + OLD.onum
    WHERE gid = OLD.gid;
END //
DELIMITER ;

客户修改订单，商品表对应商品数量同步更新
DELIMITER //
CREATE TRIGGER update_goods_num_after_update_orders
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    UPDATE goods
    SET num = num - (NEW.onum - OLD.onum)
    WHERE gid = NEW.gid;
END //
DELIMITER ;

测试上述触发器
INSERT INTO orders (gid, name, price, onum, otime) VALUES ('A0001', '橡皮', 2.5, 10, '2024-07-26');

使用 mydb7_openlab 库
USE mydb16_trigger;

 创建提取 emp_new 表所有员工姓名和工资的存储过程 s1
DELIMITER //
CREATE PROCEDURE s1()
BEGIN
    SELECT name, salary FROM emp_new;
END //
DELIMITER ;

创建存储过程 s2，实现输入员工姓名后返回员工的年龄
DELIMITER //
CREATE PROCEDURE s2(IN emp_name VARCHAR(50))
BEGIN
    SELECT age FROM emp_new WHERE name = emp_name;
END //
DELIMITER ;

创建一个存储过程 s3，有 2 个参数，传入部门号，返回该部门的平均工资
DELIMITER //
CREATE PROCEDURE s3(IN dept_id INT, OUT avg_salary DECIMAL(10, 2))
BEGIN
    SELECT AVG(salary) INTO avg_salary FROM emp_new WHERE department_id = dept_id;
END //
DELIMITER ;