创建一个触发器来计算在合同信息中插入元组时持续时间列的值。
ContractInfo Table
HAS_CONTRACT DATE_FROM DATE_TO DURATION
--------------------- --------- --------- ----------
Simon Palm 20-AUG-01 19-AUG-02 0
Simon Palm 20-SEP-02 19-AUG-03 0
Flut 10-JUN-99 09-SEP-02 0
如何触发触发器?我对触发器有点困惑。
SQL> create or replace trigger ContractInfo_buifer
2 before insert or update on contractinfo
3 for each row
4 declare
5 begin
6 :new.duration := :new.date_to - :new.date_from; -- answer in days.
7 END;
8 /
Trigger created.
SQL> insert into ContractInfo (HAS_CONTRACT, DATE_FROM, DATE_TO ) values ('Simon Palm', to_date('20-AUG-01', 'dd-mon-rr'), to_date('19-AUG-02', 'dd-mon-rr'))
2 /
1 row created.
SQL> select * from contractinfo;
HAS_CONTRA DATE_FROM DATE_TO DURATION
---------- --------- --------- ----------
Simon Palm 20-AUG-01 19-AUG-02 364
这就是我使用 Oracle 的方式:
CREATE OR REPLACE TRIGGER TRIG_ContractInfo
BEFORE INSERT OR UPDATE ON ContractInfo REFERENCING
NEW AS NEW
OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
IF INSERTING THEN
/* ON INSERT STATEMENTS */
END IF;
IF UPDATING THEN
/* ON UPDATE STATEMENTS */
END IF;
/* COMMON STATEMENTS */
END;
/