Oracle PL/SQL Before UPDATE Trigger Example

Overview

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

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

In real life scenarios, BEFORE UPDATE Trigger mostly used for purposes like, (1) Restrict Invalid Data Entry in DB via UPDATE statement, (2) Updating some data into same table. (e.g. UPDATED_BY, UPDATE_DATE) and (3)Logging/Auditing data before UPDATE of the data.

Restrictions on BEFORE UPDATE Trigger

1. We can not create this trigger on VIEW.
2. We can not update :OLD value.

Examples

Validation 

(1) Restrict Invalid Data Entry in DB via UPDATE statement.

Description

In this example we have introduced two restriction on before UPDATE trigger statement. 

Suppose some company have job openings and already having application data and the criteria is (i) Job Experience must be more than or equal to 3 years and (ii) Previous application attempt must not be done in last 2 years.

To ensure data intigrity, We will create before UPDATE trigger and it will restrict UPDATE on data which violates any of above criteria.

First, we will create table job_openings. 

Then we will create “trg_before_emp_update” Trigger on columns JOB_EXPERIENCE and LAST_APPLIED_DATE of job_openings table.

We will first insert some values into this table and then, We will try to update different values in this table and observe the result.

Code

Update values

(2) Updating some data into same table. (e.g. UPDATED_BY, UPDATE_DATE)

Description

In This example, we will update values of UPDATED_BY and UPDATE_DATE whenever user executes UPDATE statement on person_records table.

Whether user enters null or any values in these fields, Trigger will update those values and then in Database, newly updated values will be updated.

We will create table person_records. Then we will create BEFORE UPDATE Trigger “trg_before_person_update” on person_records table.

Then insert two rows in this table and observe the inserted values in DB.

Update first_name column of both rows with some new value and then will observe the table data.

The value of UPDATED_BY and UPDATE_DATE will be updated by trigger.

Code

Logging/Auditing data

(3)Logging/Auditing data before UPDATE of the data.

Description

In this example we have created a trigger which will insert rows into audit table before each updation on transaction table. Whenever user will UPDATE data of bank_transactions, the old data will be inserted into bank_transactions_audit by Trigger for audit or backup purpose.

First, we will create transaction table bank_transactions.

Then we will create audit table bank_transactions_audit. Avoid to use primary or unique constraint on audit table to avoid any constraint violation in insertion of the same row multiple times in case of multiple updates of transaction table.

Then we will create “trg_before_update_txn_audit” Trigger on table bank_transactions. 

We will insert different values in bank_transactions table. Then select data from bank_transactions and bank_transactions_audit both tables. 

Then we will update values of TXN_NUMBER column of bank_transactions table and will observe values of bank_transactions and bank_transactions_audit tables.

You will notice, in audit table, the old values are inserted.

Code