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

InfoPath & SQL Server !

Posts Tagged ‘INSERTED

SQL Server : サーバーサイドでデータの変更日付をテーブルに自動的に記録するために、AFTER UPDATEトリガを利用する。変更データの絞込みにはサブクエリではなく in 句を使う。

leave a comment »

「SQL Server : サーバーサイドでデータの作成日付をテーブルに自動的に記録するために、フィールドの列のプロパティで規定値を設定し、INSTEAD OF INSERTトリガを利用する」では、テーブルのフィールドに「作成日」のフィールドを作り、データの生成日と時間を自動的に記録する方法を説明した。データの生成日と時間をサーバー側で記録するには、フィールドのプロパティに既定値を設定する方法と、INSTEAD OF INSERTトリガで強制的にデータを与える方法があった。では、既に生成しているデータの「更新日」をSQL Server側で自動的に記録するにはどうすればよいだろう。これもテーブルに「トリガ」を設定することで実現できるが、少し工夫が必要だ。

まず次のようなテーブルを作ろう。

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

フィールド名 データ型 備考
ID int 主キー、IDENTITY(はい)、シード(1)、増分(1)
データ int
作成日 datetime
更新日 datetime

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

SQLSV_blog_update_datetime_001_mid_640

<Fig.1 : サンプルの「テスト」テーブルを作る>

テーブルができたら、いくつかサンプルのデータを作っておこう。

SQLSV_blog_update_datetime_003_mid_640

<Fig.2 : 「テスト」テーブルにサンプルのデータを作っておく>

次に、この「テスト」テーブルに次のクエリでAFTER UPDATEトリガを設定する。

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

CREATE trigger trig_02 on テスト after update

as

update テスト set 更新日 = getdate() where ID = (select ID from inserted)

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

SQLSV_blog_update_datetime_002_mid_640

<Fig.3 : 「テスト」テーブルにトリガを設定する>

SQLSV_blog_update_datetime_004_mid_640

<FIg.4 : 「テスト」テーブルにトリガを設定した>

このトリガは、「テスト」テーブルにUPDATE文が発行されたとき、変更されたデータが入っている「inserted」一時テーブルのIDを調べ、そのIDと同じIDのデータに対して「更新日」のフィールド値を gerdate() 関数で現在の日付と時間を取得して更新するというものだ。

では、このトリガを設定した「テスト」テーブルのデータを更新してみよう。次のクエリを試してみる。

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

UPDATE テスト SET データ = 101 where データ = 100

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

SQLSV_blog_update_datetime_005_mid_640

<Fig.5 : トリガを設定した「テスト」テーブルに UPDATE 文を実行する>

UPDATE 文は1件のデータを更新するものだが、処理結果のメッセージが2行表示されている。トリガが動いたのだ。ではテーブルのデータを確認してみよう。

SQLSV_blog_update_datetime_006_mid_640

<Fig.6 : データを更新した「テスト」テーブルのデータを確認する>

トリガによって「更新日」のデータが自動的に記録されたことがわかる。だが、実はこのトリガには問題がある。試しに、次のクエリを実行してみよう。このクエリでは、データが 800 のデータを更新しているが、サンプルデータには 800 のデータが2件あるのだ。

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

UPDATE テスト SET データ = 801 where データ = 800

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

SQLSV_blog_update_datetime_007_mid_640

<Fig.7 : 「テスト」テーブルの2件のデータを更新しようとしてエラーになった>

トリガに設定した UPDATE 文はサブクエリだ。サブクエリでは複数の値を処理することができない。これでは困るので、トリガを変更する。変更するには、テーブルを展開してトリガを表示し、右クリックで「変更」をクリックする。

SQLSV_blog_update_datetime_010_mid_640

<Fig.8 : 「テスト」テーブルに設定したトリガを変更する>

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

update テスト set 更新日 = getdate() where ID in (select ID from inserted)

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

= でつないだサブクエリを in 句に変更するのだ。

SQLSV_blog_update_datetime_011_mid_640

<Fig.9 : トリガのサブクエリを in 句に変更する>

変更できたら、もういちどデータを2件変更する UPDATE 文を実行してみよう。うまくいくはずだ。

SQLSV_blog_update_datetime_012_mid_640

<Fig.10 : データを2件変更する UPDATE 文を実行する>

変更したデータを確認しよう。

SQLSV_blog_update_datetime_013_mid_640

<Fig.10 : UPDATE 文を変更したテーブルを確認する>

このように更新日の日付と時間をサーバー側で自動的に記録するには、テーブルにトリガを設定すればよい。そのとき、変更をうけたデータを絞り込むにサブクエリではなくて in 句を利用する。サブクエリでは複数のデータを更新する UPDATE 文でエラーがおこるためだ。

広告

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トリガだけでなく、トリガは他にもいろいろな自動化処理をするために使えるので、データの整合性をとったり履歴を記録したりすることに使うなど、工夫のしがいがあるところだ。