14.3. トリガーを用いた例ここまで、トリガーの基本的な使い方について説明してきました。ここでは、トリガーの利用例を具体的に説明します。 ここで取り上げる例について説明します。次のような「顧客表」と「更新記録表」を処理対象のテーブルとします。顧客表にデータを記録するたびに、自動的に更新記録表にもデータが追加されるというものです。
ORACLE の場合は次のように記述します。 <<ORACLE>>
/* トリガーの定義 */
CREATE TRIGGER RECORD_LOG_TRIG
AFTER INSERT OR UPDATE OR DELETE
ON CUSTOMER
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO RECORD_LOG(
update_time, method,
new_c_num, new_name, new_addr)
VALUES(SYSDATE,'INSERT',
:NEW.c_num, :NEW.name, :NEW.addr) ;
ELSIF UPDATING THEN
INSERT INTO RECORD_LOG(
update_time, method,
old_c_num, old_name, old_addr,
new_c_num, new_name, new_addr)
VALUES(SYSDATE,'UPDATE',
:OLD.c_num, :OLD.name, :OLD.addr,
:NEW.c_num, :NEW.name, :NEW.addr) ;
ELSIF DELETING THEN
INSERT INTO RECORD_LOG(
update_time, method,
old_c_num, old_name, old_addr)
VALUES(SYSDATE,'DELETE',
:OLD.c_num, :OLD.name, :OLD.addr) ;
END IF ;
END ;
/* CUSTOMER テーブルへのデータ操作 */
INSERT INTO CUSTOMER
VALUES(1001, 'NONAKA', 'KYOTO') ;
INSERT INTO CUSTOMER
VALUES(1002, 'MORI', 'ISHIKAWA') ;
INSERT INTO CUSTOMER
VALUES(1003, 'KAMEI', 'HIROSHIMA') ;
INSERT INTO CUSTOMER
VALUES(1004, 'KOIZUMI', 'TOKYO') ;
DELETE FROM CUSTOMER WHERE name = 'MORI' ;
UPDATE CUSTOMER SET addr = 'KANAGAWA'
WHERE name = 'KOIZUMI' ;
/* RECORD_LOG の確認 */
SELECT * FROM RECORD_LOG ;
update_time method old_c_num old_name old_addr new_c_num new_name new_addr
────── ─── ───── ───── ──── ───── ───── ────
2001-04-01 INSERT NULL NULL NULL 1001 NONAKA KYOTO
2001-04-01 INSERT NULL NULL NULL 1002 MORI ISHIKAWA
2001-04-01 INSERT NULL NULL NULL 1003 KAMEI HIROSHIMA
2001-04-01 INSERT NULL NULL NULL 1004 KOIZUMI TOKYO
2001-04-01 DELETE 1002 MORI ISHIKAWA NULL NULL NULL
2001-04-01 UPDATE 1004 KOIZUMI TOKYO NULL NULL KANAGAWA
この記述に対して少し説明します。トリガーの定義の中にある INSERTING、UPDATING 及び DELETING は、トリガーがどのデータ操作文により起動したのかを示すものです。たとえば、INSERT 文で呼び出されたトリガーが起動された場合は INSERTING は TRUE を返します。そうでない場合は FALSE を返します。 :NEW.列名と :OLD.列名は、データ操作が行われる前の値と、その後の値を示しています。INSERT 文が行われた場合、もともとデータはなかったわけですから、:OLD.列名の値は NULL になります。また、:NEW.列名には新しく挿入されたデータの値が入ります。UPDATE 文では、変更される前の値は :OLD.列名、変更後の値は :NEW.列名に格納されます。DELETE 文では新しいデータはありませんので、:NEW.列名は NULL になり、DELETE される前のデータは :OLD.列名に格納されます。 次に PostgreSQL で同じことをする場合の例です。 <<PostgreSQL>>
/* 関数の定義 */
CREATE FUNCTION UPDATE_FUNC() RETURNS OPAQUE
AS '
BEGIN
IF TG_OP = ''INSERT'' THEN
INSERT INTO RECORD_LOG(
update_time, method,
new_c_num, new_name, new_addr)
VALUES(''now'',''INSERT'',
new.c_num, new.name, new.addr) ;
RETURN new ;
ELSE
IF TG_OP = ''UPDATE'' THEN
INSERT INTO RECORD_LOG(
update_time, method,
old_c_num, old_name, old_addr,
new_c_num, new_name, new_addr)
VALUES(''now'',''UPDATE'',
old.c_num, old.name, old.addr,
new.c_num, new.name, new.addr) ;
RETURN new ;
ELSE
INSERT INTO RECORD_LOG(
update_time, method,
old_c_num, old_name, old_addr)
VALUES(''now'',''DELETE'',
old.c_num, old.name, old.addr) ;
RETURN old ;
END IF ;
END IF ;
END ;
'
LANGUAGE 'plpgsql' ;
/*トリガーの定義 */
CREATE TRIGGER RECORD_LOG_TRIG
AFTER INSERT OR UPDATE OR DELETE
ON CUSTOMER
FOR EACH ROW
EXECUTE PROCEDURE UPDATE_FUNC() ;
/* CUSTOMER テーブルへのデータ操作 */
INSERT INTO CUSTOMER
VALUES(1001,'NONAKA','KYOTO') ;
INSERT INTO CUSTOMER
VALUES(1002,'MORI','ISHIKAWA') ;
INSERT INTO CUSTOMER
VALUES(1003,'KAMEI','HIROSHIMA') ;
INSERT INTO CUSTOMER
VALUES(1004,'KOIZUMI','TOKYO') ;
DELETE FROM CUSTOMER WHERE name = 'MORI' ;
UPDATE CUSTOMER SET addr = 'KANAGAWA'
WHERE name = 'KOIZUMI' ;
/* RECORD_LOG の確認 */
SELECT * FROM RECORD_LOG ;
update_time method old_c_num old_name old_addr new_c_num new_name new_addr
────── ─── ───── ───── ──── ───── ───── ────
2001-04-01 INSERT NULL NULL NULL 1001 NONAKA KYOTO
2001-04-01 INSERT NULL NULL NULL 1002 MORI ISHIKAWA
2001-04-01 INSERT NULL NULL NULL 1003 KAMEI HIROSHIMA
2001-04-01 INSERT NULL NULL NULL 1004 KOIZUMI TOKYO
2001-04-01 DELETE 1002 MORI ISHIKAWA NULL NULL NULL
2001-04-01 UPDATE 1004 KOIZUMI TOKYO 1004 KOIZUMI KANAGAWA
PostgreSQL の場合は、予め関数を定義しておかなければなりません。関数中の TG_OP という変数は、特別なシステム変数で、テーブルに対して行われたデータ操作が何であるのかが格納されます。INSERT 文が実行された場合は 'INSERT'、UPDATE 文の場合は 'UPDATE'、DELETE 文の場合は 'DELETE' が格納されます。 また、ORACLE における :OLD.列名と:NEW.列名は、PostgreSQL では old.列名及び new.列名という風に記述します。 実習課題 1以下の要求を満たすトリガーを定義しなさい。
実習課題 2以下の要求を満たすトリガーを定義しなさい。
|
|
