mysql 触发器添加多条记录

Question

有两张表，分别是合同表和客户业务表，合同表如下：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;工作流程为：先签完合同，然后再设置客户业务，根据合同的终止日期来生成客户业务的记录，一个合同可以有多个业务，现在当合同修改后，更新以前的客户业务记录，因为有多条记录，怎么循环产生呢？

Answer 1

每个业务的remindtype可能会不同的，需要先根据remindtype的值再设置业务表的sdate和edate.

Answer 2

客户业务表的数据如下：+------+------------+------------+-------+------+---------------------+---------
------------+---------------------+-------+--------+
| g_id | remindtype | account_id | gt_id | ctid | bstdate             | sdate
            | edate               | bs_id | org_id |
+------+------------+------------+-------+------+---------------------+---------
------------+---------------------+-------+--------+
|   15 |          0 |         28 |  NULL |    9 | 2008-01-01 00:00:00 | 2008-01-
01 00:00:00 | 2008-03-01 00:00:00 |    10 |      9 |
|   16 |          2 |         28 |     4 |    9 | 2008-01-01 00:00:00 | 2008-01-
01 00:00:00 | 2008-01-15 00:00:00 |    16 |      9 |
|   17 |          0 |         28 |  NULL |   11 | 2008-01-01 00:00:00 | 2008-01-
01 00:00:00 | 2008-02-22 00:00:00 |    17 |      9 |
|   20 |          0 |         28 |  NULL |   12 | 2008-01-01 00:00:00 | 2008-01-
01 00:00:00 | 2008-04-01 00:00:00 |    19 |      9 |
+------+------------+------------+-------+------+---------------------+---------
------------+---------------------+-------+--------+
4 rows in set (0.07 sec)mysql>

Answer 3

+------+------------+------------+-------+------+---------------------+---------
------------+---------------------+-------+--------+
| g_id | remindtype | account_id | gt_id | ctid | bstdate             | sdate
            | edate               | bs_id | org_id |
+------+------------+------------+-------+------+---------------------+---------
------------+---------------------+-------+--------+
|   15 |          0 |         28 |  NULL |    9 | 2008-01-01 00:00:00 | 2008-01-
01 00:00:00 | 2008-03-01 00:00:00 |    10 |      9 |
|   16 |          2 |         28 |     4 |    9 | 2008-01-01 00:00:00 | 2008-01-
01 00:00:00 | 2008-01-15 00:00:00 |    16 |      9 |
|   17 |          0 |         28 |  NULL |   11 | 2008-01-01 00:00:00 | 2008-01-
01 00:00:00 | 2008-02-22 00:00:00 |    17 |      9 |
|   20 |          0 |         28 |  NULL |   12 | 2008-01-01 00:00:00 | 2008-01-
01 00:00:00 | 2008-04-01 00:00:00 |    19 |      9 |这是客户业务表的数据，当签订合同时，会根据合同的截止日期和业务的提醒种类生成如上的数据，这是我写的生成的触发器：SELECT a.remindtype,a.sdate,a.edate,a.bstdate,a.g_id,b.contectend,a.org_id into tps,stcaution,endcaution,caution,gid,cend,orgid from jz_gbmes a left JOIN jz_contect b
       on a.ctid = b.ctid where a.g_id = new.g_id;
       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;现在是当合同表jz_contect的截止日期改变时，需要重新生成jz_gbmes表的数据，但是一个合同id可能对应多个jz_gbmes的id,我不知道当有多个jz_gbmes的id的时候，怎样控制循环生成信息。

Answer 4

你总要贴jz_gbmes表的内容吧，修改前、后是什么？

Answer 5

操作先设置contect.jsp(这个是设置合同的页面)，然后是指gbmes.jsp（这个是设置客户业务的页面），上一个表就是jz_gbmes表的内容，是这样的：如签合同开始日期：2008-1-1截止日期是：2009-1-1，并且业务的提醒类型为年，那么jz_gbmes表的数据为：+------+------------+------------+-------+------+---------------------+---------
------------+---------------------+-------+--------+
| g_id | remindtype | account_id | gt_id | ctid | bstdate             | sdate
            | edate               | bs_id | org_id |
+------+------------+------------+-------+------+---------------------+---------
------------+---------------------+-------+--------+
|   15 |          3 |         28 |  NULL |    9 | 2008-01-01 00:00:00 | 2008-01-
01 00:00:00 | 2008-03-01 00:00:00 |    10 |      9 |如果合同截止日期改为：2010-1-1哪么jz_gbmes表的数据为生成两个：
：+------+------------+------------+-------+------+---------------------+---------
------------+---------------------+-------+--------+
| g_id | remindtype | account_id | gt_id | ctid | bstdate             | sdate
            | edate               | bs_id | org_id |
+------+------------+------------+-------+------+---------------------+---------
------------+---------------------+-------+--------+
|   15 |          3 |         28 |  NULL |    9 | 2008-01-01 00:00:00 | 2008-01-
01 00:00:00 | 2008-03-01 00:00:00 |    10 |      9 |
|   16 |          3 |         28 |  NULL |    9 | 2008-01-01 00:00:00 | 2009-01-
01 00:00:00 | 2009-03-01 00:00:00 |    10 |      9 |

Answer 6

以年为标准sdate和edate是不同的。

Answer 7

这个都已经实现了，我的问题是：如何在jz_contect表中的一个id对应多个jz_gbmes时，怎么样让它多条产生数据，关键是多条的控制我不知道怎么做，谢谢.

Answer 8

两表通过什么字段连接？用SELECT COUNT(*) 不行？

Answer 9

是通过ctid（合同表的id)关联的，ctid可能对应这多个jz_gbmes的id,但是每一个id对应的提醒方式不同插入记录的算法就不同，我怎么把他们取出来，然后每个判断提醒方式生成呢？

Answer 10

提醒方式：举例说明
通过ctid取记录，提醒方式是怎样生成的？

Answer 11

操作流程是先设合同contect.jsp和客户业务guestbusiness.jsp，在guestbusiness.jsp中提交生成客户业务信息的时候，需要拿到contect.jsp中的合同截止日期，然后根据guestbusiness.jsp中设定的提醒方式，产生记录，一下是我初始生成客户业务记录的触发器，提醒方式就是下面的remindtype：SELECT a.remindtype,a.sdate,a.edate,a.bstdate,a.g_id,b.contectend,a.org_id into tps,stcaution,endcaution,caution,gid,cend,orgid from jz_gbmes a left JOIN jz_contect b
       on a.ctid = b.ctid where a.g_id = new.g_id;
       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;现在是当修改合同表中的截止日期的时候，客户已经根据合同的截止日期生成了客户记录，这样也要修改，那么我怎样有多条的情况下分别取出并根据提醒方式的不同重新产生记录呢？麻烦老大了。

Answer 12

先提取合同号ctid的个数，循环，
执行你上述代码，实际上只多了循环部份

Answer 13

ctid是1个，ctid关联的jz_gbmes的id是多个。select count(*) into n from jz_gbmes where ctid = new.ctid;然后根据n来循环，触发器中有没有类似于list类的东西呢？

Answer 14

这个是一个ctid对应多个jz_gbmes的id。我求出个数来，然后循环插入，这个我怎么取出每一个jz_gbmes的id所对应的remindtype,怎么查啊？

Answer 15

我怎么取出每一个jz_gbmes的id所对应的remindtype,怎么查啊？
用游标 OR SELECT INTO 变量均可，取决于你对哪种方式熟悉 及 数据量大小

Answer 16

大哥，select into能得到多个查询值？

Answer 17

多个查询值？:用游标
如用select into要用循环

Answer 18

哈哈，我说的不是很明白，多包涵，我现在用游标进行处理结果出现如下错误：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; 

Answer 19

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

Answer 20

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

Answer 21

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

Answer 22

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

Answer 23

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

Answer 24

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

Answer 25

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呢？

Answer 26

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