Home > Database Script, Trigger to check Constant > How to use trigger to check database constant

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.
  */
About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 70 other followers

%d bloggers like this: