How to use trigger to check database constant

We can use trigger to check database constant and customize error message. For example we have two tables name Department and Employee. I have written two trigger on Update and Delete on Department table, which will responsible to check referential integrity constraint and customize the error message.

Creating Tables Department and Employee

CREATE TABLE DEPARTMENT

  (

    Department_Id          Number(*,0) Not Null Enable,

    Department_Name        Varchar2(45 Byte) Not Null Enable,

    Department_Description Varchar2(500 Byte),

    Primary Key (Department_Id) 

  );

CREATE TABLE EMPLOYEE

  (

    Employee_Name        Varchar2(45 Byte) Not Null Enable,

    Employee_Ssn         Varchar2(45 Byte) Not Null Enable,

    Employee_Phone       Varchar2(45 Byte) Not Null Enable,

    Employee_Cellular    Varchar2(45 Byte) Not Null Enable,

    Employee_Description Varchar2(500 Byte),

    Department_Id        Number(*,0) Not Null Enable,

    Primary Key (Employee_Ssn) ,

    Foreign Key (Department_Id) References Department (Department_Id) Enable

  );

Insert some data on Department and Employee Table

Insert Into Department (Department_Id, Department_Name, Department_Description) 

Values ('1001', 'Tecnical', 'Tecnical Department');

Insert Into Department (Department_Id, Department_Name, Department_Description) 

Values ('1002', 'Merketing', 'Merketing Department');

Insert Into EMPLOYEE (Employee_Name, Employee_Ssn, Employee_Phone, Employee_Cellular, Department_Id) 

Values ('Tamim', '100001', '880175307713', '880175307713', '1001');

Trigger on Delete of Department

Create Or Replace Trigger Td_Department 

  After Delete on Department 

  FOR EACH row

    DECLARE numrows INTEGER;

  BEGIN

    SELECT COUNT(*) INTO numrows

    FROM Employee

    WHERE  Employee.Department_ID = :old.Department_ID;

    IF (numrows> 0) THEN

      raise_application_error( -20001, 'Cannot DELETE Department because Employee exists.' );

    END IF;

  END;

  /

Test a Delete SQL Statement on Department Table

  Delete 

  From Department 

  Where Department_Id = 1001 

  /*

  SQL Error: ORA-20001: Cannot DELETE Department because Employee exists.

  */

Trigger on Update of Department

Create Or Replace Trigger Tu_Department 

  After Update On Department 

  For Each Row

    Declare Numrows Integer;

  Begin

    IF (:old.Department_ID <> :new.Department_ID) THEN

      Select Count(*) Into Numrows    

      FROM Employee

      Where Employee.Department_Id = :Old.Department_Id;

      IF (numrows > 0) THEN

        raise_application_error( -20005, 'Cannot UPDATE Department because Employee exists.' );

      END IF;

    END IF;

  END;

  /

Test a Update SQL Statement on Department Table

  Update Department 

  Set Department_Id = 1003

  Where Department_Id = 1001 

  /*

  SQL Error: ORA-20005: Cannot UPDATE Department because Employee exists.

  */