CREATE Trigger by Using OLD and NEW Qualifiers

Basically OLD and NEW qualifiers are used for row-level trigger. When a row-level trigger is createsand fires, the PL/SQL run time engine creates and populates two data structures that are OLD and NEW.

OLD is used to store the original values from the database that is processed by the trigger. New qualifier is used to store the new values. These qualifiers have the same structure.

Some rules of OLD and NEW qualifiers:
–>The OLD and NEW qualifiers are available only in row level triggers.
–>OLD and NEW qualifiers are used with a colon (:) in every SQL and PL/SQL statement.
–>If the qualifiers are referenced in the WHEN restricting condition then colon (:) prefix is not used in there.

 

Example of OLD and NEW Qualifiers:

CREATE TABLE desc_editor
   (user_name      VARCHAR2(25),
    time_stamp     DATE,
    id             NUMBER(8),
    old_last_name  VARCHAR2(30),
    new_last_name   VARCHAR2(30),
    old_salary     NUMBER(8,1),
    new_salary     NUMBER(8,1));

First of all, I have created table above for inserting data by creating trigger.

CREATE OR REPLACE TRIGGER edit_value
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH ROW
 BEGIN
    INSERT INTO desc_editor(user_name, time_stamp, id, old_last_name, new_last_name, old_salary, new_salary)
    VALUES (USER, SYSDATE, :OLD.employee_id, :OLD.last_name, :NEW.last_name, :OLD.salary, :NEW.salary);
 END;
/

 

Now, I have created edit_value trigger for my employee table. This trigger will add rows in the desc_editor table for logging a user’s activity against the EMPLOYEES table. This trigger will insert data both before and after of any changes in the desc_editor table by using the OLD and NEW qualifiers. Now I am inserting below data to test my trigger.

INSERT INTO employees (employee_id, last_name, salary, email, hire_date, job_id)
 VALUES(420, 'Test trigger', 50000, 'empnew', SYSDATE, 'SA_REP');

After inserting, run the query mentioned below to see all data of desc_editor table.

SELECT *
FROM desc_editor;

Above query will show me the following data which are inserted by creating edit_value trigger.

USER_NAME TIME_STAM  ID    OLD_LAST_NAME NEW_LAST_NAME   OLD_SALARY NEW_SALARY
--------- ---------- ----- ------------- --------------- ---------- ----------
HR        05-AUG-15                      Test trigger                    50000

Now, I am updating data by using the information below which is inserted into desc_editor table.

UPDATE employees
SET salary = 1000, last_name = 'Trigger Test'
WHERE employee_id = 420;

After that, I am runing the following query again.

SELECT *
FROM desc_editor

Now, it showing the information below.

USER_NAME TIME_STAM  ID    OLD_LAST_NAME   NEW_LAST_NAME   OLD_SALARY  NEW_SALARY
--------- ---------- ----  --------------  --------------  ----------  ---------- 
HR        05-AUG-15                        Test trigger                     50000
HR        05-AUG-15   420  Test trigger    Trigger Test         50000        1000

So, we can easily insert all record of the user into another table by using the OLD and NEW Qualifiers in trigger.