MS Accessでトリガー（データマクロ・SQLではダメ）

2017年5月22日 2025年4月22日 asai

asai

本日は、久しぶりにデータベースの話題である。
システム業界人でない人が、データベースといったら「MS Access」と連想するであろう。Microsoft社が開発したOfficeスイートのひとつであるため、結構有名なはず。
しかし、ExcelやWordに比べるとAccessの認知度は少し低いかも。
Accessは、リレーショナルデータベースなので、SQLが使用できる。
でも、Excelの世界からAccessの世界にやってきた人々は「SQLって何？」な感じであろう。なにせ、Accessでは、クエリを専用のGUIエディタで作成できるので、わざわざSQLを書かなくても問合せができちゃう。そう、「SQLポケットリファレンス」を参照しなくてもOKなわけです。
Oracleとかサーバ系のシステム開発の世界からやってきた人は、クエリのデザインビューより、SQLビューにして、SELECT * FROM foo WHERE a=1とか書いてしまった方が、楽だし話が早い、と思っているに違いない。
面倒な結合条件を書くのは、デザインビューでやった方が楽な場合もあるかも。
かくいう私といえば、SQLビューにして、SELECT命令を書く方が好きだなぁ。やっぱし。
Accessでのトリガ
Accessで使えるSQL命令って、結構制限されている。
例えば、CREATE XXXX命令は、多くのコマンドが実行できない。ALTER XXXX命令も同様。トリガを作成しようとCREATE TRIGGER trg... と書いてもトリガは作成できない。
トリガに似た機能はあるのだが...
という経緯があり、SQLポケットリファレンスでは、Accessではトリガが使えないことになっている。
ちょっと突っ込まれているようだし、今後のために、Accessでのトリガを調べてみることにした。
Accessでのストアドプロシージャ
Accessでは、IFとかWHILEといったSQLでの制御命令がない。なんでかというと、VBAでやるから。でトリガの駆動部分は、マクロで書く、ということらしいので早速作ってみる。
トリガは、データマクロという種類のマクロになる。データマクロの作り方はというと、「テーブルツール」の「テーブル」タブにあるボタンで作成できる。マクロタブじゃないので注意されたし。

ツールバーのボタンは以下のように分類されている。
イベント前
　変更前
　削除前
イベント後
　挿入後処理
　更新後処理
　削除後処理
SQLでトリガを作ったことがある方なら、名前だけで機能がなんとなくわかってしまうような。
CREATE TRIGGERの文法を、SQLポケットリファレンスから引用してみよう。
CREATE TRIGGER trigger_name
timing event ON table_name [ FOR EACH { ROW | STATEMENT }]
イベント前、後の分類は、CREATE TRIGGER trigger_name の後のtimingを意味する？
まぁ、雰囲気としてはそうかな。
変更前/更新後は、UPDATEトリガのことだよね。変更と更新って似た名前になっているが...
削除前/削除後は、DELETEトリガですな。
挿入後は、INSERTトリガ。INSERTトリガは、BEFORE指定できないということか。INSTEADがないということなのか。
INSERTトリガを作成してみる
まずは、INSERTトリガを作成してみる。
テーブルfooを作って、「イベント後」の「挿入後処理」ボタンをクリック。

おー、なんか難しそうな画面になった。
これで、マクロを組めと...
とりあえず、レコードの作成を追加してみた。
INSERTトリガを作成できた。しかし、名前を付ける作業が発生しなかった。つまりは、テーブルに対してひとつのINSERTトリガしか作成できない。ということだな。
そういうことにしよう。
とりあえず、INSERTトリガを作成することができた。SQLでいえば、以下のようなCREATE TRIGGERまで実行できた感じ。
CREATE TRIGGER 挿入後処理
AFTER INSERT ON foo
INSERT foo_history VALUES(...)
えーと削除する方法がよくわからない。SQLでいうところの、DROP TRIGGERの方法が...
どこかのメニューに隠れているのであろう。後で探すとして、データマクロのレコードの作成がうまくいかず、というかINSERT時の値を指定できていないので、当たり前のようにエラーになっている。先に、ここをなんとかしよう。

上記のようにマクロを組んでみた。
foo_historyテーブルには、trg_opとtimestampの2列を定義済である。
マクロを保存して、テーブルfooに行を追加すれば...
できた。
INSERTトリガが動いて、foo_historyにレコードが作成できた。
DELETEトリガ
次は、DELETEトリガを作成してみる。
テーブルfooの画面に戻り、「イベント後」の「削除後処理」ボタンをクリック。
同じようにマクロを組む画面が表示される。
foo_historyテーブルへのレコード追加の部分は、共通化できそうなので「名前付きマクロ」を作成してみることにする。

regist_foo_historyという名前の名前付きマクロを作成してみた。どうもこいつは、ストアドプロシージャだな。SQLで中身を書けないのが"非常に"もどかしい。

DELETEトリガからregist_foo_historyを呼び出す。
トリガ内での削除レコードの参照方法は「old」で可能であった。これはSQL標準ということか。
名前付きマクロの「マクロの名前変更/削除」で作成したマクロの一覧を表示できる。ここに、「挿入後処理」、「削除後処理」が表示される。横に表示されている削除ボタンをクリックすれば、データマクロを削除することができる。
UPDATEトリガ
最後に、UPDATEトリガを作成してみる。
テーブルfooの画面に戻り、「イベント後」の「更新後処理」ボタンをクリック。
同じようにマクロを組む画面が表示される。
更新前がOldだから、更新後のレコードは、newかとカンでやってみたが、エラーになってしまった。カンじゃだめか。普通に、foo.aとすればよかった。SQL標準じゃないのか。残念。

これで、３つのトリガを作成することができた。
fooテーブルのレコードを追加、削除、更新すると、foo_historyテーブルに操作の履歴が残るようになる。
他のBEFOREトリガについては、またやるかも。