トリガーの検証

トリガーについての検証です。

ここでは、以下の言葉を、以下の意味で使用します。

・元テーブル(test)：トリガーとなる動作を受けるテーブル
・ダミーテーブル(dummy_test)：トリガー自体の動作を受けるテーブル

元テーブルにデータを入れようとすると、ダミーテーブルにデータを入れるトリガーを使って
動作検証しました。

■正常系
testテーブルにデータを入れたら、dummy_testにもデータが入るトリガーを作成。

・元テーブル作成
SQL> create table test(a number);

表が作成されました。

・ダミーテーブル作成
SQL> create table dummy_test(a number);

表が作成されました。

・ダミーテーブルにデータを入れるトリガー作成
SQL> create or replace trigger t_ins_dummy_test
  2  before insert on test
  3  for each row
  4  begin
  5  insert into dummy_test values(:new.a);
  6  end;
  7  /

トリガーが作成されました。

・トリガー実行前
SQL> select * from test;

レコードが選択されませんでした。

SQL> select * from dummy_test;

レコードが選択されませんでした。

・トリガー実行。
testテーブルにデータが入れる前に、ダミーテーブルにデータが入る
SQL> insert into test values(1);

1行が作成されました。

・トリガー実行後
SQL> select * from test;

         A
----------
         1

SQL> select * from dummy_test;

         A
----------
         1


■異常系
・トリガー実行前
SQL>  select * from dummy_test;
 select * from dummy_test

・わざとダミーテーブルを削除
SQL> drop table dummy_test purge;

表が削除されました。

・ダミーテーブルが無いので、トリガー失敗
SQL> insert into test values(1);
insert into test values(1)
            *
行1でエラーが発生しました。:
ORA-04098: トリガー'SYSTEM.T_INS_DUMMY_TEST'が無効で再検証に失敗しました

・トリガー実行後
SQL>  select * from dummy_test;
 select * from dummy_test

トリガーを使う上で注意しないといけないのが、
元テーブルと、ダミーテーブルにデータ入れる動作が、1トランザクションであるということです。
要するに、トリガーとなる動作と、トリガー自体の動作は1トランザクションであり、どちらかが
失敗するとトランザクションの処理自体がロールバックされます。
この場合は、ダミーテーブルを削除することで、わざとエラーを発生させましたが、それ以外の
エラーでダミーテーブルへの処理が失敗したとしても同じように、トランザクション自体が失敗します。

ということは、トリガーの仕組みを入れることで弱点を一つ作ってしまうことにもなるのです。
トリガー自体は便利な機能ですが、そういった仕組みを把握しておかないと、思わぬところでエラーを引き起こしてしまうことになるのです。


■トリガーが無い状態で元テーブルにデータを入れると？
・トリガー実行前
SQL> select * from test;

レコードが選択されませんでした。

SQL> select * from dummy_test;

レコードが選択されませんでした。

・トリガー無い状態で、元テーブルにデータを入れる
SQL> insert into test values(1);

1行が作成されました。

・トランザクション中にトリガー作成
SQL> create or replace trigger t_ins_dummy_test
  2  before insert on test
  3  for each row
  4  begin
  5  insert into dummy_test values(:new.a);
  6  end;
  7  /

トリガーが作成されました。

・トリガー実行後
SQL> select * from test;

         A
----------
         1

SQL> select * from dummy_test;

レコードが選択されませんでした。


元テーブルにデータを入れた後、ダミーテーブルにデータを入れるトリガーを作っても、
トリガーは動作しません。
トリガーを動作させたければ、元テーブルにトリガーとなる動作をさせる前に、トリガーを作っておく必要があります。

【ロックについての検証２】for update

テーブルにupdate文を投げたまま戻ってこないことってよくありますね。
ORACLEにタイムアウトが設定されてないので、対象テーブルのロックが解除されるまで、
ずーっと待つことになります。
アプリの不具合で、対象テーブルのロックが保持されたままになった場合、後の処理が
待ちになってしまいます。

そうならないためにも、
これから更新しようとするテーブルが既にロックされているかどうか調べてから、処理をする
ように作るのもありだと思います。

そんなとき、select * from xxx for update文を使って、更新対象テーブルがロックされてるか
確認します。

■検証

★セッションA
・テスト用テーブル作成
SQL> create table a(a number);

表が作成されました。

・テスト用データ作成
SQL> insert into a value(1);
insert into a value(1)

SQL> insert into a values(1);

1行が作成されました。

SQL> insert into a values(2);

1行が作成されました。

SQL> insert into a values(3);

1行が作成されました。

SQL> insert into a values(4);

1行が作成されました。

SQL> insert into a values(5);

1行が作成されました。

SQL> commit;

コミットが完了しました。

・テストテーブルをupdate
SQL> update a set a=1;

この時点で、テストテーブルaにロックが掛かってます。

★セッションB
別セッションで、ロック中のaテーブルを参照します。

・30秒待って、aテーブルへのロックを取得できなければ、諦める
09:31:11 SQL> select * from a for update wait 30;
select * from a for update wait 30
              *
行1でエラーが発生しました。:
ORA-30006: リソース・ビジー; WAITタイムアウトの期限に達しました。

・aテーブルへのロックを取得できなければ、即諦める
09:31:43 SQL>
09:31:43 SQL> select * from a for update nowait;
select * from a for update nowait
              *
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました

・aテーブルへのロックを取得できるまで待つ
SQL> select * from a for update;
※CTRL + Cで止めるまで待ってます。



その後・・・・

★セッションA
・テスト用テーブルの処理を確定し、ロック解除
SQL> rollback;

ロールバックが完了しました。

★セッションB
・テスト用テーブルのロックが解除されたので、セッションBがロックを獲得し、
　テーブルaの結果が返ってくる
SQL> select * from a for update;

         A
----------
         1
         2
         3
         4
         5

select * from a for update;で、テーブルaがロックされます。

select * from aは、テーブルaへのロックを取得しないので、結果が返ってきます。