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
Output
table JOB_OPENINGS created.
Output
TRIGGER TRG_BEFORE_EMP_UPDATE compiled
Output
1 rows inserted. 1 rows inserted. 1 rows inserted.
APPLICATION_ID FIRST_NAME LAST_NAME JOB_EXPERIENCE LAST_APPLIED_DATE
1 Mark Sharma 10 01-JAN-2012
2 Praveen Kumar 4 01-DEC-2010
3 Rahul Kohli 6 null
Output
Error report: SQL Error: ORA-20000: Job experience must be more than or equal to 3 years. ORA-06512: at “HR.TRG_BEFORE_EMP_UPDATE”, line 21 ORA-04088: error during execution of trigger ‘HR.TRG_BEFORE_EMP_UPDATE’ 20000. 00000 – “%s” *Cause: The stored procedure ‘raise_application_error’ was called which causes this error to be generated. *Action: Correct the problem as described in the error message or contact the application administrator or DBA for more information.
Output
Error report: SQL Error: ORA-20000: Previous application attempt must not be done in last 2 years. ORA-06512: at “HR.TRG_BEFORE_EMP_UPDATE”, line 15 ORA-04088: error during execution of trigger ‘HR.TRG_BEFORE_EMP_UPDATE’ 20000. 00000 – “%s” *Cause: The stored procedure ‘raise_application_error’ was called which causes this error to be generated. *Action: Correct the problem as described in the error message or contact the application administrator or DBA for more information.
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
Output
table PERSON_RECORDS created.
Output
TRIGGER TRG_BEFORE_PERSON_UPDATE compiled
Output
1 rows inserted. 1 rows inserted.

We have two rows in table as of now with below value.

PERSON_ID FIRST_NAME LAST_NAME HIRE_DATE UPDATED_BY UPDATED_DATE
101 Devil Mark 06-JUN-2017 null null
102 Finch Andrew 06-JUN-2017 null null

Output
1 rows updated. 1 rows updated.
PERSON_ID FIRST_NAME LAST_NAME HIRE_DATE UPDATED_BY UPDATED_DATE
101 Glenn Mark 06-JUN-2017 HR 06-JUN-2017
102 Mathew Andrew 06-JUN-2017 HR 06-JUN-2017
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
Output
table BANK_TRANSACTIONS created.
Output
table BANK_TRANSACTIONS_AUDIT created.
Output
TRIGGER TRG_BEFORE_UPDATE_TXN_AUDIT compiled
TXN_ID TXN_NUMBER PARTY_NAME TXN_DATE CREATED_BY CREATED_DATE
1 TXN1234 Peter Thomas 12-MAY-2017 HR 06-JUN-2017
2 TXN9999 Jemes Patel 10-JUN-2016 HR 06-JUN-2017
Output
no rows selected.
Output
1 rows updated. 1 rows updated.
TXN_ID TXN_NUMBER PARTY_NAME TXN_DATE CREATED_BY CREATED_DATE
1 NEWTXN8080 Peter Thomas 12-MAY-2017 HR 06-JUN-2017
2 NEWTXN9595 Jemes Patel 10-JUN-2016 HR 06-JUN-2017
TXN_ID TXN_NUMBER PARTY_NAME TXN_DATE CREATED_BY CREATED_DATE
1 TXN1234 Peter Thomas 12-MAY-2017 HR 06-JUN-2017
2 TXN9999 Jemes Patel 10-JUN-2016 HR 06-JUN-2017
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 *