Mysql触发器简明使用

触发器:trigger
创建触发器的语法
mysql> delimiter $
mysql> create trigger 触发器名称
after/before(触发时间)
insert/update/delete(监视事件)
on 表名(临视地址)
for each row
begin
sql1;
...
sqlN;
end$
mysql> delimiter ;

查看已有trigger: show trigger;
删除已有trigger: drop trigger 触发器名称

1.
表结构:
商品表:goods
订单表:ord
需求:
当下1个订单时,对应的商品要相应减少(买几个商品就少几个商品)
分析:
监视谁:ord
临视动作:insert
触发时间:之后
触发事件:update
语法:
create trigger t1
after
insert
on ord
for each row
begin
update goods xxxx
end;

例

 1 create table goods(
 2     gid int,
 3     name varchar(20),
 4     num int
 5 );
 6 create table ord(
 7     oid int,
 8     ggid int,
 9     muth int
10 );
11 
12 insert into goods values
13 (1,'cat',34),
14 (1,'dog',86),
15 (1,'pig',12);
16 
17 //添加时候
18 create trigger t1
19 after
20 insert
21 on ord
22 for each row
23 begin
24 update goods set num=num-new.muth where gid=new.ggid;
25 end;
26 
27 //删除时候
28 create trigger t2
29 after
30 delete
31 on ord
32 for each row
33 begin
34 update goods set num=num+old.muth where gid=old.ggid;
35 end;
36 
37 //修改时候
38 create trigger t3
39 before
40 update
41 on ord
42 for each row
43 begin
44 update goods set num=num+old.muth-new.muth where gid=old.ggid;
45 //或写成:update goods set num=num+old.muth-new.muth where gid=new.ggid;
46 end;
47 
48 # 如果购买量muth > 库存量num时,把muth自动改为num
49 
50 create trigger t4
51 before
52 insert
53 on ord
54 for each row
55 begin
56 # 申明变量
57 declare
58 rnum int;
59 # 判断
60 select num into rnum from goods where gid=new.ggid;
61 if new.muth > rnum then 
62     set new.muth=rnum;
63 end if;
64 
65 update goods set num=num+old.muth-new.muth where gid=old.ggid;
66 end;

例2:添加数据,触发别一个统计表,如存在就插入统计记录,如统计记录中有插入过就修改

 1 create trigger 触发器名称
 2 before
 3 insert
 4 on 触发的表
 5 for each row
 6 begin
 7 declare
 8 resNum int;
 9 select count(*) into resNum from 表名A where projectID=new.projectID and addDate=CURDATE();
10 if resNum=0 then
11     insert into 表名A (projectID,addDate,gtotal,stotal) values(new.projectID,CURDATE(),1,1);
12 else
13     update 表名A set gtotal=gtotal+1,stotal=stotal+1 where projectID=new.projectID and addDate=CURDATE();
14 end if;
15 end;

例:
插入

DELIMITER $$

USE `super`$$

DROP TRIGGER /*!50032 IF EXISTS */ `wu_autocount`$$

CREATE
    TRIGGER `wu_autocount` BEFORE INSERT ON `gbook_data` 
    FOR EACH ROW BEGIN
DECLARE resNum INT;
DECLARE nums INT;

IF new.status='1' THEN
SET nums=1;
ELSE
SET nums=0;
END IF;
SELECT COUNT(*) INTO resNum FROM gbook_autocount WHERE projectID=new.projectID AND ADDDATE=CURDATE();
IF resNum=0 THEN
    INSERT INTO gbook_autocount (projectID,ADDDATE,gtotal,stotal) VALUES(new.projectID,CURDATE(),1,nums);
ELSE
    UPDATE gbook_autocount SET gtotal=gtotal+1,stotal=stotal+nums WHERE projectID=new.projectID AND ADDDATE=CURDATE();
END IF;
END;
$$

DELIMITER ;

删除:
DELIMITER $$

USE `super`$$

DROP TRIGGER /*!50032 IF EXISTS */ `wu_autocount_del`$$

CREATE
    TRIGGER `wu_autocount_del` AFTER DELETE ON `gbook_data` 
    FOR EACH ROW BEGIN
    DECLARE num INT;
    SELECT gtotal INTO num FROM super.`gbook_autocount` WHERE `projectID`=old.projectID AND `addDate`=old.addDate;
    IF num<=1 THEN
    DELETE FROM super.`gbook_autocount` WHERE `projectID`=old.projectID AND `addDate`=old.addDate;
    ELSE
    IF old.status!='1' THEN
    UPDATE super.`gbook_autocount` SET gtotal=gtotal-1 WHERE `projectID`=old.projectID AND `addDate`=old.addDate;
    ELSE
    UPDATE super.`gbook_autocount` SET gtotal=gtotal-1,stotal=stotal-1 WHERE `projectID`=old.projectID AND `addDate`=old.addDate;
    END IF;
    END IF;
    END;
$$

DELIMITER ;


更新:
DELIMITER $$

USE `super`$$

DROP TRIGGER /*!50032 IF EXISTS */ `wu_autocount_update`$$

CREATE
    TRIGGER `wu_autocount_update` BEFORE UPDATE ON `gbook_data` 
    FOR EACH ROW BEGIN
IF new.status!=old.status THEN
    IF new.status!='1' && old.status='1' THEN
        UPDATE gbook_autocount SET stotal=stotal-1 WHERE projectID=new.projectID AND ADDDATE=old.ADDDATE;
    ELSEIF new.status='1' && old.status!='1' THEN
        UPDATE gbook_autocount SET stotal=stotal+1 WHERE projectID=new.projectID AND ADDDATE=old.ADDDATE;
    END IF;
END IF;
END;
$$

DELIMITER ;