触发器与存储过程

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

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

mysql> delete from orders where oid=1;

3.mysql> create trigger order_update_after_trigger after
    -> update on orders for each row
    -> update goods set num=num+(new.onum-old.onum)
    -> where gid=old.gid;

mysql> insert into orders(gid,name,price,onum,otime) value('D0001','计算器',28,20,now());

mysql> update orders set onum=10 where gid='D0001';

存储过程

1.mysql> create procedure proc20()
    -> begin
    ->      select name,incoming from emp_new;
    -> end //
Query OK, 0 rows affected (0.01 sec)

@age会话变量用于接收传回的值

3.mysql> create procedure s3(in in_dept int,out avg_sal float)
    -> begin
    ->      select avg(incoming) into avg_sal from emp_new where dept=in_dept;
    -> end //