admin

用于视图的SQL触发器

sql

我有一个名为“缺勤”的表,该表记录了员工下班的时间

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的新手,我不知道该怎么做。

任何帮助或想法将不胜感激!


阅读 197

收藏
2021-06-07

共1个答案

admin

您可以通过以下两种方式之一进行操作:

  1. 检查基表上的约束。
  2. 在基本表上触发

我会选择触发器上的检查约束,如果雇员缺席超过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>
2021-06-07