我有一个名为“缺勤”的表,该表记录了员工下班的时间
CREATE TABLE Absence ( absence_id_pk varchar(6) NOT NULL, staff_id_fk varchar(6), start_date date, end_date date, reason varchar(30), PRIMARY KEY (absence_id_pk), FOREIGN KEY (staff_id_fk) REFERENCES Full_Time_Employee(staff_id_fk) );
我创建了一个视图来计算员工缺勤的总天数,如下所示:
CREATE VIEW employee_absence AS SELECT staff_id_pk, staff.first_name, staff.last_name, SUM(end_date -start_date) AS "Total Days Absent" FROM Staff, Absence WHERE Absence.staff_id_fk = Staff.staff_id_pk GROUP BY staff_id_pk, staff.first_name, staff.last_name ORDER BY staff_id_pk;
我是Triggers的新手,我想拥有一个Trigger,当缺席员工的总天数> 20天时,该消息会在屏幕上显示一条消息。作为Triggers的新手,我不知道该怎么做。
任何帮助或想法将不胜感激!
您可以通过以下两种方式之一进行操作:
我会选择触发器上的检查约束,如果雇员缺席超过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) violated SQL>
触发方式
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 8 RAISE_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 20 ORA-06512: at "LALIT.TRG", line 4 ORA-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 8 DBMS_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 20 1 row created. SQL>