So-net無料ブログ作成
検索選択

MS Accessでトリガー(データマクロ・SQLではダメ) [Accessクエリ]



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

システム業界人でない人が、データベースといったら「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でやるから。でトリガの駆動部分は、マクロで書く、ということらしいので早速作ってみる。
トリガは、データマクロという種類のマクロになる。データマクロの作り方はというと、「テーブルツール」の「テーブル」タブにあるボタンで作成できる。マクロタブじゃないので注意されたし。

Accessデータマクロ.png

ツールバーのボタンは以下のように分類されている。

イベント前
 変更前
 削除前
イベント後
 挿入後処理
 更新後処理
 削除後処理

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を作って、「イベント後」の「挿入後処理」ボタンをクリック。

Accessデータマクロの作成.png

おー、なんか難しそうな画面になった。

これで、マクロを組めと...
とりあえず、レコードの作成を追加してみた。

INSERTトリガを作成できた。しかし、名前を付ける作業が発生しなかった。つまりは、テーブルに対してひとつのINSERTトリガしか作成できない。ということだな。
そういうことにしよう。

とりあえず、INSERTトリガを作成することができた。SQLでいえば、以下のようなCREATE TRIGGERまで実行できた感じ。

CREATE TRIGGER 挿入後処理
AFTER INSERT ON foo
INSERT foo_history VALUES(...)

えーと削除する方法がよくわからない。SQLでいうところの、DROP TRIGGERの方法が...

どこかのメニューに隠れているのであろう。後で探すとして、データマクロのレコードの作成がうまくいかず、というかINSERT時の値を指定できていないので、当たり前のようにエラーになっている。先に、ここをなんとかしよう。

AccessINSERTトリガの作成.png

上記のようにマクロを組んでみた。
foo_historyテーブルには、trg_opとtimestampの2列を定義済である。
マクロを保存して、テーブルfooに行を追加すれば...

できた。
INSERTトリガが動いて、foo_historyにレコードが作成できた。

DELETEトリガ

次は、DELETEトリガを作成してみる。
テーブルfooの画面に戻り、「イベント後」の「削除後処理」ボタンをクリック。
同じようにマクロを組む画面が表示される。

foo_historyテーブルへのレコード追加の部分は、共通化できそうなので「名前付きマクロ」を作成してみることにする。

Access名前付きマクロの作成.png

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

AccessDELETEトリガ.png

DELETEトリガからregist_foo_historyを呼び出す。
トリガ内での削除レコードの参照方法は「old」で可能であった。これはSQL標準ということか。

名前付きマクロの「マクロの名前変更/削除」で作成したマクロの一覧を表示できる。ここに、「挿入後処理」、「削除後処理」が表示される。横に表示されている削除ボタンをクリックすれば、データマクロを削除することができる。

UPDATEトリガ

最後に、UPDATEトリガを作成してみる。

テーブルfooの画面に戻り、「イベント後」の「更新後処理」ボタンをクリック。
同じようにマクロを組む画面が表示される。

更新前がOldだから、更新後のレコードは、newかとカンでやってみたが、エラーになってしまった。カンじゃだめか。普通に、foo.aとすればよかった。SQL標準じゃないのか。残念。

AccessUPDATEトリガ.png

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

続きの記事
MS AccessでBEFOREトリガー





サイト内を検索


nice!(0)  コメント(0) 
共通テーマ:携帯コンテンツ

AREarthroidPro ARで地球を表示するアプリ

Copyright Atsushi Asai Google+朝井淳
[改訂第4版]SQLポケットリファレンス

[改訂第4版]SQLポケットリファレンス

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2017/02/18
  • メディア: 単行本(ソフトカバー)

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2015/03/03
  • メディア: 単行本(ソフトカバー)
C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2011/04/08
  • メディア: 単行本(ソフトカバー)

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2013/04/16
  • メディア: 単行本(ソフトカバー)