4月 | 2019 | DB & SQL 技術ブログ

ITコーディネータのシュウです。

桜の時期も終わってしまいました。ただ、今年は桜が咲いてから、少し寒かったので、桜がすぐには散らず、例年より長く、桜を楽しむことができたように思います。
東北や北海道などは、まだこれからがいよいよ本番ということなので、仕事で出張とかあればついでにお花見もできるのですが、そういう機会があるかな～。

＜本日の題材＞
データベーストリガー（Oracle）

データベーストリガーについて、まだブログで取り上げていなかったので、今回、取り上げてみたいと思います。
データベーストリガーとは、プロシージャやファンクションと同じように、データベースに格納されたPL/SQLのプログラムですが、プログラムなどから明示的にコールされて呼び出すのではなく、何らかのイベントをきっかけとして自動的に起動されるものです。

トリガーには、大きくは「DMLトリガー」「DDLトリガー」「その他」の3つの種類があります。
「DMLトリガー」は、特定の表に対してDML操作(INSERT, UPDATE, DELETEなど)が行われるときに起動するもの
「DDLトリガー」は、DDL操作(CREATE, ALTER, DROPなど)により起動するもの
「その他」については、
　・データベースにログインやログオフするとき
　・データベースの起動時や停止時
　・エラーの発生時
などで起動するものがあります。
一般的によく使用される「DMLトリガー」についてですが、文トリガー、行トリガーという種類があり、さらに、処理の前後のどちらで起動させるかという、「BEFOREトリガー」「AFTERトリガー」という分類があります。

例）
今回は、ユーザー管理用の「ACCOUNTS」テーブルというものがあったとして、それに対しての、INSERT, UPDATE, DELETE が行われるタイミング(AFTER)で、処理のログを「ACCOUNTS_LOG」テーブルに登録するトリガーを作成してみます。

まず、処理のログを登録する「ACCOUNTS_LOG」テーブルを作成します。

CREATE TABLE accounts_log(
log_date DATE
,old_account_id INT
,new_account_id INT
,action VARCHAR2(50));

次に、トリガーを作成しますが、「ACCOUNTS」テーブルに対しての、INSERT, UPDATE, DELETE が行われてデータが更新されるたびに、「ACCOUNTS_LOG」テーブルに、処理日、変更前後の「ACCOUNT_ID」、そして処理内容を記録させます。
今回は、行トリガーのAFTERトリガーとして作成します。

CREATE OR REPLACE TRIGGER accounts_change_trigger
AFTER INSERT OR UPDATE OR DELETE
ON accounts FOR EACH ROW
DECLARE
log_action accounts_log.action%TYPE;
BEGIN
IF INSERTING THEN
log_action := 'Insert';
ELSIF UPDATING THEN
log_action := 'Update';
ELSIF DELETING THEN
log_action := 'Delete';
ELSE
DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
END IF;

INSERT INTO accounts_log (log_date, old_account_id, new_account_id, action)
VALUES (SYSDATE, :OLD.account_id, :NEW.account_id, log_action);
END;
/

トリガーの中で、「FOR EACH ROW」を付けることで、行トリガーであることを示しています。そして、:OLD.列名、:NEW.列名とすることで、変更前と変更後の列名の値を取得することができます。
それでは、下記のように、「ACCOUNTS」テーブルへのデータの登録、修正、削除を順に行ってみます。

INSERT INTO accounts(account_id, name) VALUES(101, '山田　太郎');

INSERT INTO accounts(account_id, name) VALUES(102, '高橋　三郎');

UPDATE accounts SET NAME = '高橋　一郎' WHERE account_id = 102;

DELETE FROM accounts WHERE account_id = 101;

それでは、「ACCOUNTS_LOG」テーブルを確認してみます。

「LOG_DATE」項目の表示を時刻まで出すようにフォーマットを変更します。
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

データを確認します。
SELECT * FROM accounts_log
ORDER BY log_date;

たしかに、「ACCOUNTS_LOG」テーブルへの更新履歴が登録されています。
ここで、INSERTの場合は、変更前はデータがないので、old_accounti_id は NULL であり、DELETEの場合は、変更後はデータがなくなるので、new_accounti_id は NULLになることがわかります。

また、COMMITをする前にROLLBACKをしてみると「ACCOUNTS_LOG」のデータはどうなるでしょうか？

ROLLBACK;

再度、「ACCOUNTS_LOG」テーブルを確認してみます。

先ほど見えていたデータは、処理が取り消されたので、ログからも取り消されているのが確認できます。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

だいぶ前になってしまいますが、お正月に、田舎に帰省したときに、飛行機から富士山が綺麗に見えたため、撮った写真です。飛行機の搭乗員の方も、これほど富士山が綺麗に見えるのは珍しいと言われていましたので、とても得をした気分になりましたね。
今年は縁起がいいかも！
いろいろと仕事が忙しかったため、前回の投稿からかなり時間が空いてしまいましたが、今年も頑張っていければと思います。よろしくお願いいたします。

＜本日の題材＞
データベースリンク（Oracle）

システム開発の仕事をしていると、時折、複数のORACLEのサーバーがあり、それぞれに格納されているテーブルを同時にアクセスしてデータを抽出するなどの処理を行いたいという依頼を受けることがあります。
こういうときにORACLEデータベース側で、他のデータベースにアクセスできるデータベースリンクというものを作成することで、そういった操作が可能になります。
今回は、それを試してみたいと思います。

例）
今回は、Oracle12cのデータベースから、Oracle11gR2 のデータベースへのデータベースリンクを作成して、Oracle12c側からOracl11gR2側のデータを抽出できるようにしたいと思います。

設定の内容としては、大きくは以下になります。
① ORACLEデータベースサーバー同士で接続できるように、
　　ネットサービス名を設定します。
　　今回は、Oracle12c側で設定を行います。
② データベースリンクを作成するための権限を、該当のユーザ
　　ーに与えます。
③　該当のユーザーでデータベースリンクを作成します。

それでは始めて行きます。
①ネットサービス名の設定は、ツールでも可能ですが、今回はOracle12c側の「tnsnames.ora」ファイルを直接編集することで行います。

追加するネットサービス名を「ORCL11G」として、以下の内容を追加します。

ORCL11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = サービス名)
)
)

※「HOST=」の後には、Oracl11gR2サーバーのIPアドレス(ホスト名でもよい)を設定し、「PORT=」の後にはデフォルトのポート番号を記載しています。最後の「SERVICE_NAME =」の後には、Oracle11gR2データベースのサービス名を設定します。

② データベースリンクを作成するための権限を、該当のユーザーに与えます。以前、「Oracle12cでのユーザー作成でエラー「ORA-65096」」という内容でブログで取り上げたときの内容を使って、Oracle12cの「BLOG_TEST」というユーザーに権限を与えます。

Sqlplusでsysdba でログインした後、「PDBORCL」というプラガブルデータベースに接続します。

ALTER SESSION SET CONTAINER = PDBORCL;