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:
- Data validation
- Update values automatically
- Data logging, or auditing
1. Data Validation
Suppose some companies have job openings and already having application data and the criteria are :
- Job Experience must be more than or equal to 3 years and
- 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;
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.
--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;
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.
--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;
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 |
Nice article. It helped me a lot. Thank you so much.
I am getting error when i try to do the update everything else is working
select * from employees;
Drop table emp_changing_sal;
CREATE TABLE emp_changing_sal(empid NUMBER, new_sal NUMBER, UPDATED_TIME DATE, UPDATED_BY VARCHAR2(15));
DROP trigger emp_change;
CREATE TRIGGER emp_change
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO emp_changing_sal(empid,new_sal,UPDATED_TIME,UPDATED_BY) VALUES (:NEW.employee_id,:NEW.salary,SYSDATE,USER);
END;
/
UPDATE employees SET salary = salary + 200 WHERE employee_id = 105;
Hi Thanks for this informations, i inform you thzt i have issue in creation trigger, my trigger avoid to insert or update if the new.value=old.value in my table oracle, i try a lot of example but i have all times the issues, for information, the attribute that i want to update or insert is not a unique key.
please help me.
Best regards,