Oracle PL/SQL – UPDATEトリガの例

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

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

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

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

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

--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の日付
）;

トリガーの作成または置換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;

終わり;

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

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;

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

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

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

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

select **  from BANK__TRANSACTIONS__AUDIT;

-- output

選択された行はありません。

--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から** を選択します。

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