您可以通过以下两种方式之一进行操作：
- 检查基表上的约束。
- 在基本表上触发
我会选择触发器上的检查约束，如果雇员缺席超过20天，我将根本不允许雇员在表中输入记录。
检查约束
SQL> DROP TABLE absence PURGE;Table dropped.SQL>SQL> CREATE TABLE Absence 2 ( 3 absence_id_pk varchar(6) NOT NULL, 4 staff_id_fk varchar(6), 5 start_date date, 6 end_date date, 7 reason varchar(30), 8 PRIMARY KEY (absence_id_pk) 9 );Table created.SQL>SQL> ALTER TABLE Absence ADD ConSTRAINT chk CHECK(end_date - start_date <= 20);Table altered.SQL>SQL> INSERT INTO absence(absence_id_pk, start_date, end_date) VALUES(1, SYSDATE -20, SYSDATE);1 row created.SQL> INSERT INTO absence(absence_id_pk, start_date, end_date) VALUES(2, SYSDATE -21, SYSDATE);INSERT INTO absence(absence_id_pk, start_date, end_date) VALUES(2, SYSDATE -21, SYSDATE)*ERROR at line 1:ORA-02290: check constraint (LALIT.CHK) violatedSQL>
触发方式
SQL> DROP TABLE absence PURGE;Table dropped.SQL>SQL> CREATE TABLE Absence 2 ( 3 absence_id_pk varchar(6) NOT NULL, 4 staff_id_fk varchar(6), 5 start_date date, 6 end_date date, 7 reason varchar(30), 8 PRIMARY KEY (absence_id_pk) 9 );Table created.SQL> CREATE OR REPLACE TRIGGER trg 2 BEFORE INSERT 3 ON absence 4 FOR EACH ROW 5 BEGIN 6 IF :NEW.end_date - :NEW.start_date > 20 7 THEN 8RAISE_APPLICATION_ERROR(-20001, 'Total days absent are more than 20'); 9 END IF; 10 END; 11 /Trigger created.SQL>SQL> INSERT INTO absence(absence_id_pk, start_date, end_date) VALUES(1, SYSDATE -20, SYSDATE);1 row created.SQL> INSERT INTO absence(absence_id_pk, start_date, end_date) VALUES(2, SYSDATE -21, SYSDATE);INSERT INTO absence(absence_id_pk, start_date, end_date) VALUES(2, SYSDATE -21, SYSDATE) *ERROR at line 1:ORA-20001: Total days absent are more than 20ORA-06512: at "LALIT.TRG", line 4ORA-04088: error during execution of trigger 'LALIT.TRG'SQL>
如果仍然要允许插入，则只显示消息。然后，删除 RAISE_APPLICATION_ERROR 并放入 DBMS_OUTPUT 。
SQL> DROP TABLE absence PURGE;Table dropped.SQL>SQL> CREATE TABLE Absence 2 ( 3 absence_id_pk varchar(6) NOT NULL, 4 staff_id_fk varchar(6), 5 start_date date, 6 end_date date, 7 reason varchar(30), 8 PRIMARY KEY (absence_id_pk) 9 );Table created.SQL> CREATE OR REPLACE TRIGGER trg 2 BEFORE INSERT 3 ON absence 4 FOR EACH ROW 5 BEGIN 6 IF :NEW.end_date - :NEW.start_date > 20 7 THEN 8DBMS_OUTPUT.PUT_LINE('Total days absent are more than 20'); 9 END IF; 10 END; 11 /Trigger created.SQL>SQL> INSERT INTO absence(absence_id_pk, start_date, end_date) VALUES(1, SYSDATE -20, SYSDATE);1 row created.SQL> INSERT INTO absence(absence_id_pk, start_date, end_date) VALUES(2, SYSDATE -21, SYSDATE);Total days absent are more than 201 row created.SQL>
