Oracle PL/SQL Before INSERT Trigger Example
Overview
This Article will help you to understand “BEFORE INSERT TRIGGER statement” with examples and it’s detailed description.
This statement specifies that Oracle will fire this trigger BEFORE the INSERT operation is executed.
- In real life scenarios, BEFORE INSERT Trigger mostly used for purposes like, Restrict Invalid Data Entry in DB.
- Insert/Update some value before insertion of the data (e.g CREATED_BY, CREATION_DATE etc).
Restrictions on BEFORE INSERT Trigger:
- We can not create this trigger on VIEW.
- We can not update :OLD value, as in INSERT it’s not present.
Examples
Validation
(1) Restricting some invalid data entry in DB.
Description
In this example we have introduced two restrictions on before insert statement. User can’t enter employees’ date of birth which doesn’t comply with the rule of 18 years age of employee. In other restriction, user can’t enter Future Date of Death. If any condition will be violated then trigger will Raise application error which will give error message and stops execution of INSERT statement.
First, we will create table employee_details.
Then we will create “trg_before_emp_insr” Trigger on table employee_details.
We will try to insert different values in this table and observe the result.
Code
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
--Creating employee_details table. CREATE TABLE employee_details ( EMP_ID number(10) primary key, FIRST_NAME varchar2(50), LAST_NAME varchar2(50), DATE_OF_BIRTH date, DATE_OF_DEATH date, CREATED_BY varchar2(20), CREATED_DATE date ); |
Output:
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
-- Creating TRIGGER CREATE OR REPLACE TRIGGER trg_before_emp_insr BEFORE INSERT on employee_details FOR EACH ROW DECLARE emp_age number; BEGIN -- Finding employee age by date of birth SELECT MONTHS_BETWEEN(TRUNC(sysdate), TO_DATE(:new.DATE_OF_BIRTH,'DD-MON-YYYY'))/12 INTO EMP_AGE FROM DUAL; -- Check whether employee age is greater than 18 or not IF (EMP_AGE < 18) THEN RAISE_APPLICATION_ERROR(-20000,'Employee age must be greater than or equal to 18.'); END IF; -- Allow only past date of death IF(:new.DATE_OF_DEATH > sysdate) THEN RAISE_APPLICATION_ERROR(-20000,'Date of death can not be Future date.'); END IF; END; / |
Output
0 1 2 3 4 5 6 |
-- setting date format to to 'DD-MON-YYYY' alter session set nls_date_format = 'DD-MON-YYYY'; INSERT INTO employee_details VALUES (1,'Churchil','Thomas','18-MAY-1999','01-MAY-2017','HR',sysdate); |
Output
0 1 2 |
INSERT INTO employee_details VALUES (2,'Mathew','Peter','18-MAY-2010','01-MAY-2017','HR',sysdate); |
Output
0 1 2 |
INSERT INTO employee_details VALUES (3,'Brett','Avis','18-MAY-1999','01-MAY-2040','HR',sysdate); |
Output
Insert or Update values (same table)
(2) Insert/Update some value before insertion of the data (e.g created_by, created_date etc).
Description
In This example, we will insert values of few columns (here, created_by and created_date) without caring about what values given in INSERT statement. whether user enters null or any values in these fields, Trigger will update those values and then in Database, newly updated values will be inserted.
Here, we are using the same table “employee_details” which we created in previous example.
We will insert three rows in this table and observe the inserted values in DB.
In First INSERT statement, values of CREATED_BY and CREATED_DATE is null. And in second INSERT, value of CREATED_BY is “XYZ” which will be replaced by current logged in user “HR” by trigger.
We have three rows, notice the values of CREATED_BY and CREATED_DATE in other two rows.
In Tirgger, CREATED_BY and CREATED_DATE are being set without taking any care of provided values in INSERT statement.
Code
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE OR REPLACE TRIGGER trg_before_emp_insr_userinfo BEFORE INSERT ON employee_details FOR EACH ROW DECLARE username varchar2(20); BEGIN SELECT USER INTO username FROM dual; -- Setting created_by and created_Date values. :NEW.CREATED_BY := username; :NEW.CREATED_DATE := sysdate; END; / -- setting date format to to 'DD-MON-YYYY' alter session set nls_date_format = 'DD-MON-YYYY'; select * from employee_details; |
We have one row in table as of now with below value.
EMP_ID | FIRST_NAME | LAST_NAME | DATE_OF_BIRTH | DATE_OF_DEATH | CREATED_BY | CREATED_DATE |
1 | Churchil | Thomas | 18-MAY-1999 | 01-MAY-2017 | HR | 24-MAY-2017 |
0 1 2 3 4 5 6 |
INSERT INTO employee_details VALUES (2,'Mathew','Peter','01-JAN-1990','01-MAY-2005',null,null); INSERT INTO employee_details VALUES (3,'Brett','Avis','01-JAN-1990','01-MAY-2005','XYZ',null); select * from employee_details; |
EMP_ID | FIRST_NAME | LAST_NAME | DATE_OF_BIRTH | DATE_OF_DEATH | CREATED_BY | CREATED_DATE |
1 | Churchil | Thomas | 18-MAY-1999 | 01-MAY-2017 | HR | 24-MAY-2017 |
2 | Mathew | Peter | 01-JAN-1990 | 01-MAY-2005 | HR | 24-MAY-2017 |
3 | Brett | Avis | 01-JAN-1990 | 01-MAY-2005 | HR | 24-MAY-2017 |
Insert or Update values (different table)
(3) Insert/Update some value before insertion of the data
Description
This is similar kind of example like previous example.
Here first we will create table “bank_transactions”.
On this table, we will create BEFORE INSERT Trigger “trg_before_txn_insr_txninfo”.
Then we’ll insert values in this table and observe the data inserted into DB.
In first INSERT, user is not providing values of txn_date, created_by and created_date. But after insert, values will be provided by Tirgger.
In Second INSERT, user is providing future txn date (e.g. 10-JUN-2050), but suppose we have requirement to keep txn_Date as of sysdate. So trigger will update this value to sysdate.
Code
0 1 2 3 4 5 6 7 8 9 10 11 12 |
--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 ); |
Output
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
--Creating Trigger CREATE OR REPLACE TRIGGER trg_before_txn_insr_txninfo BEFORE INSERT ON bank_transactions FOR EACH ROW DECLARE username varchar2(20); BEGIN SELECT USER INTO username FROM dual; -- Setting txn_date, created_by and created_date values. :NEW.TXN_DATE := sysdate; :NEW.CREATED_BY := username; :NEW.CREATED_DATE := sysdate; END; / |
Output
0 1 2 3 4 5 6 7 8 |
--Inserting values INSERT INTO BANK_TRANSACTIONS values ('1','TXN1234','Peter Thomas',null,null,null); INSERT INTO BANK_TRANSACTIONS values ('2','TXN9999','Jemes Patel','10-JUN-2050',null,null); select * from BANK_TRANSACTIONS; |
TXN_ID | TXN_NUMBER | PARTY_NAME | TXN_DATE | CREATED_BY | CREATED_DATE |
1 | TXN1234 | Peter Thomas | 24-MAY-2017 | HR | 24-MAY-2017 |
2 | TXN9999 | Jemes Patel | 24-MAY-2017 | HR | 24-MAY-2017 |
I was suggested this blog by my cousin. I am not sure whether this post is written by him as nobody else know such detailed about my problem. You are wonderful! Thanks!|