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.
*/
