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

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

  1. Data validation
  2. 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.

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

  1. User can’t enter the employees’ date of birth, which doesn’t comply with the rule of 18 years age of the employee.
  2. User can’t enter future date of death.

2.2 Create a trg_before_emp_insr trigger on table employee_details

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

trg_before_emp_insr_userinfo
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

  1. Syntax :- Oracle official docs
  2. PL/SQL Triggers :- Oracle official docs

上一篇: Oracle PL/SQL – Enable and Disable Triggers
下一篇: JaCoCo Java Code Coverage + Maven example
 评论 ( What Do You Think )
名称
邮箱
网址
评论
验证
   
 

 


  • 微信公众号

  • 我的微信

站点声明:

1、一号门博客CMS,由Python, MySQL, Nginx, Wsgi 强力驱动

2、部分文章来源于互联网, 若有侵权, 联系邮箱:summer@yihaomen.com, 同时欢迎大家注册用户,主动发布文章.

3、鄂ICP备14001754号-3, 鄂公网安备 42280202422812号