mysql 触发器

1、触发器:trigger
        1、触发器创建语法4要素:
            1、监视地点:table
            2、监视事件:insert / update / delete
            3、触发时间:after / before
            4、触发事件:insert / update / delete
         
        2、需求:
            1、商品表:goods
                 订单表:ord
                 当下1个订单时对应的商品要减少(买几个商品就减少几个库存)
            
            2、分析:
                    监视地点:ord
                    监视事件:insert
                    触发时间:after
                    触发事件:update
            
            3、创建触发器的语法实例:
                    delimiter  $       //修改定界符
                    
                    create trigger t1
                    after
                    insert
                    on ord
                    for each row
                    begin
                        SQL1 ; SQL2 ........;
                    end;
                    $
            4、查看触发器:show trigger t1;
                 删除触发器:drop trigger t1;
                 
            5、引用行变量:old 和 new
                    insert : 只能引用 new
                    delete:只能引用old
                    update: 两者都可以使用
     
            6、应用实例:
                1、创建表:
                    create table goods (
                        gid int,
                        name varchar(20),
                        num smallint
                    );
                    create table ord (
                        oid int,
                        gid int,
                        much smallint
                    );
                    insert into goods values
                    (1, ‘cat‘, 34),
                    (2, ‘dog‘, 65),
                    (3, ‘pig‘, 21);
                2、创建触发器:
                    1、增加订单:
                            create trigger t1  after  insert on  ord  for each row
                            begin
                                /* 这一个语句是有问题的,*/
                                update goods set num = num - 2 where gid = 1;
                                /* 下面这个才是对的 :被监视的语句是否能在触发器中被引用到*/
                                update goods set num = num - new.much where gid = new.gid;
                            end;
                            $  
                    2、删除订单:
                            create trigger t2 after delete on ord for each row
                            begin
                                update goods set num = num +old.much where gid = old.gid;
                            end;
                            $
                    3、修改订单(仅限数量):
                            create trigger t3 before delete on ord for each row
                            begin
                                update goods set num = num - new.much +old.much where gid = old.gid;
                            end;
                            $
                            思考:before 目前似乎没有看出与 after 的区别?答:insert 之后 new 行已经插入到表中,之后的IF操作就无效了
                            再思考:如果剩余 3 头猪,但是客户买了 10 头猪,发生什么情况?能否预防?
                            能否在购买量 much > 库存量 num 时,把 much 自动改为 num
                            提示:before 下手
                                create trigger t5  before insert on  ord  for each row
                                begin
                                    declare  rnum int;
                                    select num into rnum from goods where gid =  new.gid;
                                    if new.much > rnum then
                                        set new.much  = rnum;
                                    end if;
                                    
                                        update goods set num = num - new.much where gid = new.gid;
                                end;
                                $  
        
        3、触发器中 for each row 是干嘛的?
                解释:行级触发器,每一行受影响,触发器都执行
             在 oracle 中,如果不写,则无论影响多少行,都只会执行一次:语句级触发器
                遗憾的是:mysql 目前不支持语句级触发

mysql 触发器,布布扣,bubuko.com

mysql 触发器

上一篇:连接sql server的语句


下一篇:连接access的语句