ある目的で、データベース・テーブルの更新日時をプログラムで取得する必要が出てきました。レコードの追加、アップデートが最後に行われた日時を知りたいというわけです。こういうことは、割と普通なのではないかと思って調べてみましたら、案の定そんな方法がありました。データベースは、Microsoft SQL Server 2005です。
SQL Server 2000では使えないらしいのですが、システムテーブルに対してクエリを行えば、テーブルについての情報を取得できるそうで、その方法を利用しようというわけです。さっそく、以下のようなクエリを書いてみました。
SELECT modify_date FROM sys.objects WHERE name=table AND type='U'
tableは、情報を取得したいテーブルの名前です。modify_dateは、テーブルが変更された日時を返すフィールドです。これでうまく行くでしょうか？
結果、期待どおりには行きません。どうやら、テーブルが最後に更新された日時、というのを勘違いしていたようです。この場合の更新とは、ALTERコマンドが最後に実行された日時を意味するようで、INSERTやUPDATEが実行された日時ではないようです。
これは期待するものではないですから、別に方法はないか、調べました。今度の方法は、データベースのトリガ機能を利用するものです。レコードが更新されたらレコード中の日時フィールドを現在日時に更新するというものです。こんなトリガをテーブルに設定します。
CREATE TRIGGER trigger_name ON table FOR UPDATE AS BEGIN UPDATE table SET modify_date=getdate() WHERE id=(SELECT id FROM inserted) END
trigger_nameはトリガ名、適当な名前にします。tbaleはテーブル名です。tableに、modify_dateというdatetime型のフィールドを作成しておきます。作成時にも日付が入るように、既定値もgetdate()関数の値にしておけばよいでしょう。
これは、私にはよくわからなかったのですが、WHERE句の中にあるサブクエリでinsertedというテーブル？がありますが、これは現在処理中のレコードを意味するようです。
ということで、テーブルにレコードが追加されれば既定値で現在日時が入り、UPDATEで更新されれば現在日時が入るようになりました。では、テーブル全体の最終更新日時はというと、以下のようなクエリで達成できます。
SELECT TOP 1 modify_date FROM table ORDER BY modify_date DESC
modify_date順で逆順ソートし、先頭要素のmodify_dateを持ってきます。これで、目的は達成されました。
続けて、これを何に使ったのか？ということを書きましょう。