Oracle PL/SQL – UPDATEトリガの例
この記事では、 `BEFORE UPDATE TRIGGER`の使い方を示します。更新操作が実行される前に起動しています。現実のシナリオでは、主に次のような目的で使用されます。
-
データ検証
-
値を自動的に更新する
-
データロギングまたは監査
1.データ検証
求人があり、既にアプリケーションデータを持っている企業があり、その基準は次のとおりです。
-
職歴は3年以上でなければならず、
-
過去2年間は、前回の申請をしないでください.
データの完全性を保証するために、我々は `BEFORE UPDATE`トリガを作成し、上記のいずれかの条件に違反するデータに対してUPDATEを制限します。
1.1まず、job__openingsテーブルを作成します。
job__openings
--Creating job__openings table. CREATE TABLE job__openings ( APPLICATION__ID番号(10)主キー、 FIRST__NAME varchar2(50)、 LAST__NAME varchar2(50)、 JOB__EXPERIENCE number(2)、 LAST__APPLIED__DATE日 );
1.2それから、私たちは列の前のアップデートのtiggerを作成します
job
openingsテーブルの
JOB
EXPERIENCE`と LAST
APPLIED
DATE ‘
trg
before
emp__update
-- Creating TRIGGER CREATE OR REPLACE TRIGGER trg__before__emp__update BEFORE UPDATE OF JOB__EXPERIENCE,LAST__APPLIED__DATE on job__openings FOR EACH ROW DECLARE years__since__last__applied number(5); BEGIN years__since__last__applied := -1; IF(:NEW.LAST__APPLIED__DATE IS NOT NULL) THEN SELECT MONTHS__BETWEEN(TO__DATE(sysdate,'DD-MON-YYYY'), TO__DATE(:NEW.LAST__APPLIED__DATE,'DD-MON-YYYY'))/12 INTO years__since__last__applied FROM dual; -- Check whether years__since__last__applied is greater than 2 years or not IF (years__since__last__applied <= 2) THEN RAISE__APPLICATION__ERROR(-20000,'Previous application attempt must not be done in last 2 years.'); END IF; END IF; -- Job experience must be more than or equal to 3 years. IF(:new.JOB__EXPERIENCE < 3) THEN RAISE__APPLICATION__ERROR(-20000,'Job experience must be more than or equal to 3 years.'); END IF; END;
1.3正常データ。
-- setting date format to to 'DD-MON-YYYY' セッションセットを変更するnls__date__format = 'DD-MON-YYYY'; INSERT INTO job__openings VALUES(1、 'Mark'、 'Sharma'、10、'01-JAN-2012 '); INSERT INTO job__openings VALUES(2、 'Praveen'、 'Kumar'、4、'01 -DEC-2010 '); INSERT INTO job__openings VALUES(3、 'Rahul'、 'Kohli'、6、null); -- output 1 rows inserted. 1 rows inserted. 1 rows inserted.
select ** from job__openings;
| =========================================== ======================= | APPLICATION
ID | FIRST
NAME | LAST
NAME | JOB
EXPERIENCE | LAST
APPLIED
DATE
| 1 |マーク| Sharma | 10 | 01-JAN-2012
| 2 | Praveen |クマール| 4 | 2010年12月1日
| 3 | Rahul | Kohli | 6 | null | ================================= ==================
1.4テスト・トリガー・エラー – 職務経験は3年以上でなければなりません。
-- Try to update job__experience less than 3 years UPDATE job__openings SET JOB__EXPERIENCE = 2ここで、APPLICATION__ID = 1; -- error Error report - ORA-20000: Job experience must be more than or equal to 3 years. ORA-06512: at "SYSTEM.TRG__BEFORE__EMP__UPDATE", line 21 ORA-04088: error during execution of trigger 'SYSTEM.TRG__BEFORE__EMP__UPDATE'
1.5テスト・トリガ・エラー – 過去2年間は、以前のアプリケーションの試行を行わないでください。
-- Try to update last__Applied__Date which is less than 2 years UPDATE job__openings SET LAST__APPLIED__DATE = '10 -JUN-2016 'ここでAPPLICATION__ID = 3; -- error Error report - ORA-20000: Previous application attempt must not be done in last 2 years. ORA-06512: at "SYSTEM.TRG__BEFORE__EMP__UPDATE", line 15 ORA-04088: error during execution of trigger 'SYSTEM.TRG__BEFORE__EMP__UPDATE'
2.値を更新する
:NEW.UPDATED
BY`と
:NEW.UPDATEDDATE`を自動的に更新する `BEFORE UPDATE`トリガーの例です。
2.1テーブルを作成します。
person__records
--Creating person__records table. CREATE TABLE person__records ( PERSON__ID番号(10)主キー、 FIRST__NAME varchar2(50)、 LAST__NAME varchar2(50)、 HIRE__DATE日付、 UPDATED__BY varchar2(20)、 UPDATED__DATEの日付 );
2.2テーブル `person__records`の更新前トリガを作成します。
trg
before
person__update
トリガーの作成または置換trg__before__person__update各行ごとのperson__recordsのBEFORE UPDATE DECLAREユーザー名varchar2(20); ベギン ユーザー名をFROM dualから選択します。 - updated__byとupdated__Dateの値を設定する。 :NEW.UPDATED__BY := username; :NEW.UPDATED__DATE := sysdate; 終わり;
2.3 2つのレコードを挿入します。
-- setting date format to to 'DD-MON-YYYY' alter session set nls__date__format = 'DD-MON-YYYY'; INSERT INTO person__records VALUES (101,'Devil','Khedut',sysdate,null,null); INSERT INTO person__records VALUES (102,'Kanji','Yadav',sysdate,null,null); -- output 1行挿入されました。 1行挿入されました。
選択** from person__records;
[cols = “,,,,,”]| =========================================== ==================
| PERSON
ID | FIRST
NAME | LAST
NAME | HIRE
DATE | UPDATED
BY | UPDATED
DATE
| 101 |悪魔|ケダルート| 06-JUN-2017 | null | null
| 102 |漢字|ヤダブ| 06-JUN-2017 | null | null
| =========================================== ==================
2.4レコードを更新し、更新前のトリガを起動します。ザ
UPDATED
BY`と
UPDATEDDATE`の値は自動的に更新されます。
UPDATE person__records SET first__name = 'Lavji' WHERE person__id = 101; UPDATE person__records SET first__name = 'ジョギ' WHERE person__id = 102; -- output 1 rows updated. 1 rows updated.
select ** from person__records;
| =========================================== ==================== | PERSON
ID | FIRST
NAME | LAST
NAME | HIRE
DATE | UPDATED
BY | UPDATED
DATE | 101 | Lavji | Khedut | 06-JUN-2017 | HR | 06-JUN -2017 | 102 |ジョギ|ヤダブ| 06-JUN-2017 |人事| 06-JUN-2017 | ========================= ================================
3.ロギング/監査データ
この例では、トランザクションテーブルを更新するたびに監査テーブルに行を挿入するトリガを作成しています。
bank
transactions`のユーザUPDATEデータのたびに、監査やバックアップのために古いデータが
banktransactions__audit`にトリガによって挿入されます。
3.1銀行取引テーブルを作成します。
bank__transactions
--Creating bank__transactions table. CREATE TABLE bank__transactions ( TXN__ID番号(10)主キー、 TXN__NUMBER varchar2(20)、 PARTY__NAME varchar2(50)、 TXN__DATEの日付、 CREATED__BY varchar2(20)、 CREATED__DATE日 );
3.2別の銀行取引監査テーブルを作成します。
bank
transactions
audit
--Creating bank__transactions__audit table. CREATE TABLE bank__transactions__audit ( TXN__ID number(10), TXN__NUMBER varchar2(20), PARTY__NAME varchar2(50), TXN__DATE date, CREATED__BY varchar2(20), CREATED__DATE date );
3.3 `bank__transactions`テーブルのupdate beforeトリガを作成します。
trg
before
update
txn
audit
--Creating Trigger triggerの作成または置換trg__before__update__txn__audit各行のbank__transactionsへの更新前の更新 ベギン -- Insert OLD values in audit table for logging purpose INSERT INTO bank__transactions__audit VALUES(:OLD.TXN__ID,:OLD.TXN__NUMBER, :OLD.PARTY__NAME,:OLD.TXN__DATE,:OLD.CREATED__BY,:OLD.CREATED__DATE); END;
3.4 Insert some records.
--Inserting values INSERT INTO BANK__TRANSACTIONS values ('1','TXN1234','Peter Thomas','12-MAY-2017','HR',sysdate); INSERT INTO BANK__TRANSACTIONS values ('2','TXN9999','Jemes Patel','10-JUN-2016','HR',sysdate); select ** from BANK__TRANSACTIONS;
| =========================================== ================= | TXN
ID | TXN
NUMBER | PARTY
NAME | TXN
DATE | CREATED
BY | CREATED
DATE | 1 | TXN1234 |ピーター・トーマス| 2011年5月12日| HR | 06-JUN-2017 | 2 | TXN9999 | Jemes Patel | 10-JUN-2016 | HR | 06-JUN-2017 | ======================== ========================
3.5挿入操作で更新前のトリガが起動されませんでした。
select ** from BANK__TRANSACTIONS__AUDIT; -- output 選択された行はありません。
3.6 Update records, it will fires the “before update” trigger and insert
the old data into another audit table.
--updating values. UPDATE bank__transactions SET txn__number = 'NEWTXN8080' WHERE txn__id = 1; UPDATE bank__transactions SET txn__number = 'NEWTXN9595' WHERE txn__id = 2; -- output 1行が更新されました。 1行が更新されました。
BANK__TRANSACTIONSから** を選択します。
[cols = “,,,,,”]| =========================================== =================
| TXN
ID | TXN
NUMBER | PARTY
NAME | TXN
DATE | CREATED
BY | CREATED
DATE
| 1 | NEWTXN8080 |ピーター・トーマス| 12-MAY-2017 | HR | 06-JUN-2017
| 2 | NEWTXN9595 | Jemes Patel | 10-JUN-2016 | HR | 06-JUN-2017
| =========================================== =================
BANK__TRANSACTIONS__AUDITから** を選択します。
[cols = “,,,,,”]| =========================================== =================
| TXN
ID | TXN
NUMBER | PARTY
NAME | TXN
DATE | CREATED
BY | CREATED
DATE
| 1 | TXN1234 |ピーター・トーマス| 12-MAY-2017 | HR | 06-JUN-2017
| 2 | TXN9999 | Jemes Patel | 10-JUN-2016 | HR | 06-JUN-2017
| =========================================== =================
===参考文献
。
https://docs/
– CREATE TRIGGERステートメント]。
https://docs/
– PL/SQLトリガー]
before update
リンク://tag/oracle/[oracle]
plsql
trigger