SQL Trigger for View

Question

I have a table called Absence which records periods of Staff absence from work

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)
);

and I have created a view to count the total number of days an employee has been absent like so:

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;

I am new to Triggers and what I want to have is a Trigger that prints out a message to the screen when a Staff's total days absent > 20 days. Being completely new to Triggers, I don't have much idea how to go about this.

Any help or ideas would be greatly appreciated!

Answer 1

There are several problems with your solution.

• Never use DBMS_OUTPUT in a trigger. This may work fine when you write it and test from your IDE, but it generally will not result in any messages getting out to any application. Triggers write to log tables.
• Your view trigger only catches the problem on the next insert after the problem happens. This could be days or months afterward and absences could have become much larger than 20 by then.
• There is really no reason to perform an insert to the view. You can't insert a new absence using the view because the view doesn't expose the dates. Or are you performing an INSERT just to check the days absent? In that case, why don't you just query from the view where days > 20?

Your best course of action is to have the After Insert and Update trigger on the table itself check the number of days absent and write a log entry if the days > 20. This log entry can contain any information the trigger can obtain: if the operation was an Insert or Update, the employee this effects, the user who performed the operation, the date and time the operation was performed, the number of absent days before the operation took affect, the number of absent days after the operation took affect and so on.

A scheduled job could check the log table and do something (send email, whatever) when it comes across new entries.

Answer 2

You could do it in either of the two ways:

1. Check constraint on the base table.
2. Trigger on the base table

I would chose the check constraint over the trigger, I would simply not allow an employee to enter record into the table if his absence is more than 20 days.

CHECK constraint

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>

TRIGGER appraoch

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>

If you still want to allow the insert, however just display message. Then, remove the RAISE_APPLICATION_ERROR and put a DBMS_OUTPUT instead.

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>