oracle trigger example

Oracle Trigger

• Update Trigger: we will follow the belowexample

In this example I am created two tables employee and department. When employee change the department then one employee is deducted from his existing department and new employee is get added into new department.




CREATE TABLE TBL_DEPARTMENT (
ID NUMBER(10) primary key,
NAME     VARCHAR2(100),
EMPLOYEE_TOTAL  NUMBER(10));


CREATE TABLE TBL_EMPLOYEE (
ID NUMBER(10) primary key,
NAME     VARCHAR2(100),
DESIGNATION  VARCHAR2(100),
DEPARTMENT_ID  NUMBER(10),
CONSTRAINT "TBL_EMPLOYEE _FK"  FOREIGN KEY ("DEPARTMENT_ID")
REFERENCES "TBL_DEPARTMENT" ("ID") ENABLE);


insert into TBL_DEPARTMENT (ID,NAME,EMPLOYEE_TOTAL) values(1,'Account',5);
insert into TBL_DEPARTMENT (ID,NAME,EMPLOYEE_TOTAL) values(2,'Admin',2);
insert into TBL_EMPLOYEE (ID,NAME,DESIGNATION, DEPARTMENT_ID) values(1,'Mr. Employess','Manager',1);


CREATE OR REPLACE TRIGGER TR_EMPLOYEE_UPDATE
BEFORE UPDATE ON TBL_EMPLOYEE
FOR EACH ROW
DECLARE
BEGIN
      IF(:OLD.DEPARTMENT_ID != :NEW.DEPARTMENT_ID) THEN
      UPDATE TBL_DEPARTMENT set EMPLOYEE_TOTAL = EMPLOYEE_TOTAL+1 where ID = :NEW.DEPARTMENT_ID;
        UPDATE TBL_DEPARTMENT set EMPLOYEE_TOTAL = EMPLOYEE_TOTAL-1 where ID = :OLD.DEPARTMENT_ID;
      END IF;
END TR_EMPLOYEE_UPDATE;


select * from tbl_department;
update tbl_employee set department_id=2 where id=1;
select * from tbl_department ;


 If you check the employee count of the department  before and after the update query for department then you can realize the difference.

• Insert Trigger : We will continue above example, suppose we add new employee,  our corresponding department count will increase automatically.

CREATE OR REPLACE TRIGGER TR_EMPLOYEE_INSERT
AFTER INSERT ON TBL_EMPLOYEE
FOR EACH ROW
DECLARE
BEGIN
      UPDATE TBL_DEPARTMENT set EMPLOYEE_TOTAL = EMPLOYEE_TOTAL+1 where ID = :NEW.DEPARTMENT_ID;
END  TR_EMPLOYEE_INSERT ;

select * from tbl_department;
insert into TBL_EMPLOYEE (ID,NAME,DESIGNATION, DEPARTMENT_ID) values(2,'Mr. Employess New','Ass. Manager',1);
select * from tbl_department ;
Execute the above statement in sequence then just compare the output of department count before and after the employee insert statement.

• Delete Trigger : For delete also we will continue with same example , suppose we delete some employee from our database then the corresponding department count will be deducted by 1.

CREATE OR REPLACE TRIGGER TR_EMPLOYEE_DELETE
BEFORE DELETE ON TBL_EMPLOYEE
FOR EACH ROW
DECLARE
BEGIN
      UPDATE TBL_DEPARTMENT set EMPLOYEE_TOTAL = EMPLOYEE_TOTAL-1 where ID = :OLD.DEPARTMENT_ID;
END TR_EMPLOYEE_DELETE;


select * from tbl_department;
delete from tbl_employee where id = 2;
select * from tbl_department;


Execute the above statement in sequence then just compare the output of department count before and after the employee delete statement.