Oracle PL/SQL – UPDATEトリガの例

投稿日: 2019-11-05 2019-11-05
タグ: after update, oracle, plsql, trigger

この記事では、「AFTER UPDATE TRIGGER」の使い方を示します。更新操作が実行された後に起動します。

1.ロギングの例

この例では、
employee salary`の 'SALARY’列に対する各更新の後に、 'after update’トリガを起動し、新しい更新データを監査目的のために
employeesalary__log`テーブルに挿入します。

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

employee__salary

CREATE TABLE employee__salary
(
    EMP__ID number(10),
    SALARY number(10),
    EMP__NAME varchar2(50)
);

employee

salary

log

CREATE TABLE employee__salary__log
(
    EMP__ID number(10),
    NEW__SALARY number(10),
    UPDATED__DATE date,
    UPDATED__BY varchar2(20)
);

trg

log

employee__salary

CREATE OR REPLACE TRIGGER trg__log__employee__salary
AFTER UPDATE
  OF SALARY
    ON employee__salary
FOR EACH ROW

DECLARE
username varchar2(20);

BEGIN

    -- get current login user
    SELECT USER INTO username FROM dual;

    -- Insert new values into log table.
    INSERT INTO employee__salary__log VALUES (
        :NEW.EMP__ID, :NEW.SALARY, sysdate, username);
END;

1.2データを挿入してトリガーをテストします。

INSERT INTO employee__salary VALUES (101,15000,'Pranav');

INSERT INTO employee__salary VALUES (201,40000,'Vikram');

INSERT INTO employee__salary VALUES (301,35000,'Nikhil');

-- fire trigger, insert into log table

UPDATE employee__salary SET SALARY = '28000' WHERE emp__id = 101;

-- fire trigger, insert into log table
UPDATE employee__salary SET SALARY = '43000' WHERE emp__id = 301;

1.3データを表示します。

select **  from EMPLOYEE__SALARY;

| ========================== | EMP

ID | SALARY | EMP

NAME | 101 | 28000 | Pranav | 201 | 40000 | Vikram | 301 | 43000 | Nikhil | ============

select **  from EMPLOYEE__SALARY__LOG;

| ========================== | EMP

ID | NEW

SALARY | UPDATED

DATE | UPDATED

BY | 101 | 28000 | 10-JUN-17 | SYSTEM | 301 | 43000 | 10-JUN-17 | SYSTEM | ==================== ==========

2. WHEN条件によるロギング

この例では、 ‘employee

salary`の’ SALARY ‘列が50000より大きいハイキングで更新されると、’ after update ‘トリガーが発生し、新しい更新データが `employee

salary__log`テーブルに挿入されます。

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

employee__salary

CREATE TABLE employee__salary
(
    EMP__ID number(10),
    SALARY number(10),
    EMP__NAME varchar2(50)
);

employee

salary

hike__log

CREATE TABLE employee__salary__hike__log
(
    EMP__ID number(10),
    NEW__SALARY number(10),
    HIKE number(10),
    UPDATED__DATE date,
    UPDATED__BY varchar2(20)
);

trg

log

salary__hike

CREATE OR REPLACE TRIGGER trg__log__salary__hike
AFTER UPDATE
  OF SALARY
    ON employee__salary
FOR EACH ROW
WHEN ((NEW.SALARY - OLD.SALARY) > 50000)

DECLARE
username varchar2(20);

BEGIN

    SELECT USER INTO username FROM dual;

    -- Insert new values into log table.
    INSERT INTO employee__salary__hike__log VALUES (
        :NEW.EMP__ID, :NEW.SALARY, :NEW.SALARY - :OLD.SALARY ,sysdate, username);
END;

2.2データを挿入してトリガーをテストします。

INSERT INTO employee__salary VALUES (101,15000,'Pranav');

INSERT INTO employee__salary VALUES (201,40000,'Vikram');

INSERT INTO employee__salary VALUES (301,35000,'Nikhil');

-- new salary - old salary > 50000, fire after update trigger, insert into log

UPDATE employee__salary SET SALARY = '70000' WHERE emp__id = 101;

UPDATE employee__salary SET SALARY = '100000' WHERE emp__id = 301;

-- new salary - old salary < 50000, no action.
UPDATE employee__salary SET SALARY = '45000' WHERE emp__id = 201;

2.3データを表示する。

select **  from EMPLOYEE__SALARY;

| ========================== | EMP

ID | SALARY | EMP

NAME | 101 | 70000 | Pranav | 201 | 45000 | Vikram | 301 | 100000 | Nikhil | ============

select **  from EMPLOYEE__SALARY__HIKE__LOG;

| =========================================== = | EMP

ID | NEW

SALARY | HIKE | UPDATED

DATE | UPDATED

BY | 101 | 70000 | 55000 | 10-JUN-17 | HR | 301 | 100000 | 65000 | 10-JUN-17 | HR | ========== ==========================

参考文献

https://docs/

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

UPDATEトリガの例前]

更新後


oracle

plsql

trigger

getdocs

13036