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

本日は、久しぶりにデータベースの話題である。

システム業界人でない人が、データベースといったら「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トリガについては、またやるかも。

続きの記事

MS AccessでBEFOREトリガー

Access クエリ 徹底活用ガイド ~仕事の現場で即使える

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2018/05/25
• メディア: 大型本

---

サイト内を検索