Oracle PL/SQL – DELETEトリガの例

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

この記事では、 `BEFORE DELETE TRIGGER`の使い方を示しています。削除操作が実行される前に起動します。現実のシナリオでは、主に次のような目的で使用されます。

無効なDELETE操作を制限します.
別のテーブルからデータを削除する.

1.無効なDELETE操作を制限する

この例では、
item details`と
orderdetails`という2つのテーブルがあります。

order details`テーブルは、
itemdetails`テーブルからのアイテムの購買オーダーの値を含む。今、ユーザーがitem__detailsから項目を削除したいときは、その項目のPENDING注文が存在するかどうかを確認する必要があります。

保留中の注文が見つかった場合、アイテムを削除することは許可されず、
item__details`の削除操作を制限するためにアプリケーションエラーを
BEFORE DELETE TRIGGER`から呼び出すでしょう

1.1テーブルとトリガーを作成します。

ITEM__DETAILS

CREATE TABLE ITEM__DETAILS
(
    ITEM__ID number(10) primary key,
    ITEM__NAME varchar2(30),
    TYPE varchar2(50),
    PRICE__IN__DOLLAR number(10)
);

注文詳細

CREATE TABLE ORDER__DETAILS
(
    ORDER__ID number(10) primary key,
    ITEM__ID number(10),
    QUANTITY number(5),
    ORDER__DATE date,
    STATUS varchar2(20)
);

trg

before

item__delete

CREATE OR REPLACE TRIGGER trg__before__item__delete
BEFORE DELETE
  on item__details
  FOR EACH ROW

DECLARE
pending__orders number;

BEGIN
pending__orders := 0;
-- Find pending orders

SELECT count（1）INTO pending__orders FROM order__Details WHERE item__id =:OLD.item__id AND STATUS = 'PENDING';

-- Check whether any pending order exists or not
    IF (pending__orders > 0) THEN
      RAISE__APPLICATION__ERROR(-20000,pending__orders||
        ' pending orders found for this item. First COMPLETE or CANCEL the order and then delete.');
    END IF;

END;

1.2 Insert data for testing.

ITEM__DETAILS VALUES（1、 'Fidget Spinner'、 'TOYS'、10）に挿入します。 ITEM__DETAILS VALUES（2、 'Radio'、 'ELECTRONICS'、15）に挿入します。 ITEM__DETAILS VALUES（3、 'Toys Car'、 'TOYS'、25）に挿入します。 ITEM__DETAILS VALUES（4、 'Mobile'、 'ELECTRONICS'、150）に挿入します。

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

ORDER__DETAILS VALUES（101,1,5、'10 -JUN-2017 '、' COMPLETED '）に挿入します。
ORDER__DETAILS VALUES（102,2,2、'15-JUN-2017 '、' CANCELLED '）に挿入します。
ORDER__DETAILS VALUES（103,4,1、'17 -JUN-2017 '、' PENDING '）に挿入します。
ORDER__DETAILS VALUES（104,4,1、'01-JUN-2017 '、' COMPLETED '）に挿入します。

1.3データを表示します。

ITEM__DETAILSから** を選択します。

[cols = “,,,”、options = “header”、]| =======================================================================
| ITEM

ID | ITEM

NAME | TYPE | PRICE

IN

DOLLAR
| 1 | Fidget Spinner | TOYS | 10
| 2 |ラジオ|エレクトロニクス| 15
| 3 |おもちゃ車| TOYS | 25
| 4 |モバイル|エレクトロニクス| 150
| =======================================================================

ORDER__DETAILSから** を選択します。

[cols = “,,,,”、options = “header”、]| =========================================
| ORDER

ID | ITEM

ID | QUANTITY | ORDER__DATE | STATUS
| 101 | 1 | 5 | 10-JUN-2017 |完了
| 102 | 2 | 2 | 15-JUN-2017 | CANCELED
| 103 | 4 | 1 | 17-JUN-2017 |ペンディング
| 104 | 4 | 1 | 01-JUN-2017 |完了しました
| =========================================

1.4 PENDING注文を持つアイテムを削除します。

DELETE FROM item__detailsどこitem__id = 4;

-- output
Error report -
ORA-20000: 1 pending orders found for this item. First COMPLETE or CANCEL the order and then delete.
ORA-06512: at "SYSTEM.TRG__BEFORE__ITEM__DELETE", line 11
ORA-04088: error during execution of trigger 'SYSTEM.TRG__BEFORE__ITEM__DELETE'

2.別のテーブルから削除する

この例では、2つのテーブル
patient`と
patient

details`があります。
patient`には基本的な詳細が含まれ、
patient

details`には病気、医師の名前などの患者の値が含まれます。

ユーザーが
patient`からデータを削除したいときはいつでも、
patient__details`からデータを削除する必要があります。私たちは、患者が削除された後もそれを必要としないためです。そこでここでは、患者テーブル上の `BEFORE DELETE TRIGGER`でデータを削除します。

2.1テーブルとトリガを作成します。

患者

CREATE TABLE PATIENT
(
    PATIENT__ID number(10) primary key,
    NAME varchar2(30),
    PHONE__NO number(12)
);

PATIENT__DETAILS

CREATE TABLE PATIENT__DETAILS
(
    PD__ID number(10) primary key,
    PATIENT__ID number(10),
    DISEASE varchar2(50),
    ADMITTED__DATE date,
    DOCTOR varchar2(30)
);

trg

delete

from__details

CREATE OR REPLACE TRIGGER trg__delete__from__details
BEFORE DELETE
  on patient
  FOR EACH ROW

BEGIN

-- Delete from PATIENT__DETAILS also

PATIENT__DETAILS PDから削除するPD.PATIENT__ID =:OLD.PATIENT__ID;
    
終わり;

2.2テスト用のデータを挿入する。

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

患者の値に挿入する（1、 'Devil Lal'、9898989898）;患者の値に挿入する（2、 'Martin Kiyosaki'、9090909090）;

（101,1、 'FEVER'、'10-JUN-2016 '、' Dr。RJ Sharma '）に挿入します。
（102,1、 'COLD'、 '01-DEC-2016'、 'Dr。RJ Sharma'）に挿入します。
患者の誕生日の価値を挿入する（103,2、 '関節炎'、 '01-DEC-2015'、 'Dr. KD Verma'）;
（104,2、 'BACKPAIN'、'12-FEB-2017 '、' Dr. KD Verma '）;

2.3データを表示する。

患者を選択** する。

[cols = “,,”、options = “header”、]| ========================
| PATIENT

ID | NAME | PHONE

NO
| 1 |デビル・ラル| 9898989898
| 2 |マーチン・キヨサキ| 9090909090
| ========================

PATIENT__DETAILSから** を選択します。

[cols = “,,,,”、options = “header”、]| ===========================================
| PD

ID | PATIENT

ID |病気| ADMITTED__DATE | DOCTOR
| 101 | 1 | FEVER | 10-JUN-2016 | Dr。 RJシャルマ
| 102 | 1 |冷たい| 01-Dec-2016 | Dr。 RJシャルマ
| 103 | 2 |関節炎| 01-Dec-2015 | Dr。 KD Verma
| 104 | 2 | BACKPAIN | 12-FEB-2017 | Dr。 KD Verma
| ===========================================

2.4 `patient`テーブルからアイテムを削除する。 `patient__detail`を見直してください
関連するデータはトリガーによって自動的に削除されます。

患者のどこから削除するかpatient__id = 2;

-- output
-- 1 row deleted.

患者を選択** する。

[cols = “,,”、options = “header”、]| ====================
| PATIENT

ID | NAME | PHONE

NO
| 1 |デビル・ラル| 9898989898
| ====================

PATIENT__DETAILSから** を選択します。

===参考文献

。

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

01/server.102/b14200/statements

7004.htm#i2153487[Creating
DMLトリガー:例]。

https://docs/トリガ

: – Oracleの公式ドキュメント]。リンク://oracle/oracle-plsql-before-update-trigge-example/[Oracle PL/SQL
– UPDATEトリガの例前]
リンク://タグ前/削除前/[削除前]リンク://タグ/oracle/[oracle]

plsql

trigger

getdocs

13036