mysql 触发器添加多条记录

legend_jhz 2009-01-09 05:01:55

有两张表，分别是合同表和客户业务表，合同表如下：



CREATE TABLE `jz_contect` (

  `ctid` int(10) unsigned NOT NULL auto_increment,

  `gbid` int(10) unsigned default NULL,

  `tcid` int(10) unsigned default NULL,

  `price` float default NULL,

  `pricetype` int(11) default NULL,

  `jfstdate` timestamp NULL default '0000-00-00 00:00:00',

  `fftype` int(11) default NULL,

  `contectstart` timestamp NULL default '0000-00-00 00:00:00',

  `contectend` timestamp NULL default '0000-00-00 00:00:00',

客户业务表如下：



CREATE TABLE `jz_gbmes` (

  `g_id` int(10) unsigned NOT NULL auto_increment,

  `remindtype` int(11) default NULL,

  `ctid` int(10) unsigned default NULL,

  `bstdate` timestamp NULL default CURRENT_TIMESTAMP,

  `sdate` timestamp NULL default NULL,

  `edate` timestamp NULL default NULL,

  `bs_id` int(10) unsigned default NULL,

  PRIMARY KEY  (`g_id`),

  KEY `FK2_jz_gbmes` (`ctid`),

  CONSTRAINT `FK2_jz_gbmes` FOREIGN KEY (`ctid`) REFERENCES `jz_contect` (`ctid`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8;

工作流程为：先签完合同，然后再设置客户业务，根据合同的终止日期来生成客户业务的记录，一个合同可以有多个业务，现在当合同修改后，更新以前的客户业务记录，因为有多条记录，怎么循环产生呢？

...全文

611 44 打赏收藏转发到动态举报

写回复

用AI写文章

44 条回复

切换为时间正序

请发表友善的回复…

发表回复

legend_jhz 2009-01-13

打赏
举报

搞定了，谢谢！

legend_jhz 2009-01-13

打赏
举报

这样其实得先判断出这个jz_contect表中ctid所对应的n个g_id有没有完成的记录，只要一个g_id对应的有业务完成记录，就不能进行生成数据，如果没有才能进行插入操作。这样直接拿这个g_id的话就不准确了。

wwwwb 2009-01-13

打赏
举报

jz_gbmes表的g_id不是？

legend_jhz 2009-01-13

打赏
举报



DECLARE gbmes_csr CURSOR FOR SELECT g_id FROM jz_gbmes where ctid = new.ctid;

    DECLARE gb_csr CURSOR FOR SELECT a.remindtype,a.sdate,a.edate,a.bstdate,a.g_id,b.contectend,a.org_id from jz_gbmes a where a.ctid = new.ctid;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_gb=1;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_gbmes=1;

OPEN gbmes_csr;  

    REPEAT  

    FETCH gbmes_csr INTO gg; 

    SELECT count(*) into tt from jz_stream where g_id = gg and results = 1;

    if(tt > 0) then set flag = true;

    end if;

    UNTIL no_more_gbmes  END REPEAT; 

    CLOSE gbmes_csr; 





OPEN gb_csr;  

    REPEAT  

    FETCH gb_csr INTO tps,stcaution,endcaution,caution,gid,cend,orgid;

这两个都不一样，怎么加游标的id呢？

wwwwb 2009-01-13

打赏
举报

用一个handler就行了

legend_jhz 2009-01-13

打赏
举报

既然有游标的话，那不还得有handler控制么？

wwwwb 2009-01-13

打赏
举报

在SELECT INTO 中加入条件
select into .... where id=你的游标中相关ID

legend_jhz 2009-01-13

打赏
举报

加入条件不行？WHERE ID=游标ID
这句是什么意思？不是很明白。

wwwwb 2009-01-13

打赏
举报

select into只能存入一条记录的值，加入条件不行？WHERE ID=游标ID

legend_jhz 2009-01-13

打赏
举报

哪么存在多条记录值判断存放的时候select into可以吗？

wwwwb 2009-01-13

打赏
举报

在同一块中不能有两个HANDLER，调整一下思路吧

legend_jhz 2009-01-13

打赏
举报

不能用两个handler?

legend_jhz 2009-01-13

打赏
举报

哈哈，我说的不是很明白，多包涵，我现在用游标进行处理结果出现如下错误：



Error Code : 1413

Duplicate handler declared in the same block

可是我需要两个游标，不能只有一个handler啊，我的代码如下：



DECLARE flag boolean default false; 

    DECLARE gbmes_csr CURSOR FOR SELECT g_id FROM jz_gbmes where ctid = new.ctid;

    DECLARE gb_csr CURSOR FOR SELECT a.remindtype,a.sdate,a.edate,a.bstdate,a.g_id,b.contectend,a.org_id from jz_gbmes a where a.ctid = new.ctid;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_gb=1;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_gbmes=1; 

    select count(*) into s from jz_guestcard where ctid = new.ctid and jfstate = 1; 

    OPEN gbmes_csr;  

    REPEAT  

    FETCH gbmes_csr INTO gg; 

    SELECT count(*) into tt from jz_stream where g_id = gg and results = 1;

    if(tt > 0) then set flag = true;

    end if;

    UNTIL no_more_gbmes  END REPEAT; 

    CLOSE gbmes_csr; 

    if(s = 0 || !flag) then

    delete from jz_guestcard where ctid = new.ctid;

    OPEN gb_csr;  

    REPEAT  

    FETCH gb_csr INTO tps,stcaution,endcaution,caution,gid,cend,orgid;

       if(tps = 0) then

	   set ye = Year(caution);

           set yue = month(endcaution);

	   set dd = dayofmonth(endcaution);

           set efcd = STR_TO_DATE(concat(ye,'/',yue,'/',dd),'%Y/%m/%d');

           set sfcd = STR_TO_DATE(concat(ye,'/',month(stcaution),'/',dayofmonth(stcaution)),'%Y/%m/%d');

	   if(caution > efcd) then

		set sfcd = DATE_ADD(sfcd,interval 1 year);

		set efcd = DATE_ADD(efcd,interval 1 year);

	   end if;

	   while(sfcd < cend) do

		insert into jz_stream(g_id,results,sdate,edate,org_id) values(gid,0,sfcd,efcd,orgid);

		set sfcd = DATE_ADD(sfcd,interval 1 year);

		set efcd = DATE_ADD(efcd,interval 1 year);

           end while;

       elseif(tps = 1) then

           set ye = Year(caution);

           set yue = month(endcaution);

	   set dd = dayofmonth(endcaution);

           set efcd = STR_TO_DATE(concat(ye,'/',yue,'/',dd),'%Y/%m/%d');

           set sfcd = STR_TO_DATE(concat(ye,'/',month(stcaution),'/',dayofmonth(stcaution)),'%Y/%m/%d');

	   while(caution > efcd) do

		set sfcd = DATE_ADD(sfcd,interval 3 month);

		set efcd = DATE_ADD(efcd,interval 3 month);

	   end while;

	   while(sfcd < cend) do

		insert into jz_stream(g_id,results,sdate,edate,org_id) values(gid,0,sfcd,efcd,orgid);

		set sfcd = DATE_ADD(sfcd,interval 3 month);

		set efcd = DATE_ADD(efcd,interval 3 month);

           end while;

         end if;

    UNTIL no_more_gb  END REPEAT; 

    CLOSE gb_csr;