写该触发器的目的是,求一小时内插入DBZ1101_H表的所有数据的平均值,最后只保留一条数据,而且是每天的整点时间的.如:今天八点到九点钟的数据,应该保存为,今天八点钟到九点钟的所有数据的平均值.其中CJSJ是采集时间,这个字段的数据应为:2008-02-29 09:00:00.
我写了下面的脚本,可是总是掉示我无效的表.列,表.列 或列规格.
CREATE OR REPLACE TRIGGER DBZ1101_H_TRIG
BEFORE INSERT
ON DBZ1101_H
FOR EACH ROW
BEGIN
IF To_Char(:NEW.CJSJ,'HH24:MM:SS') >'00:00:00' AND To_Char(:NEW.CJSJ,'HH24:MM:SS') <= '23:00:00' THEN
UPDATE DBZ1101_H SET A.CJSJ = To_Date(To_Char(SYSDATE,'YYYY-MM-DD')||'01:00:00','YYYY-MM-DD HH24:MI:SS'),
SET YL = (:OLD.YL + :NEW.YL) / 2,SET YC = (:OLD.YC + :NEW.YC) / 2,
SET GD = (:OLD.GD + :NEW.GD) / 2, SET ZLLL = (:OLD.ZLLL + :NEW.ZLLL) / 2,
SET RL = (:OLD.RL + :NEW.RL) / 2, SET WD = (:OLD.WD + :NEW.WD) / 2, SET LJLL = :NEW.LJLL
WHERE To_Char(CJSJ,'HH24:MM:SS') > '00:00:00' AND To_Char(:NEW.CJSJ,'HH24:MM:SS') <= '01:00:00';
END IF;
END;
我写了下面的脚本,可是总是掉示我无效的表.列,表.列 或列规格.
CREATE OR REPLACE TRIGGER DBZ1101_H_TRIG
BEFORE INSERT
ON DBZ1101_H
FOR EACH ROW
BEGIN
IF To_Char(:NEW.CJSJ,'HH24:MM:SS') >'00:00:00' AND To_Char(:NEW.CJSJ,'HH24:MM:SS') <= '23:00:00' THEN
UPDATE DBZ1101_H SET A.CJSJ = To_Date(To_Char(SYSDATE,'YYYY-MM-DD')||'01:00:00','YYYY-MM-DD HH24:MI:SS'),
SET YL = (:OLD.YL + :NEW.YL) / 2,SET YC = (:OLD.YC + :NEW.YC) / 2,
SET GD = (:OLD.GD + :NEW.GD) / 2, SET ZLLL = (:OLD.ZLLL + :NEW.ZLLL) / 2,
SET RL = (:OLD.RL + :NEW.RL) / 2, SET WD = (:OLD.WD + :NEW.WD) / 2, SET LJLL = :NEW.LJLL
WHERE To_Char(CJSJ,'HH24:MM:SS') > '00:00:00' AND To_Char(:NEW.CJSJ,'HH24:MM:SS') <= '01:00:00';
END IF;
END;
BEFORE INSERT
ON dbz1101_h
FOR EACH ROW
BEGIN
IF TO_CHAR (:NEW.cjsj, 'HH24:MM:SS') > '00:00:00'
AND TO_CHAR (:NEW.cjsj, 'HH24:MM:SS') <= '23:00:00'
THEN
UPDATE dbz1101_h
SET a.cjsj =
TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD') || '01:00:00',
'YYYY-MM-DD HH24:MI:SS'
),
yl = (:OLD.yl + :NEW.yl) / 2,
yc = (:OLD.yc + :NEW.yc) / 2,
gd = (:OLD.gd + :NEW.gd) / 2,
zlll = (:OLD.zlll + :NEW.zlll) / 2,
rl = (:OLD.rl + :NEW.rl) / 2,
wd = (:OLD.wd + :NEW.wd) / 2,
ljll = :NEW.ljll
WHERE TO_CHAR (cjsj, 'HH24:MM:SS') > '00:00:00'
AND TO_CHAR (:NEW.cjsj, 'HH24:MM:SS') <= '01:00:00';
END IF;
END;