CREATE OR REPLACE TRIGGER SETOLMESULT BEFORE
INSERT ON "PREDATA_PREOLMRESULT"
FOR EACH ROW
DECLARE
MAXID PREDATA_PREOLMRESULT.RESULTID%TYPE;
MAXENDTIME PREDATA_PREOLMRESULT.ENDTIME%TYPE;
MAXSTARTTIME PREDATA_PREOLMRESULT.STARTTIME%TYPE;
BEGIN
SELECT T.RESULTID,T.STARTTIME ,T.ENDTIME INTO MAXID,MAXSTARTTIME,MAXENDTIME FROM PREDATA_PREOLMRESULT T
WHERE T.CODE =:NEW.CODE AND :NEW.STARTTIME BETWEEN T.STARTTIME AND T.ENDTIME AND T.TAGNAME=:NEW.TAGNAME AND T.OLMCODETYPE =:NEW.OLMCODETYPE;
IF( MAXID IS NOT NULL)
THEN
:NEW.STARTTIME :=MAXSTARTTIME;
IF (to_char(:NEW.ENDTIME , 'yyyy-mm-dd hh24:mi:ss') < to_char(MAXENDTIME, 'yyyy-mm-dd hh24:mi:ss') ) THEN
:NEW.ENDTIME :=MAXENDTIME;
END IF;
DELETE FROM PREDATA_PREOLMRESULT WHERE RESULTID =MAXID;
END IF;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('NO DATA');
END;
ORACLE TRIGGER
最新推荐文章于 2020-11-08 17:43:20 发布