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

InfoPath & SQL Server !

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 文でエラーがおこるためだ。

コメントを残す

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

WordPress.com ロゴ

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

Twitter 画像

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

Facebook の写真

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

Google+ フォト

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

%s と連携中

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