CREATE Trigger by using OLD and NEW Qualifiers

Posted by tagicon_admin | August 5, 2015 | in : PL/SQL

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:

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


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.

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

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

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

After that, I am runing the following query again.

Now, it showing the information below.

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


Related Posts

Leave a Reply