Oracle PL/SQL Before UPDATE Trigger example

This article shows you how to use BEFORE UPDATE TRIGGER, it’s fire before the update operation is executed. In real life scenarios, it is mostly used for purposes like:

1. Data validation
2. Update values automatically
3. Data logging, or auditing

1. Data Validation

Suppose some companies have job openings and already having application data and the criteria are :

1. Job Experience must be more than or equal to 3 years and
2. Previous application attempt must not be done in last 2 years.

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

1.1 First, we will create table job_openings.

--Creating job_openings table.
CREATE TABLE job_openings
    APPLICATION_ID number(10) primary key,
    FIRST_NAME varchar2(50),
    LAST_NAME varchar2(50),
    JOB_EXPERIENCE number(2),
    LAST_APPLIED_DATE date
);

1.2 Then we will create a before update tigger on column JOB_EXPERIENCE and LAST_APPLIED_DATE of job_openings table.

-- Creating TRIGGER
CREATE OR REPLACE TRIGGER trg_before_emp_update
BEFORE UPDATE OF JOB_EXPERIENCE,LAST_APPLIED_DATE
  on job_openings
  FOR EACH ROW 
DECLARE
years_since_last_applied number(5);
BEGIN
years_since_last_applied := -1;
  IF(:NEW.LAST_APPLIED_DATE IS NOT NULL) THEN
    SELECT MONTHS_BETWEEN(TO_DATE(sysdate,'DD-MON-YYYY'), TO_DATE(:NEW.LAST_APPLIED_DATE,'DD-MON-YYYY'))/12
      INTO years_since_last_applied FROM dual;
   -- Check whether years_since_last_applied is greater than 2 years or not
    IF (years_since_last_applied <= 2) THEN
      RAISE_APPLICATION_ERROR(-20000,'Previous application attempt must not be done in last 2 years.');
    END IF;
  END IF;
    -- Job experience must be more than or equal to 3 years.
    IF(:new.JOB_EXPERIENCE < 3) THEN
      RAISE_APPLICATION_ERROR(-20000,'Job experience must be more than or equal to 3 years.');
    END IF;          
END;

1.3 Normal Data.

-- setting date format to to 'DD-MON-YYYY'
alter session set nls_date_format = 'DD-MON-YYYY';
INSERT INTO job_openings VALUES (1,'Mark','Sharma',10,'01-JAN-2012');
INSERT INTO job_openings VALUES (2,'Praveen','Kumar',4,'01-DEC-2010');
INSERT INTO job_openings VALUES (3,'Rahul','Kohli',6,null);
-- output
1 rows inserted.
1 rows inserted.
1 rows inserted.

select * from job_openings;

1.4 Test Trigger raise error – Job experience must be more than or equal to 3 years.

-- Try to update job_experience less than 3 years
UPDATE job_openings SET JOB_EXPERIENCE = 2 where APPLICATION_ID = 1;
-- error 
Error report -
ORA-20000: Job experience must be more than or equal to 3 years.
ORA-06512: at "SYSTEM.TRG_BEFORE_EMP_UPDATE", line 21
ORA-04088: error during execution of trigger 'SYSTEM.TRG_BEFORE_EMP_UPDATE'

1.5 Test Trigger raise error – Previous application attempt must not be done in last 2 years.

-- Try to update last_Applied_Date which is less than 2 years
UPDATE job_openings SET LAST_APPLIED_DATE = '10-JUN-2016' where APPLICATION_ID = 3;
-- error
Error report -
ORA-20000: Previous application attempt must not be done in last 2 years.
ORA-06512: at "SYSTEM.TRG_BEFORE_EMP_UPDATE", line 15
ORA-04088: error during execution of trigger 'SYSTEM.TRG_BEFORE_EMP_UPDATE'

2. Update values

A BEFORE UPDATE trigger example to update :NEW.UPDATED_BY and :NEW.UPDATED_DATE automatically.

2.1 Create a table.

--Creating person_records table.
CREATE TABLE person_records
    PERSON_ID number(10) primary key,
    FIRST_NAME varchar2(50),
    LAST_NAME varchar2(50),
    HIRE_DATE date,
    UPDATED_BY varchar2(20),
    UPDATED_DATE date
);

2.2 Create a before update trigger on table person_records

CREATE OR REPLACE TRIGGER trg_before_person_update
BEFORE UPDATE
  on person_records
  FOR EACH ROW 
DECLARE
username varchar2(20);
BEGIN
  SELECT USER INTO username FROM dual;
  -- Setting updated_by and updated_Date values.
  :NEW.UPDATED_BY := username;
  :NEW.UPDATED_DATE := sysdate;
END;

2.3 Insert two records.

-- setting date format to to 'DD-MON-YYYY'
alter session set nls_date_format = 'DD-MON-YYYY';
INSERT INTO person_records VALUES (101,'Devil','Khedut',sysdate,null,null);
INSERT INTO person_records VALUES (102,'Kanji','Yadav',sysdate,null,null);
-- output
1 rows inserted.
1 rows inserted.

select * from person_records;

2.4 Update records and it will fires the before update trigger. The values of UPDATED_BY and UPDATED_DATE will be updated automatically.

UPDATE person_records SET first_name = 'Lavji' WHERE person_id = 101;
UPDATE person_records SET first_name = 'Jogi' WHERE person_id = 102;
-- output
1 rows updated.
1 rows updated.

select * from person_records;

3. Logging/Auditing data

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

3.1 Create a bank transaction table.

--Creating bank_transactions table.
CREATE TABLE bank_transactions
    TXN_ID number(10) primary key,
    TXN_NUMBER varchar2(20),
    PARTY_NAME varchar2(50),
    TXN_DATE date,
    CREATED_BY varchar2(20),
    CREATED_DATE date
);

3.2 Create another bank transaction audit table.

--Creating bank_transactions_audit table.
CREATE TABLE bank_transactions_audit
    TXN_ID number(10),
    TXN_NUMBER varchar2(20),
    PARTY_NAME varchar2(50),
    TXN_DATE date,
    CREATED_BY varchar2(20),
    CREATED_DATE date
);

3.3 Create a before update trigger on bank_transactions table.

--Creating Trigger
CREATE OR REPLACE TRIGGER trg_before_update_txn_audit
BEFORE UPDATE
  ON bank_transactions
  FOR EACH ROW
BEGIN
  -- Insert OLD values in audit table for logging purpose
  INSERT INTO bank_transactions_audit VALUES(:OLD.TXN_ID,:OLD.TXN_NUMBER,
    :OLD.PARTY_NAME,:OLD.TXN_DATE,:OLD.CREATED_BY,:OLD.CREATED_DATE);
END;

3.4 Insert some records.

--Inserting values
INSERT INTO BANK_TRANSACTIONS values ('1','TXN1234','Peter Thomas','12-MAY-2017','HR',sysdate);
INSERT INTO BANK_TRANSACTIONS values ('2','TXN9999','Jemes Patel','10-JUN-2016','HR',sysdate);
select * from BANK_TRANSACTIONS;

3.5 Insert operation didn't fire the before update trigger.

select * from BANK_TRANSACTIONS_AUDIT;
-- output
no rows selected.

3.6 Update records, it will fires the "before update" trigger and insert the old data into another audit table.

--updating values.
UPDATE bank_transactions SET txn_number = 'NEWTXN8080' WHERE txn_id = 1;
UPDATE bank_transactions SET txn_number = 'NEWTXN9595' WHERE txn_id = 2;
-- output
1 rows updated.
1 rows updated.

select * from BANK_TRANSACTIONS;

select * from BANK_TRANSACTIONS_AUDIT;

References

1. Oracle - CREATE TRIGGER Statement
2. Oracle - PL/SQL Triggers