作业要求

触发器

mysql> create trigger after_order_insert
    -> after insert on orders
    -> for each row
    -> update goods set num = num - new.onum where gid = new.gid;

mysql> create trigger after_order_delete
    -> after delete on orders
    -> for each row
    -> update goods set num = num + old.onum where gid = old.gid;

mysql> create trigger after_order_update
    -> after update on orders
    -> for each row
    -> begin
    ->     if old.onum <> new.onum then
    ->         update goods set num = num - new.onum + old.onum where gid = new.gid;
    ->     end if;
    -> end$$

-- 插入订单

insert into orders (gid, name, price, onum, otime) values ('a0001', '橡皮', 2.5, 5, curdate());

-- 检查商品表中橡皮的数量

select num from goods where gid = 'a0001';

-- 删除订单

delete from orders where oid = 1;

-- 检查商品表中橡皮的数量

select num from goods where gid = 'a0001';

-- 更新订单 update orders set onum = 10 where oid = 1;

-- 检查商品表中橡皮的数量

select num from goods where gid = 'a0001';

存储过程

mysql> create procedure s1()
    -> select name, incoming from emp_new;

mysql>  create procedure s2(in emp_name varchar(50))
    -> select age from emp_new where name = emp_name;

mysql>  create procedure s3(in dept_id int)
    -> select avg(incoming) as avg_incoming from emp_new where sid = dept_id;

mysql> call s1();

mysql> call s2('张三');

mysql> call s3(101);