Oracle PL/SQL – INSERT前のトリガーの例

投稿日: 2019-11-05 2019-11-05
タグ: before insert, oracle, plsql, trigger

この記事では、 `BEFORE INSERT TRIGGER`を使用する方法を示します。INSERT操作が実行される前に起動します。実際の生活のシナリオでは、ほとんどの目的のために使用されます

データ検証
値を自動的に更新する（例:CREATED

BY、CREATION

DATEなど）

1.テーブル

`employee__details`を作成し、このテーブルに異なる値を挿入し、トリガーの動作を観察します。

employee__details

--Creating employee__details table.

CREATE TABLE employee__details
（
    EMP__ID番号（10）主キー、
    FIRST__NAME varchar2（50）、
    LAST__NAME varchar2（50）、
    DATE__OF__BIRTH日、
    DATE__OF__DEATH日付、
    CREATED__BY varchar2（20）、
    CREATED__DATE日
）;

出力

テーブルEMPLOYEE__DETAILSが作成されました。

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
employeedetails

trg

before

emp__insr

トリガーの作成または置換trg__before__emp__insr BEFORE INSERT on employee__details各行用

DECLARE emp__age number;

ベギン

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

セッションセットを変更する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'

セッションセットを変更する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'

セッションセットを変更する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.値を更新する

挿入前のトリガの例は、いくつかの値を自動的に更新します。

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'

セッションセットを変更するnls__date__format = 'DD-MON-YYYY';

select **  from employee__details;

| 1 |パテール|トーマス| 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;

| 1 |パテ|トーマス| 18-May-1999 | 01年5月-2017 | HR | 24-MAY-2017

| 2 |パテール|メシュー| 01-JAN-1990 | 01-May-2005 | HR | 24-MAY-2017

| 3 |パテ|メシュー| 01-JAN-1990 | 2005年5月1日| HR | 24-MAY-2017 | ======================= ========================================

参考文献

https://docs.oracle.com/cd/B19306

01/server.102/b14200/statements

7004.htm[Syntax

:- Oracle official docs].

https://docs.oracle.com/cloud/latest/db112/LNPLS/triggers.htm#LNPLS020

[PL/SQL

トリガ: – Oracleの公式ドキュメント]

insert before

リンク://tag/oracle/[oracle]

plsql

trigger

getdocs

13036