CREATE OR REPLACE TRIGGER "RQC_RECORD_TRI_INS"
BEFORE insert ON RQC_RECORD
FOR EACH ROW
DECLARE
NEW_TIME VARCHAR2(8);
START_TIME VARCHAR2(8);
END_TIME VARCHAR2(8);
INSERT_EXCE exception;
BEGIN
NEW_TIME := to_char(sysdate,'hh24:mi');
select starttime into START_TIME from rqc_worktime;
select endtime into END_TIME from rqc_worktime;
-- dbms_output.put_line(:new.recordtypeno);
-- 得到要插入行的recordtypeno是否为开关机,并且判断当前时间是否为营业时间,如果条件都成立则不让插入数据,抛出异常
if ((:new.recordtypeno = '010' or :new.recordtypeno = '011') and (NEW_TIME >= START_TIME and NEW_TIME <= END_TIME))
then raise INSERT_EXCE;
end if;
-- exception when INSERT_EXCE then dbms_output.put_line('在营业时间内进行正常开关机');
exception when INSERT_EXCE then raise_application_error('-20002', '在营业时间内进行正常开关机');
BEFORE insert ON RQC_RECORD
FOR EACH ROW
DECLARE
NEW_TIME VARCHAR2(8);
START_TIME VARCHAR2(8);
END_TIME VARCHAR2(8);
INSERT_EXCE exception;
BEGIN
NEW_TIME := to_char(sysdate,'hh24:mi');
select starttime into START_TIME from rqc_worktime;
select endtime into END_TIME from rqc_worktime;
-- dbms_output.put_line(:new.recordtypeno);
-- 得到要插入行的recordtypeno是否为开关机,并且判断当前时间是否为营业时间,如果条件都成立则不让插入数据,抛出异常
if ((:new.recordtypeno = '010' or :new.recordtypeno = '011') and (NEW_TIME >= START_TIME and NEW_TIME <= END_TIME))
then raise INSERT_EXCE;
end if;
-- exception when INSERT_EXCE then dbms_output.put_line('在营业时间内进行正常开关机');
exception when INSERT_EXCE then raise_application_error('-20002', '在营业时间内进行正常开关机');
END;
注::new可获取插入的字段值。
raise_application_error用来抛异常,阻止插入。
oracle中时间可以比较大小。
to_char(sysdate,'hh24:mi');可以获取当前时间,后面的为格式。