Trigger Example

CREATE TABLE emp_rec_tb 

( 

emp_id   NUMBER(3)    , 

emp_name   VARCHAR2(30) , 

salary NUMBER(8)    , 

department_id NUMBER(3)

);

CREATE TABLE sal_details_log_tb 

(

emp_id NUMBER(3), 

old_sal NUMBER(8), 

new_sal NUMBER(8),

increament NUMBER(8), 

updated_time TIMESTAMP

);

CREATE OR REPLACE TRIGGER sal_update_trg

AFTER UPDATE OF salary ON emp_rec_tb 

FOR EACH ROW

/*  *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

Creating trigger to maintain log about updated salary details 

Author   :  Murugappan Annamalai

Date     :  23-APR-14 12.13.40.047963 PM +05:30

    *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* */

DECLARE

       v_inc_sal NUMBER(8);

BEGIN

    v_inc_sal := :NEW.salary - :OLD.salary;

INSERT INTO sal_details_log_tb(emp_id, old_sal, new_sal, updated_time, increament) VALUES (:NEW.emp_id, :OLD.salary, :NEW.salary, SYSTIMESTAMP, v_inc_sal);

Dbms_output.put_line('Employee ID :  ' || :NEW.emp_id  );

Dbms_output.put_line('New Salary  :  ' || :NEW.salary  );

Dbms_output.put_line('Old Salary  :  ' || :OLD.salary  );

Dbms_output.put_line('Increament  :  ' ||  v_inc_sal   );

END sal_update_trg;

BEGIN

INSERT INTO emp_rec_tb VALUES ('100', 'Raj'     , 40000 , 10);

INSERT INTO emp_rec_tb VALUES ('101', 'Senthil' , 34000 , 10);

INSERT INTO emp_rec_tb VALUES ('102', 'Karthi'  , 45000 , 20);

INSERT INTO emp_rec_tb VALUES ('103', 'Ramesh'  , 28000 , 30);

INSERT INTO emp_rec_tb VALUES ('104', 'Mohan'   , 12000 , 30);

commit;

END;

/

SELECT * FROM emp_rec_tb;

UPDATE emp_rec_tb 

SET salary = 27000

WHERE emp_id = 104;

UPDATE emp_rec_tb 

SET salary = 42000

WHERE emp_id = 101;

SELECT * FROM sal_details_log_tb;

DROP TABLE emp_rec_tb;

DROP TABLE sal_details_log_tb;

DROP TRIGGER sal_update_trg;