ホチキス先生の「プログラマーと呼ばれたい」

InfoPath & SQL Server !

SQL Server : サーバーサイドでデータの作成日付をテーブルに自動的に記録するために、フィールドの列のプロパティで規定値を設定し、INSTEAD OF INSERTトリガを利用する

leave a comment »

データを管理する際に、そのデータがいつ作られ、いつ更新されたかを記録しておくことが求められる場合がある。これらの日付を記録するため、次のようにテーブルに「作成日」と「更新日」のフィールドを持たせておくことにする。どちらのフィールドも「datetime型」としておこう。

SQL_storedatetime_001_mid_640

<fig.1 : テーブル「Table_3」に「作成日」と「更新日」のフィールドを作成した>

このテーブルに新しいデータを生成し、「作成日」の値を記録するとき、クライアント側で明示的に処理を行うなら次のようなSQL文になるだろう。このとき、getdate()はSQL Serverから日付を取得する関数だ。

————————————————————————

insert into Table_3(サンプル値,作成日) values(‘ほげほげ’,getdate())

————————————————————————

SQL_storedatetime_002_mid_640

<fig.2 : 明示的にSQL文によってデータ作成日を登録した>

SQL_storedatetime_003_mid_640

<fig.3 : Table_3に作成日が登録された>

すでに生成したデータを更新し、「更新日」の値を記録するとき、クライアント側で明示的に処理を行うなら次のようなSQL文になるだろう。

————————————————————————

update Table_3 set サンプル値 = ‘ほにゃらら’,更新日 = getdate() where サンプル値 = ‘ほげほげ’

————————————————————————

SQL_storedatetime_004_mid_640

<fig.4 : Table_3のデータ「ほげほげ」を「ほにゃらら」に更新し、明示的にSQL文によってデータ作成日を登録した>

SQL_storedatetime_005_mid_640

<fig.5 : Table_3のデータを更新し、更新日が登録された>

新しいデータを作成したときに自動的に「作成日」が記録されるようにするには、フィールドのプロパティで「規定値」を設定する方法がある。規定値に「getdate()」関数を設定しておけばいい。規定値を設定すれば、SQL文で明示的に日付を与えなくてもテーブルには自動的に日付が入るようになる。

SQL_storedatetime_006_mid_640

<fig.6 : Table_3の「作成日」フィールドのプロパティで「規定値」を「getdate()」に設定した>

フィールドに規定値を設定すると、次のSQL文で新しいデータを生成すると「作成日」の値が自動的に記録される。

————————————————————————

insert into Table_3(サンプル値) values(‘規定値でほげ’)

————————————————————————

SQL_storedatetime_007_mid_640

<fig.7 : Table_3に「作成日」を明示的に指定せずにデータを生成した>

SQL_storedatetime_008_mid_640

<fig.8 : Table_3に「作成日」を明示的に指定せずにデータを生成し、規定値が記録された>

このように規定値を使って自動的に値を与えることは一般的だが、この方法ではうまくいかない場合がある。いくら規定値を指定していても、規定値はあくまでも値が示されないときのものであって、ユーザーが明示的に値を与えると規定値は無視されユーザーの指定した値が記録されるのだ。たとえばユーザーが次のSQL文を使うと「記録日」が期待どおりの「規定値」にならない。

————————————————————————

insert into Table_3(サンプル値,作成日) values(‘昔の日付で出ています’,’1961/8/13′)

————————————————————————

SQL_storedatetime_009_mid_640

<fig.9 : 「作成日」に規定値を設定したTable_3にSQLで明示的に日付を指定してデータを生成するSQL文を実行した>

SQL_storedatetime_010_mid_640

<fig.10 : 「作成日」に規定値を設定したTable_3にSQLで明示的に日付を指定して過去の日付でデータを生成した>

データを管理するためには、ユーザーが「作成日」に勝手な値を指定できないようにしたほうがいい。そこでユーザーが「作成日」に明示的な日付を設定しても、それを無効にしてサーバーサイドで日付を強制的に設定される方法を考えてみよう。それにはテーブルにINSERTトリガを設定する方法がある。INSERTトリガは、テーブルにデータがINSERTされたときに自動的に実行されるストアドプロシージャだ。

INSERTトリガが実行されるタイミングは2つある。ひとつはINSERTが実行された後にトリガが実行されるAFTER INSERT、もうひとつはINSERTが実行される前にトリガが実行されるINSTEAD OF INSERTだ。INSTEAD OF INSERTはその名前からわかるように、実際のINSERT文に代わって実行されるトリガだ。ためしに次のSQL文でトリガをテーブルに設定してみよう。トリガを作成するには、create triggerを使う。

————————————————————————

create trigger trig_01 on Table_3 instead of insert
as
insert into Table_3(サンプル値,作成日) values(‘トリガでほげ’,GETDATE())

————————————————————————

このトリガを設定するSQL文で、create triggerの次にくる「trig_01」はトリガ名だ。わかりやすい名前を考えて自分で考えてつけることになる。その後onに続いてどのテーブルに対して設定するのかの「テーブル名」を記述している。そしてinstead of insertがトリガの種類だ。次にasに続けて、INSERTに代わって実行させたいSQL文を記述している。トリガを作成したら、左のペイン「オブジェクトエクスプローラー」の「Table_3」を展開すると「トリガ」のところに「trig_01」という名前のトリガができていることがわかる。

SQL_storedatetime_014_mid_640

<fig.11 : create trigger文でTable_3にINSTEAD OF INSERTトリガを作成した>

これでTable_3に何か値をinsertしようとしても、代わりにINSTEAD OF INSERTトリガが働き、あらかじめ決められた内容のデータしか生成しなくなる。たとえば次のようなSQL文をTable_3に実行してみよう。

————————————————————————

insert into Table_3(サンプル値,作成日) values(‘どうなる’,’1961/8/13′)

————————————————————————

SQL_storedatetime_015_mid_640

<fig.12 : INSTEAD OF INSERTトリガを設定したTable_3に明示的にinsert文を実行する>

ひとつのinsert文しか実行していないのに、処理結果が2つあらわれている。トリガが動いたのだ。

SQL_storedatetime_016_mid_640

<fig.13 : INSTEAD OF INSERTトリガを設定したTable_3に明示的にinsert文を実行した結果>

Table_3を見ると、明示的に実行されたINSERT文は無視され、トリガによって強制的にデータがinsertされたことがわかる。「作成日」はgetdate()で得られた日付になっている。

ここで「作成日」が強制的にgetdate()の値になったことはよいが、「サンプル値」が決まった値では困る。「サンプル値」はもとのinsert文で示された値でなければいけない。このときINSERTトリガでは、もとのinsert文によって与えられたデータは、一時的なテーブル「INSERTED」に保存されている。トリガを変更し、INSERTEDテーブルの値を利用して「サンプル値」を取得しよう。トリガを変更するには、ALTER TRIGGERを使う。

————————————————————————

alter trigger trig_01 on Table_3 instead of insert
as
insert into Table_3(サンプル値,作成日) select サンプル値,getdate() from inserted

————————————————————————

このINSTEAD OF INSERTトリガで、「サンプル値」はinsert文によって一時テーブルinsertedに記録された「サンプル値」を得、「作成日」はgetdate()で与えている。

SQL_storedatetime_017_mid_640

<fig.14 : alter triggetによってINSTEAD OF INSERTトリガを変更した>

SQL_storedatetime_018_mid_640

<fig.15 : INSTEAD OF INSERTトリガを変更したテーブルにINSERT文を実行した>

SQL_storedatetime_019_mid_640

<fig.16 : 「サンプル値」をinserted一時テーブルから取得し「作成日」はgetdate()で明示的に値を与えた結果>

このように、テーブルにデータを生成するとき「作成日」を与えたいとき、ひとつはフィールドのプロパティで「規定値」を設定する方法があり、さらにユーザーによる「作成日」の明示的な指定を避けたいときは、INSTEAD OF INSERTトリガを設定し、insertされた値を強制的に変更する手法を使うことができる。INSTEAD OF INSERTトリガだけでなく、トリガは他にもいろいろな自動化処理をするために使えるので、データの整合性をとったり履歴を記録したりすることに使うなど、工夫のしがいがあるところだ。

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中

%d人のブロガーが「いいね」をつけました。