CREATE OR REPLACE TRIGGER "TIG_CYSCYXJL1_INSERT_SUM" AFTER
INSERT ON "WSCL_CYSCYXJL1" FOR EACH ROW
declare
T1LLLTS1 NUMBER:=0;
S5LLLT1 NUMBER:=0;
SUMT1LLLTS1 NUMBER:=0;
SUMS5LLLT1 NUMBER:=0;
intSum integer:=0;
BEGIN
if :NEW.FSJ='6:00' then
select count(S5LLLTS) into intSum from WSCL_cyscyxjl1 where FSJ='6:00';
if intSum>0 then
SELECT NVL(S5LLLTS,0) INTO S5LLLT1 FROM WSCL_cyscyxjl1 WHERE FRQ=:NEW.FRQ-1 AND FSJ='6:00';
SELECT NVL(T1LLLTS,0) INTO T1LLLTS1 FROM WSCL_cyscyxjl1 WHERE FRQ=:NEW.FRQ-1 AND FSJ='6:00';
end if;
SUMT1LLLTS1:=:new.T1LLLTS+T1LLLTS1;
SUMS5LLLT1:=:new.S5LLLTS+S5LLLT1;
INSERT INTO WSCL_cyscyxjl1(FRQ,FSJ,S5LLLTS,T1LLLTS) values(:new.frq,'DAYSUM',SUMS5LLLT1,SUMT1LLLTS1);
end if;END;
请问我这个TRIGGER那里错了,编译没错,但在执行的时候提示select count(S5LLLTS) into intSum from WSCL_cyscyxjl1 where FSJ='6:00';出错。
select count(S5LLLTS) from WSCL_cyscyxjl1 where FSJ='6:00' 查询没问题。其功能 :查询前一天6点数据,于今天刚插入的6点数据 求和后,插入一条 合计记录
INSERT ON "WSCL_CYSCYXJL1" FOR EACH ROW
declare
T1LLLTS1 NUMBER:=0;
S5LLLT1 NUMBER:=0;
SUMT1LLLTS1 NUMBER:=0;
SUMS5LLLT1 NUMBER:=0;
intSum integer:=0;
BEGIN
if :NEW.FSJ='6:00' then
select count(S5LLLTS) into intSum from WSCL_cyscyxjl1 where FSJ='6:00';
if intSum>0 then
SELECT NVL(S5LLLTS,0) INTO S5LLLT1 FROM WSCL_cyscyxjl1 WHERE FRQ=:NEW.FRQ-1 AND FSJ='6:00';
SELECT NVL(T1LLLTS,0) INTO T1LLLTS1 FROM WSCL_cyscyxjl1 WHERE FRQ=:NEW.FRQ-1 AND FSJ='6:00';
end if;
SUMT1LLLTS1:=:new.T1LLLTS+T1LLLTS1;
SUMS5LLLT1:=:new.S5LLLTS+S5LLLT1;
INSERT INTO WSCL_cyscyxjl1(FRQ,FSJ,S5LLLTS,T1LLLTS) values(:new.frq,'DAYSUM',SUMS5LLLT1,SUMT1LLLTS1);
end if;END;
请问我这个TRIGGER那里错了,编译没错,但在执行的时候提示select count(S5LLLTS) into intSum from WSCL_cyscyxjl1 where FSJ='6:00';出错。
select count(S5LLLTS) from WSCL_cyscyxjl1 where FSJ='6:00' 查询没问题。其功能 :查询前一天6点数据,于今天刚插入的6点数据 求和后,插入一条 合计记录
SELECT NVL(T1LLLTS,0) FROM WSCL_cyscyxjl1 WHERE FRQ=:NEW.FRQ-1 AND FSJ='6:00';
都只返回一条记录吗
插入:NEW.FSJ='6:00' ,intsum至少为1SELECT NVL(S5LLLTS,0) INTO S5LLLT1 FROM WSCL_cyscyxjl1 WHERE FRQ=:NEW.FRQ-1 AND FSJ='6:00';
SELECT NVL(T1LLLTS,0) INTO T1LLLTS1 FROM WSCL_cyscyxjl1 WHERE FRQ=:NEW.FRQ-1 AND FSJ='6:00';可以合并成
SELECT NVL(S5LLLTS,0),NVL(T1LLLTS,0) INTO S5LLLT1,T1LLLTS1 FROM WSCL_cyscyxjl1 WHERE FRQ=:NEW.FRQ-1 AND FSJ='6:00';
但是可能不会有返回值,这样的话会出错
改成下面的试试
SELECT NVL(S5LLLTS,0),NVL(T1LLLTS,0) INTO S5LLLT1,T1LLLTS1 FROM (
select S5LLLTS,T1LLLTS from WSCL_cyscyxjl1 WHERE FRQ=:NEW.FRQ-1 AND FSJ='6:00'
union all select null,null from dual order by 1)where rownum=1;