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
Output
table EMPLOYEE_SALARY created.
Output
table EMPLOYEE_SALARY_LOG created.
Output
TRIGGER TRG_LOG_EMPLOYEE_SALARY compiled
Output
1 rows inserted. 1 rows inserted. 1 rows inserted.
Output
no rows selected.
Output
1 rows updated. 1 rows updated.
EMP_ID SALARY EMP_NAME
101 28000 Pranav
201 40000 Vikram
301 43000 Nikhil
EMP_ID NEW_SALARY UPDATED_DATE UPDATED_BY
101 28000 10-JUN-17 HR
301 43000 10-JUN-17 HR
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
Output
table EMPLOYEE_SALARY created.
Output
table EMPLOYEE_SALARY_HIKE_LOG created.
Output
TRIGGER TRG_LOG_SALARY_HIKE compiled
Output
1 rows inserted. 1 rows inserted. 1 rows inserted.
Output
no rows selected.
Output
1 rows updated. 1 rows updated. 1 rows updated.
EMP_ID SALARY EMP_NAME
101 70000 Pranav
201 45000 Vikram
301 100000 Nikhil
EMP_ID NEW_SALARY HIKE UPDATED_DATE UPDATED_BY
101 70000 55000 10-JUN-17 HR
301 100000 65000 10-JUN-17 HR
References
  1. Syntax :- Oracle official docs
  2. PL/SQL Triggers :- Oracle official docs
It's good to share...Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Google+Email this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *