DELIMITER $$ DROP TRIGGER IF EXISTS tri_insertBaseTime$$ CREATE TRIGGER tri_insertBaseTime AFTER INSERT ON t_base_time FOR EACH ROW BEGIN DECLARE num INT DEFAULT 0; DECLARE rec_num INT DEFAULT 0; DECLARE lastTime DATE DEFAULT '0000-00-00'; DECLARE nextTime DATE DEFAULT '0000-00-00'; IF (new.type IN (0,1,2,3)) THEN SELECT COUNT(*) INTO rec_num FROM t_overdue WHERE lift_id=new.lift_id AND TYPE=new.type; IF (rec_num!=0) THEN WHILE i <rec_num DO SELECT last_time INTO lastTime FROM t_overdue WHERE lift_id=new.lift_id AND TYPE=new.type LIMIT num,1; SELECT next_time INTO nextTime FROM t_overdue WHERE lift_id=new.lift_id AND TYPE=new.type LIMIT num,1; IF (DATEDIFF(lastTime,new.last_time)!=0 || DATEDIFF(nextTime,new.next_time)!=0) THEN INSERT INTO t_overdue (lift_id,TYPE,last_time,next_time,up_time,is_sms) VALUES (new.lift_id,new.type,new.last_time,new.next_time,NOW(),new.is_sms); END IF; SET rec_num=rec_num+1; END WHILE; END IF; ELSE INSERT INTO t_overdue(lift_id,TYPE,last_time,next_time,up_time,is_sms) VALUES (new.lift_id,new.type,new.last_time,new.next_time,NOW(),new.is_sms); END IF;
END;$$ DELIMITER ;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'num,1; SELECT next_time INTO nextTime FROM t_overdue WHERE lift_id=new.lift' at line 12还是报错啊~
主要就是这个num变量的值获取不到,改成固定的,比如0,就可以执行通过,求解
set @asql=concat('SELECT last_time INTO lastTime FROM t_overdue WHERE lift_id=new.lift_id AND TYPE=new.type LIMIT ',num,',1'); prepare stml from @asql; execute stml;其它的自行修改
[Err] 1336 - Dynamic SQL is not allowed in stored function or trigger无语,改了以后出现这样的错误
在TRIGGER中无法使用动态语句,MYSQL限制
我用的是这个 WHILE i <rec_num DO SELECT last_time INTO lastTime FROM t_overdue WHERE lift_id=new.lift_id AND TYPE=new.type LIMIT num,1; SELECT next_time INTO nextTime FROM t_overdue WHERE lift_id=new.lift_id AND TYPE=new.type LIMIT num,1; IF (DATEDIFF(lastTime,new.last_time)!=0 || DATEDIFF(nextTime,new.next_time)!=0) THEN INSERT INTO t_overdue (lift_id,TYPE,last_time,next_time,up_time,is_sms) VALUES (new.lift_id,new.type,new.last_time,new.next_time,NOW(),new.is_sms); END IF; SET rec_num=rec_num+1; END WHILE; 有什么可以获取3条记录中的每条记录的last_time和next_time
DROP TRIGGER IF EXISTS tri_insertBaseTime$$
CREATE TRIGGER tri_insertBaseTime AFTER INSERT ON t_base_time
FOR EACH ROW
BEGIN
DECLARE num INT DEFAULT 0;
DECLARE rec_num INT DEFAULT 0;
DECLARE lastTime DATE DEFAULT '0000-00-00';
DECLARE nextTime DATE DEFAULT '0000-00-00';
IF (new.type IN (0,1,2,3)) THEN
SELECT COUNT(*) INTO rec_num FROM t_overdue WHERE lift_id=new.lift_id AND TYPE=new.type;
IF (rec_num!=0) THEN
WHILE i <rec_num DO
SELECT last_time INTO lastTime FROM t_overdue WHERE lift_id=new.lift_id AND TYPE=new.type LIMIT num,1;
SELECT next_time INTO nextTime FROM t_overdue WHERE lift_id=new.lift_id AND TYPE=new.type LIMIT num,1;
IF (DATEDIFF(lastTime,new.last_time)!=0 || DATEDIFF(nextTime,new.next_time)!=0) THEN
INSERT INTO t_overdue (lift_id,TYPE,last_time,next_time,up_time,is_sms) VALUES (new.lift_id,new.type,new.last_time,new.next_time,NOW(),new.is_sms);
END IF;
SET rec_num=rec_num+1;
END WHILE;
END IF;
ELSE
INSERT INTO t_overdue(lift_id,TYPE,last_time,next_time,up_time,is_sms) VALUES (new.lift_id,new.type,new.last_time,new.next_time,NOW(),new.is_sms);
END IF;
END;$$
DELIMITER ;
SELECT next_time INTO nextTime FROM t_overdue WHERE lift_id=new.lift' at line 12还是报错啊~
prepare stml from @asql;
execute stml;其它的自行修改
WHILE i <rec_num DO
SELECT last_time INTO lastTime FROM t_overdue WHERE lift_id=new.lift_id AND TYPE=new.type LIMIT num,1;
SELECT next_time INTO nextTime FROM t_overdue WHERE lift_id=new.lift_id AND TYPE=new.type LIMIT num,1;
IF (DATEDIFF(lastTime,new.last_time)!=0 || DATEDIFF(nextTime,new.next_time)!=0) THEN
INSERT INTO t_overdue (lift_id,TYPE,last_time,next_time,up_time,is_sms) VALUES (new.lift_id,new.type,new.last_time,new.next_time,NOW(),new.is_sms);
END IF;
SET rec_num=rec_num+1;
END WHILE; 有什么可以获取3条记录中的每条记录的last_time和next_time
打开游标循环取值