Oracle PL/SQL Before INSERT Trigger example
This article shows you how to use BEFORE INSERT TRIGGER, it’s fire BEFORE an INSERT operation is executed. In real life scenarios, it is mostly used for purposes like
- Data validation
- Update values automatically (e.g CREATED_BY, CREATION_DATE etc)
1. Table
Create a employee_details, we will try to insert different values into this table and observe the trigger behavior.
--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
table EMPLOYEE_DETAILS created.
2. Data Validation
2.1 am before insert trigger example to restrict invalid data entry:
- User can’t enter the employees’ date of birth, which doesn’t comply with the rule of 18 years age of the employee.
- User can’t enter future date of death.
2.2 Create a trg_before_emp_insr trigger on table employee_details
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(TO_DATE(sysdate,'DD-MON-YYYY'), 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;
2.3 Normal data.
-- setting date format to to 'DD-MON-YYYY' alter session set nls_date_format = 'DD-MON-YYYY'; INSERT INTO employee_details VALUES (1,'Patel','Thomas','18-MAY-1999','01-MAY-2017','HR',sysdate); -- output 1 rows inserted.
2.4 Test trigger raise error – Employee age must be greater than or equal to 18.
-- setting date format to to 'DD-MON-YYYY' alter session set nls_date_format = 'DD-MON-YYYY'; INSERT INTO employee_details VALUES (2,'Patel','Peter','18-MAY-2010','01-MAY-2017','HR',sysdate); -- error Error report - ORA-20000: Employee age must be greater than or equal to 18. ORA-06512: at "SYSTEM.TRG_BEFORE_EMP_INSR", line 18 ORA-04088: error during execution of trigger 'SYSTEM.TRG_BEFORE_EMP_INSR'
2.5 Test trigger raise error – Date of death can not be Future date.
-- setting date format to to 'DD-MON-YYYY' alter session set nls_date_format = 'DD-MON-YYYY'; INSERT INTO employee_details VALUES (3,'Patel','Thomas','18-MAY-1999','01-MAY-2040','HR',sysdate); -- error Error report - ORA-20000: Date of death can not be Future date. ORA-06512: at "SYSTEM.TRG_BEFORE_EMP_INSR", line 23 ORA-04088: error during execution of trigger 'SYSTEM.TRG_BEFORE_EMP_INSR'
3. Update Values
A before insert trigger example to update some values automatically.
CREATE OR REPLACE TRIGGER trg_before_emp_insr_userinfo BEFORE INSERT ON employee_details FOR EACH ROW DECLARE username varchar2(20); BEGIN -- Replaced by the current logged in user "HR" by a trigger. 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;
EMP_ID | FIRST_NAME | LAST_NAME | DATE_OF_BIRTH | DATE_OF_DEATH | CREATED_BY | CREATED_DATE |
1 | Patel | Thomas | 18-MAY-1999 | 01-MAY-2017 | HR | 24-MAY-2017 |
-- setting date format to to 'DD-MON-YYYY' alter session set nls_date_format = 'DD-MON-YYYY'; INSERT INTO employee_details VALUES (2,'Patel','Methew','01-JAN-1990','01-MAY-2005',null,null); INSERT INTO employee_details VALUES (3,'Patel','Methew','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 | Patel | Thomas | 18-MAY-1999 | 01-MAY-2017 | HR | 24-MAY-2017 |
2 | Patel | Methew | 01-JAN-1990 | 01-MAY-2005 | HR | 24-MAY-2017 |
3 | Patel | Methew | 01-JAN-1990 | 01-MAY-2005 | HR | 24-MAY-2017 |
References