在触发器中需要传递多个参数,我开始这样写:DECLARE total int;
DECLARE tp int;
DECLARE nian int;
DECLARE i int;
DECLARE ff int;
DECLARE seasion int;
DECLARE half int;
DECLARE sfc timestamp;
DECLARE efc timestamp;
DECLARE dend timestamp;
SELECT period_diff(DATE_FORMAT(contectend,'%Y%m'),DATE_FORMAT(jfstdate,'%Y%m')) into total ,pricetype into tp ,fftype into ff,jfstdate into sfcfrom jz_contect where ctid = new.ctid;
不行,说不认识pricetype,后来又改为这样:DECLARE efc timestamp;
DECLARE dend timestamp;
SELECT period_diff(DATE_FORMAT(contectend,'%Y%m'),DATE_FORMAT(jfstdate,'%Y%m')) into total from jz_contect where ctid = new.ctid;
SELECT pricetype into tp from jz_contect where ctid = new.ctid;
SELECT fftype into ff from jz_contect where ctid = new.ctid;
SELECT jfstdate into sfc from jz_contect where ctid = new.ctid;结果在调用的时候出现了锁定现象,说是用的innodb型,造成链接超时,我怎样把查询出的多个数据赋值给变量呢?
DECLARE tp int;
DECLARE nian int;
DECLARE i int;
DECLARE ff int;
DECLARE seasion int;
DECLARE half int;
DECLARE sfc timestamp;
DECLARE efc timestamp;
DECLARE dend timestamp;
SELECT period_diff(DATE_FORMAT(contectend,'%Y%m'),DATE_FORMAT(jfstdate,'%Y%m')) into total ,pricetype into tp ,fftype into ff,jfstdate into sfcfrom jz_contect where ctid = new.ctid;
不行,说不认识pricetype,后来又改为这样:DECLARE efc timestamp;
DECLARE dend timestamp;
SELECT period_diff(DATE_FORMAT(contectend,'%Y%m'),DATE_FORMAT(jfstdate,'%Y%m')) into total from jz_contect where ctid = new.ctid;
SELECT pricetype into tp from jz_contect where ctid = new.ctid;
SELECT fftype into ff from jz_contect where ctid = new.ctid;
SELECT jfstdate into sfc from jz_contect where ctid = new.ctid;结果在调用的时候出现了锁定现象,说是用的innodb型,造成链接超时,我怎样把查询出的多个数据赋值给变量呢?
select ff,gg into @dd,@dd1 from aa where ff=1;
有多条,用游标
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `trigger_jz_guestcard` AFTER INSERT ON `jz_contect`
每次新生成了数据:SELECT period_diff(DATE_FORMAT(contectend,'%Y%m'),DATE_FORMAT(jfstdate,'%Y%m')) into total from jz_contect where ctid = new.ctid;然后插入另一个表中,当然需要根据取出来的内容判断一下才插入。
,pricetype,fftype,jfstdate
into total , tp , ff, `sfcfrom jz_contect` where ctid = new.ctid;
SELECT period_diff(DATE_FORMAT(contectend,'%Y%m'),DATE_FORMAT(jfstdate,'%Y%m')) into total ,pricetype into tp ,fftype into ff,jfstdate into sfcfrom jz_contect where ctid = new.ctid;
这个问题,上述代码应该可以了,其它的明日我看看,现在有事