Oracle PL/SQL – UPDATEトリガの例
この記事では、「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 | ========== ==========================
参考文献
トリガ: – Oracleの公式ドキュメント]。リンク://oracle/oracle-plsql-before-update-trigge-example/[Oracle PL/SQL
-
UPDATEトリガの例前]