Oracle PL/SQL After UPDATE Trigger Example

Oracle PL/SQL After UPDATE Trigger Example

Overview

This Article will help you to understand “AFTER UPDATE TRIGGER statement” with examples and it’s detailed description.

This statement specifies that Oracle will fire this trigger AFTER the UPDATE operation is executed.

In real life scenarios, AFTER UPDATE Trigger mostly used for Data Logging in audit table.

Note :- Do not create recursive triggers. Creating an AFTER UPDATE statement trigger on the employee_salary table that itself issues an UPDATE statement on employee_salary, causes the trigger to fire recursively until it has run out of memory.

Restrictions on AFTER UPDATE Trigger
  1. You cannot specify an AFTER trigger on a view or an object view.
  2. You cannot write either the :OLD or the :NEW value.

Examples

Data Logging 

(1) Data Logging in audit table.

Description

In this example we have created a trigger which will insert rows into audit table after each update on SALARY column of employee_salary table. Whenever user will update data of SALARY column in employee_salary table, the data row from employee_salary table will also be inserted into employee_salary_log by Trigger.

First, we will create table employee_salary.

Then we will create audit table employee_salary_log.

Then we will create “trg_log_employee_salary” Trigger on SALARY column of employee_salary table. This trigger will insert values of the row for which salary column is updated by UPDATE statement. 

We will insert different values in employee_salary table. Then select data from employee_salary_log. No data will be there.

Now UPDATE salary in employee_salary table for few employees. Then select data from employee_salary and employee_salary_log.

We will get the data rows from log table for which we have updated value of salary in employee_salary table.

Code

Data Logging with WHEN condition 

(2) Data Logging in audit table with WHEN condition in trigger.

Description

In this example we have created a trigger which will insert rows into audit table after each update on SALARY column of employee_salary table, whenever the hike is Greater than 50000.

Whenever user will update data of SALARY column in employee_salary table with hike greater than 50000, the data row from employee_salary table will also be inserted into employee_salary_hike_log by Trigger.

First, we will create table employee_salary same as first example.

Then we will create audit table employee_salary_hike_log.

Then we will create “trg_log_salary_hike” Trigger on SALARY column of employee_salary table with WHEN condition. This trigger will insert values of the row for which salary column is updated by UPDATE statement. 

We will insert different values in employee_salary table. Then select data from employee_salary_hike_log. No data will be there.

Then we will UPDATE salary in employee_salary table for few employees. Then select data from employee_salary and employee_salary_hike_log.

We will get the data rows from log table for which we have updated value of salary with hike of 50000 in employee_salary table.

Code: