S
sxbug
Unregistered / Unconfirmed
GUEST, unregistred user!
数据库是oracle9,从2个表(jamis.salary,jamis.staff)里面取了部分字段作了一个视图(getcommcost),我想通过视图修改数据,所以我给视图作了一个触发器(TGetCommCost),代码如下:
CREATE OR REPLACE TRIGGER "JAMIS"."TGETCOMMCOST" INSTEAD OF
UPDATE ON "JAMIS"."GETCOMMCOST"
FOR EACH ROW Begin
IF UPDATING('COMUNICATECOST,FAXCOST,PHONEHOMECOST,PHONEHOMECOST1,PHONEHOMECOST2,PHONEMOBILECOST, PHONEMOBILECOST1, PHONEMOBILECOST2, PHONEMOBILECOST3, PHONEWORKCOST') then
update JAMIS.SALARY SET
COMUNICATECOST=NEW.COMUNICATECOST,FAXCOST=NEW.FAXCOST,PHONEHOMECOST=NEW.PHONEHOMECOST, PHONEHOMECOST1=NEW.PHONEHOMECOST1,PHONEHOMECOST2=NEW.PHONEHOMECOST2,PHONEMOBILECOST=NEW.PHONEMOBILECOST, PHONEMOBILECOST1=NEW.PHONEMOBILECOST1, PHONEMOBILECOST2=NEW.PHONEMOBILECOST2, PHONEMOBILECOST3=NEW.PHONEMOBILECOST3, PHONEWORKCOST=NEW.PHONEWORKCOST
where EMPNO_INNER=OLD.EMPNO_INNER AND PAY_DATE=OLD.PAY_DATE;
ELSE IF UPDATING('E_MAIL,FAX,PHONE_HOME,PHONE_HOME1,PHONE_HOME2,PHONE_MOBILE,PHONE_MOBILE1, PHONE_MOBILE2,PHONE_MOBILE3, PHONE_WORK') then
update JAMIS.STAFF SET
E_MAIL=NEW.E_MAIL, FAX=NEW.FAX,PHONE_HOME=NEW.PHONE_HOME, PHONE_HOME1=NEW.PHONE_HOME1,PHONE_HOME2=NEW.PHONE_HOME2, PHONE_MOBILE=NEW.PHONE_MOBILE,PHONE_MOBILE1=NEW.PHONE_MOBILE1, PHONE_MOBILE2=NEW.PHONE_MOBILE2,PHONE_MOBILE3=NEW.PHONE_MOBILE3, PHONE_WORK=NEW.PHONE_WORK
where EMPNO=OLD.EMPNO;
END IF;
END;
在enterprise manager中看到触发器,编译有错。前台是用delphi的query来更新数据。每次更新数据时,前台程序总显示:触发器无效。
我写的触发器错在哪里?
我这样做能达到目的吗?
CREATE OR REPLACE TRIGGER "JAMIS"."TGETCOMMCOST" INSTEAD OF
UPDATE ON "JAMIS"."GETCOMMCOST"
FOR EACH ROW Begin
IF UPDATING('COMUNICATECOST,FAXCOST,PHONEHOMECOST,PHONEHOMECOST1,PHONEHOMECOST2,PHONEMOBILECOST, PHONEMOBILECOST1, PHONEMOBILECOST2, PHONEMOBILECOST3, PHONEWORKCOST') then
update JAMIS.SALARY SET
COMUNICATECOST=NEW.COMUNICATECOST,FAXCOST=NEW.FAXCOST,PHONEHOMECOST=NEW.PHONEHOMECOST, PHONEHOMECOST1=NEW.PHONEHOMECOST1,PHONEHOMECOST2=NEW.PHONEHOMECOST2,PHONEMOBILECOST=NEW.PHONEMOBILECOST, PHONEMOBILECOST1=NEW.PHONEMOBILECOST1, PHONEMOBILECOST2=NEW.PHONEMOBILECOST2, PHONEMOBILECOST3=NEW.PHONEMOBILECOST3, PHONEWORKCOST=NEW.PHONEWORKCOST
where EMPNO_INNER=OLD.EMPNO_INNER AND PAY_DATE=OLD.PAY_DATE;
ELSE IF UPDATING('E_MAIL,FAX,PHONE_HOME,PHONE_HOME1,PHONE_HOME2,PHONE_MOBILE,PHONE_MOBILE1, PHONE_MOBILE2,PHONE_MOBILE3, PHONE_WORK') then
update JAMIS.STAFF SET
E_MAIL=NEW.E_MAIL, FAX=NEW.FAX,PHONE_HOME=NEW.PHONE_HOME, PHONE_HOME1=NEW.PHONE_HOME1,PHONE_HOME2=NEW.PHONE_HOME2, PHONE_MOBILE=NEW.PHONE_MOBILE,PHONE_MOBILE1=NEW.PHONE_MOBILE1, PHONE_MOBILE2=NEW.PHONE_MOBILE2,PHONE_MOBILE3=NEW.PHONE_MOBILE3, PHONE_WORK=NEW.PHONE_WORK
where EMPNO=OLD.EMPNO;
END IF;
END;
在enterprise manager中看到触发器,编译有错。前台是用delphi的query来更新数据。每次更新数据时,前台程序总显示:触发器无效。
我写的触发器错在哪里?
我这样做能达到目的吗?