Oracle PL / SQL – INSERT触发器之前的示例

本文向您展示了如何使用BEFORE INSERT TRIGGER ，在执行INSERT操作之前就可以使用。 在现实生活中，它主要用于以下目的

1. 资料验证
2. 自动更新值（例如CREATED_BY，CREATION_DATE等）

1.桌子

创建一个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
);

输出量

table EMPLOYEE_DETAILS created.

2.数据验证

2.1在插入触发器示例之前限制无效数据输入：

1. 用户无法输入员工的生日，这不符合员工18岁的规定。
2. 用户无法输入未来的死亡日期。

2.2在表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正常数据。

-- 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测试触发器提升错误–员工年龄必须大于或等于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测试触发器引发错误–死亡日期不能为未来日期。

-- 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.更新值

插入前触发器示例自动更新某些值。

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;

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

参考文献

1. 语法：-Oracle官方文档
2. PL / SQL触发器：-Oracle官方文档

翻译自: https://mkyong.com/oracle/oracle-plsql-before-insert-trigger-example/