我想当系统时间大于表内字段时间时,将字段HPI_STATE置为2,不知道这样写对不对,一只编辑不过去
CREATE OR REPLACE TRIGGER QXM.BIDU_HPI_STATE
BEFORE INSERT OR DELETE OR UPDATE
ON QXM.HRM_PACT_INFO
REFERENCING OLD AS OLD NEW AS NEW_VALUE
FOR EACH ROW
BEGIN
update HRM_PACT_INFO set :NEW_VALUE.HPI_STATE:=2where to_char(sysdate,'YYYY-MM-DD') >= to_char(:OLD.HPI_END_DATE,'YYYY-MM-DD');END;
/
CREATE OR REPLACE TRIGGER QXM.BIDU_HPI_STATE
BEFORE INSERT OR DELETE OR UPDATE
ON QXM.HRM_PACT_INFO
REFERENCING OLD AS OLD NEW AS NEW_VALUE
FOR EACH ROW
BEGIN
update HRM_PACT_INFO set :NEW_VALUE.HPI_STATE:=2where to_char(sysdate,'YYYY-MM-DD') >= to_char(:OLD.HPI_END_DATE,'YYYY-MM-DD');END;
/
这个写法错误的。
而且你比较日期,怎么又去to_char?只能写:
if sysdate>=:OLD.HPI_END_DATE
then
:NEW_VALUE.HPI_STATE:=2;
end if;还有当你进行insert时,:old是不存在的。