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.

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.

trg_before_emp_update

-- 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;
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

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.

person_records

--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

trg_before_person_update

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;
PERSON_ID FIRST_NAME LAST_NAME HIRE_DATE UPDATED_BY UPDATED_DATE
101 Devil Khedut 06-JUN-2017 null null
102 Kanji Yadav 06-JUN-2017 null null

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;
PERSON_ID FIRST_NAME LAST_NAME HIRE_DATE UPDATED_BY UPDATED_DATE
101 Lavji Khedut 06-JUN-2017 HR 06-JUN-2017
102 Jogi Yadav 06-JUN-2017 HR 06-JUN-2017

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.

bank_transactions

--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.

bank_transactions_audit

--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.

trg_before_update_txn_audit

--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;
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

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;
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

select * from BANK_TRANSACTIONS_AUDIT;
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. Oracle - CREATE TRIGGER Statement
  2. Oracle - PL/SQL Triggers

About the Author

author image
Dhaval Dadhaniya
Dhaval Dadhaniya is a software engineer by profession and reader/writter by passion. He is working in a well reputed MNC as an applications Developer with 5 years of experience. He have good understanding and knowledge of Java, Database, Spring, Hibernate and exploring other technologies related to Software development. Befriend him on Facebook

Comments

Leave a Reply

avatar