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.