mysql 触发器+存储过程

本文是工作的一些记录，并不适合学习和参考。

select max(id) 应该用last_insert_id()代替。

SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = '表名'      #用于查找某个表的最后一个主键值

p_policy_base表是策略的基础信息表，用于存储策略的基础信息。

p_timepolicy是时间策略表，表示一条具体的时间策略，与p_policy_base通过sn及fid形成非约束关联关系。

c_configs存储一些配置信息。

需求:

1.当对p_timepolicy执行insert操作时，自动向p_ploicy_base插入一条相关记录。

2.当对p_timepolicy执行update操作时，自动更新p_policy_base相应记录。

CREATE TABLE `c_configs` (
  `configid` int(11) NOT NULL AUTO_INCREMENT,
  `configtype` int(11) DEFAULT NULL,
  `configkey` varchar(32) DEFAULT NULL,
  `configvalue` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`configid`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

CREATE TABLE `p_policy_base` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `sn` varchar(20) NOT NULL DEFAULT '' COMMENT '策略编码',
  `name` varchar(60) NOT NULL DEFAULT '' COMMENT '策略名',
  `des` varchar(255) DEFAULT NULL COMMENT '短描述',
  `fid` int(11) DEFAULT '0' COMMENT '关联到具体策略表的id',
  `domainid` int(11) DEFAULT '0' COMMENT '域id,如果为空表示总部策略',
  `createTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '策略创建时间',
  `state` int(11) NOT NULL DEFAULT '0' COMMENT '策略状态,-1:系统默认策略,0:创建,1:发布,2:废弃',
  `editurl` varchar(150) DEFAULT NULL COMMENT '编辑时所用的url,这个url表示=以前的所有字符.',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='策略基本信息表';

CREATE TABLE `p_timepolicy` (
  `timepolicyid` int(11) NOT NULL AUTO_INCREMENT,
  `starttime` datetime DEFAULT NULL,
  `endtime` datetime DEFAULT NULL,
  `description` varchar(128) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `domainid` int(11) DEFAULT NULL,
  PRIMARY KEY (`timepolicyid`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

####  新定义的配置policysn
insert into c_configs (configtype,configkey,configvalue) values(33,'policysn','0');

insert into p_policy_base (sn,`name`, des,createTime) values('04','访问控制策略',null,now());

insert into p_policy_base (sn,`name`, des,createTime) values('040002','时间策略',null,now());

##### 向p_policy_base添加数据存储过程
DROP PROCEDURE if exists addPolicyBase;
CREATE PROCEDURE addPolicyBase (
	in p_name varchar(60), in p_des varchar(60),
    in p_psn varchar(20), in p_fid int,
    in p_domainid int
)
begin         
     declare v_subsn int;  	                
     declare v_sn varchar(20);               
     declare v_psn varchar(20);     
     declare v_pediturl varchar(150);
	
	 #得到并更新下一个子策略sn	
     select cast(configvalue as UNSIGNED)+1 into v_subsn from c_configs where configtype=33 and configkey='policysn';
     update c_configs set configvalue=CONCAT(v_subsn) where configtype=33 and configkey='policysn';          

	 #扩展sn长度
     if v_subsn>=1 and v_subsn<=9 then     
        set v_sn= CONCAT('000',v_subsn);
     end if;
          
     if v_subsn>=10 and v_subsn<=99 then     
        set v_sn= CONCAT('00',v_subsn);
     end if;          

     if v_subsn>=100 and v_subsn<=999 then     
        set v_sn= CONCAT('0',v_subsn);
     end if;     

     if v_subsn>=1000 and v_subsn<=9999 then     
        set v_sn= CONCAT('',v_subsn);        
     end if;

     #查看策略sn
     #select v_sn as subsn;             
     #select sn from p_policy_base where `type`=1 and length(sn)=6;          
          
     #得到父策略sn(len:6),v_psn主要用来判断策略分类是否存在
     select sn,editurl into v_psn,v_pediturl from p_policy_base where length(sn)=6 and sn=p_psn;
     
     #真正的策略sn                   
     set v_sn = CONCAT(p_psn,'-', v_sn);
          
     #插入子策略     
     if v_psn is not null then
        insert into p_policy_base (sn,`name`,des,fid,domainid,createTime,state,editurl) 
               values(v_sn,p_name,p_des,p_fid,p_domainid,now(),0, CONCAT(v_pediturl,p_fid));
     end if;
     
     #如果没有相应的策略分类(主要用于查错)
     if v_psn is null then 
     	#插入相应的策略分类     
        insert into p_policy_base (sn,`name`,fid,createTime,state) 
               values(p_psn,p_psn,0,now(),0);
        #再插入相应的子策略     
        insert into p_policy_base (sn,`name`,des,fid,domainid,createTime,state,editurl) 
               values(p_psn,p_name,p_des,p_fid,p_domainid,now(),0, CONCAT(p_fid));
     end if;
end;

##### 更新p_policy_base信息存储过程
DROP PROCEDURE if exists updatePolicyBase;
CREATE PROCEDURE updatePolicyBase (
    in p_name varchar(60), in p_des varchar(60),
    in p_psn varchar(20), in p_fid int,
    in p_domainid int
)
begin 
     update p_policy_base set `name`=p_name,des=p_des,domainid=p_domainid 
            where length(sn)>6 and sn like CONCAT(p_psn,'%') and fid=p_fid;               
     #测试
     #insert into test (`name`, des, time) values ('update',CONCAT(p_psn,':',p_fid), now());
end;

##### p_timepolicy的insert触发器
DROP TRIGGER IF EXISTS `p_timepolicy_trigger_insert`;
CREATE  TRIGGER `p_timepolicy_trigger_insert` AFTER INSERT ON `p_timepolicy` FOR EACH ROW 
BEGIN     
     declare p_name varchar(32);       
     declare p_des varchar(128);     
     declare p_fid int;            
     declare p_domainid int;            
	
     select timepolicyid,`name`,description,domainid into p_fid,p_name,p_des,p_domainid 
     from p_timepolicy order by timepolicyid desc limit 0,1;     
     call addPolicyBase(p_name,p_des,'040002', p_fid, p_domainid);
	 
	 update c_frontconfig set valuestr=CONCAT(UNIX_TIMESTAMP()) where name='p_timepolicy';
END;

##### p_timepolicy更新操作触发器
DROP TRIGGER `p_timepolicy_trigger_update`;
create Trigger p_timepolicy_trigger_update  after  update on  p_timepolicy for each ROW
BEGIN
     call updatePolicyBase(NEW.`name`, NEW.description, '040002', OLD.timepolicyid, NEW.domainid);

     update c_frontconfig set valuestr=CONCAT(UNIX_TIMESTAMP()) where name='p_timepolicy';
end;

#### 删除策略存储过程
#### 调用此方法，将会删除p_policy_base及具体策略信息表
DROP PROCEDURE if exists deletePolicy;
CREATE PROCEDURE deletePolicy (
       in p_policyid int       
)
begin    
     declare v_sn6 varchar(20);          
     declare v_fid int;
     #in p_sn varchar(20), in p_fid int     

     select LEFT(sn,6),fid into v_sn6,v_fid from p_policy_base where id=p_policyid;  
     delete from  p_policy_base  where id=p_policyid and state<>-1; 
        
     if v_sn6='040001' then
        delete from p_ippolicy where ippolicyid=v_fid;
     elseif v_sn6='040002' then               
        delete from p_timepolicy where timepolicyid=v_fid;            
     elseif v_sn6='040021' then               
        delete from p_orderset where setid=v_fid;                 
     elseif v_sn6='040041' then          
        delete from p_orderset where setid=v_fid; 
     elseif v_sn6='040061' then     
        delete from p_orderset where setid=v_fid; 
     #else     
     #   delete from p_orderset WHERE 1<>1;
     end if; 
end;

#### 调用删除策略,参数p_policy_base.id
call deletePolicy(57);