ログトリガー

リレーショナルデータベースでは、ログトリガーまたは履歴トリガーは、データベーステーブルへの行の挿入、更新、削除などの変更に関する情報を自動的に記録するメカニズムです。

これは、変化データの取得、およびデータウェアハウスにおける緩やかに変化するディメンションの処理のための特定の手法です。

導入

運用データベースは通常、組織の現在の状態を捉えるように設計されており、履歴アーカイブではなく「現在」のスナップショットとして機能します。このような環境では、更新はしばしば破壊的になります。特定のデータポイントが変更されると、システムは効率性を優先し、既存の値を新しい値に置き換えます。たとえば、従業員名簿や顧客名簿で、個人が新しい場所に移動すると、データベースに対して更新操作が実行され、新しい住所が古い住所に直接書き込まれます。その結果、以前の住所は完全に上書きされてシステムから失われ、データベースには最新の情報のみが残り、エンティティの履歴や以前の状態の記録はなくなります。

ログトリガーは、変更を自動的に検知し、情報の以前の状態を保存するための仕組みです。

意味

監査対象とするテーブルがあるとします。このテーブルには以下の列が含まれています。

Column1, Column2, ..., Columnn

この列は主キーColumn1であると想定されます。

これらの列は、以下の型を持つように定義されています。

Type1, Type2, ..., Typen

ログトリガーは、テーブルに対する変更（INSERT、UPDATE、DELETE操作）を、以下のように定義される別の履歴テーブルに書き込むことで機能します。

CREATE TABLE HistoryTable ( Column1 Type1 , Column2 Type2 , : : Columnn Typen ,開始日時(DATETIME) 、終了日時(DATETIME )

上記のように、この新しいテーブルには元のテーブルと同じ列に加え、型との2つの新しい列が含まれています。これはタプルバージョニングとして知られています。これらの 2 つの追加列は、指定されたエンティティ (主キーのエンティティ) に関連付けられたデータの「有効期間」を定義します。言い換えれば、 (含まれる) と(含まれない)の間の期間にデータがどのように存在していたかを保存します。DATETIMEStartDateEndDateStartDateEndDate

元のテーブル上の各エンティティ（一意の主キー）に対して、履歴テーブルに以下の構造が作成されます。データは例として示されています。

時系列順に表示される場合、任意の行のEndDate列は、その次の行の列（存在する場合）と全く同じであることに注意してください。これは、定義上、の値が含まれないため、両方の行がその時点まで共通していることを意味するものではありません。StartDateEndDate

ログトリガーには、古い値（DELETE、UPDATE）と新しい値（INSERT、UPDATE）がトリガーにどのように公開されるかによって、2つのバリアントがあります（これはRDBMSに依存します）。

レコードデータ構造のフィールドとしての古い値と新しい値

CREATE TRIGGER HistoryTable ON OriginalTable FOR INSERT , DELETE , UPDATE AS DECLARE @ Now DATETIME SET @ Now = GETDATE ()/* セクションを削除中 */UPDATE HistoryTable SET EndDate = @ Now WHERE EndDate IS NULL AND Column1 = OLD . Column1/* セクションの挿入 */INSERT INTO HistoryTable ( Column1 , Column2 , ..., Columnn , StartDate , EndDate ) VALUES ( NEW . Column1 , NEW . Column2 , ..., NEW . Columnn , @ Now , NULL )

仮想テーブルの行として表される古い値と新しい値

CREATE TRIGGER HistoryTable ON OriginalTable FOR INSERT , DELETE , UPDATE AS DECLARE @ Now DATETIME SET @ Now = GETDATE ()/* セクションを削除中 */UPDATE HistoryTable SET EndDate = @ Now FROM HistoryTable , DELETED WHERE HistoryTable . Column1 = DELETED . Column1 AND HistoryTable . EndDate IS NULL/* セクションの挿入 */INSERT INTO HistoryTable ( Column1 , Column2 , ..., Columnn , StartDate , EndDate ) SELECT ( Column1 , Column2 , ..., Columnn , @ Now , NULL ) FROM INSERTED

互換性に関する注意事項

上記のコードは、コードの慣用表現として示されています。トリガーの構文は、 RDBMSによって大きく異なります。例えば、次のようになります。

この関数GetDate()はシステムの日時を取得するために使用されますが、特定のRDBMSでは別の関数名を使用したり、別の方法でこの情報を取得したりする場合があります。
一部のRDBMS（Db2、MySQLなど）では、同じトリガーを複数の操作（INSERT、DELETE、UPDATE）に関連付けることができません。そのため、各操作ごとにトリガーを作成する必要があります。INSERT操作の場合は挿入セクションのみを指定し、DELETE操作の場合は削除セクションのみを指定し、UPDATE操作の場合は、上記のように両方のセクション（削除セクションが先、挿入セクションが後）を指定する必要があります。これは、 UPDATE操作が論理的には DELETE操作の後にINSERT操作が続くものとして表現されるためです。
示されているコードでは、古い値と新しい値を含むレコードデータ構造はOLDとと呼ばれていますNEW。特定のRDBMSでは、これらは異なる名前を持つ可能性があります。
示されているコードでは、仮想テーブルはDELETEDとと呼ばれていますINSERTED。特定のRDBMSでは、これらのテーブルは異なる名前を持つ可能性があります。別のRDBMS（Db2）では、これらの論理テーブルの名前を指定することもできます。
示されているコードでは、コメントはC/C++スタイルで記述されていますが、特定のRDBMSではサポートされていないか、別の構文を使用する必要があります。
いくつかのRDBMSでは、トリガーの本体をキーワードBEGINとENDキーワードで囲む必要があります。

一般的なRDBMSでの実装

IBM Db2

出典：^{[ 1 ]}

トリガーは複数の操作（INSERT、DELETE、UPDATE）に紐付けることはできないため、各操作ごとにトリガーを作成する必要があります。
古い値と新しい値は、レコードデータ構造のフィールドとして公開されます。これらのレコードの名前は定義可能で、この例では、O古い値には、N新しい値にはという名前が付けられています。

-- INSERT のトリガーCREATE TRIGGER Database . TableInsert AFTER INSERT ON Database . OriginalTable REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN DECLARE Now TIMESTAMP ; SET NOW = CURRENT TIMESTAMP ;INSERT INTO Database.HistoryTable ( Column1 , Column2 , ... , Columnn , StartDate , EndDate ) VALUES ( N.Column1 , N.Column2 , ... , N.Columnn , Now , NULL ) ; END ;-- 削除用のトリガーCREATE TRIGGER Database . TableDelete AFTER DELETE ON Database . OriginalTable REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL BEGIN DECLARE Now TIMESTAMP ; SET NOW = CURRENT TIMESTAMP ;UPDATE Database.HistoryTable SET EndDate = Now WHERE Column1 = O.Column1 AND EndDate IS NULL ; END ;-- 更新用のトリガーCREATE TRIGGER Database . TableUpdate AFTER UPDATE ON Database . OriginalTable REFERENCING NEW AS N OLD AS O FOR EACH ROW MODE DB2SQL BEGIN DECLARE Now TIMESTAMP ; SET NOW = CURRENT TIMESTAMP ;UPDATE Database.HistoryTable SET EndDate = Now WHERE Column1 = O.Column1 AND EndDate IS NULL ;INSERT INTO Database.HistoryTable ( Column1 , Column2 , ... , Columnn , StartDate , EndDate ) VALUES ( N.Column1 , N.Column2 , ... , N.Columnn , Now , NULL ) ; END ;

Microsoft SQL Server

出典：^{[ 2 ]}

同じトリガーを、INSERT、DELETE、UPDATE のすべての操作に関連付けることができます。
DELETED古い値と新しい値は、およびという名前の仮想テーブルの行として表されますINSERTED。

CREATE TRIGGER TableTrigger ON OriginalTable FOR DELETE , INSERT , UPDATE ASDECLARE @NOW DATETIME SET @NOW = CURRENT_TIMESTAMPUPDATE HistoryTable SET EndDate = @ now FROM HistoryTable , DELETED WHERE HistoryTable . ColumnID = DELETED . ColumnID AND HistoryTable . EndDate IS NULLINSERTINTOHistoryTable(ColumnID,Column2,...,Columnn,StartDate,EndDate)SELECTColumnID,Column2,...,Columnn,@NOW,NULLFROMINSERTED

MySQL

A trigger cannot be attached to more than one operation (INSERT, DELETE, UPDATE), so a trigger must be created for each operation.
The old and new values are exposed as fields of a record data structures called Old and New.

DELIMITER$$/* Trigger for INSERT */CREATETRIGGERHistoryTableInsertAFTERINSERTONOriginalTableFOREACHROWBEGINDECLARENDATETIME;SETN=now();INSERTINTOHistoryTable(Column1,Column2,...,Columnn,StartDate,EndDate)VALUES(New.Column1,New.Column2,...,New.Columnn,N,NULL);END;/* Trigger for DELETE */CREATETRIGGERHistoryTableDeleteAFTERDELETEONOriginalTableFOREACHROWBEGINDECLARENDATETIME;SETN=now();UPDATEHistoryTableSETEndDate=NWHEREColumn1=OLD.Column1ANDEndDateISNULL;END;/* Trigger for UPDATE */CREATETRIGGERHistoryTableUpdateAFTERUPDATEONOriginalTableFOREACHROWBEGINDECLARENDATETIME;SETN=now();UPDATE HistoryTable SET EndDate = N WHERE Column1 = OLD . Column1 AND EndDate IS NULL ;INSERT INTO HistoryTable ( Column1 , Column2 , ..., Columnn , StartDate , EndDate ) VALUES ( New . Column1 , New . Column2 , ..., New . Columnn , N , NULL ); END ;

オラクル

同じトリガーを、INSERT、DELETE、UPDATE のすべての操作に関連付けることができます。
:OLD古い値と新しい値は、およびと呼ばれるレコードデータ構造のフィールドとして公開されます:NEW。
（DELETE操作を実行する際に）主キー:NEWを定義するレコードのフィールドがNULLかどうかをテストする必要があります。これは、すべての列にNULL値を持つ新しい行が挿入されるのを防ぐためです。

CREATE OR REPLACE TRIGGER TableTrigger AFTER INSERT OR UPDATE OR DELETE ON OriginalTable FOR EACH ROW DECLARE Now TIMESTAMP ; BEGIN SELECT CURRENT_TIMESTAMP INTO Now FROM Dual ;UPDATE HistoryTable SET EndDate = Now WHERE EndDate IS NULL AND Column1 = : OLD . Column1 ;IF : NEW . Column1 IS NOT NULL THEN INSERT INTO HistoryTable ( Column1 , Column2 , ..., Columnn , StartDate , EndDate ) VALUES (: NEW . Column1 , : NEW . Column2 , ..., : NEW . Columnn , Now , NULL ); END IF ; END ;

PostgreSQL

トリガーに関連付けられたアクションは関数として指定する必要があるため、まず関数を定義します。
古い値と新しい値は、とという名前の仮想テーブルの行として公開されますold_tableがnew_table、これらの名前は異なる場合があります。
トリガーは複数の操作（INSERT、DELETE、UPDATE）に関連付けることができますが、この場合は仮想テーブルの名前を指定するために各操作に異なるトリガーが関連付けられ、これらのトリガーは同じ関数を参照できます。

CREATE OR REPLACE FUNCTION process_for_table () RETURNS TRIGGER AS $$ DECLARE now TIMESTAMP : = NOW (); BEGIN --- セクションの削除IF ( TG_OP = 'UPDATE' OR TG_OP = 'DELETE' ) THEN UPDATE HistoricTable SET EndDate = now FROM HistoricTable AS H INNER JOIN old_table ON H . ColumnID = old_table . ColumnID WHERE HistoricTable . ColumnID = H . ColumnID AND HistoricTable . EndDate IS NULL ; END IF ;--- セクションの挿入IF ( TG_OP = 'INSERT' OR TG_OP = 'UPDATE' ) THEN INSERT INTO HistoricTable SELECT ColumnID , Column2 , ..., Columnn , now , NULL FROM new_table ; END IF ; RETURN NULL ; END ; $$ LANGUAGE plpgsqlCREATE TRIGGER TriggerForTableInsert AFTER INSERT ON OriginalTable REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION process_for_table ();CREATE TRIGGER TriggerForTableUpdate AFTER UPDATE ON OriginalTable REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION process_for_table ();CREATE TRIGGER TriggerForTableDelete AFTER DELETE ON OriginalTable REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION process_for_table ();

歴史情報

通常、データベースのバックアップは、過去の情報を保存および取得するために使用されます。データベースのバックアップは、すぐに使用できる過去の情報を効率的に取得する手段というだけでなく、セキュリティメカニズムとしての役割も果たします。

（完全な）データベースバックアップは、特定の時点におけるデータのスナップショットに過ぎません。そのため、各スナップショットの情報は把握できますが、スナップショット間の情報は把握できません。データベースバックアップに含まれる情報は、時間的に離散的です。

ログトリガーを使用すると、得られる情報は離散的ではなく連続的になり、任意の時点での情報の正確な状態を知ることができます。ただし、その情報は、使用するRDBMSのDATETIMEデータ型によって提供される時間の粒度に制限されます。

利点

簡単だ。
これは商用製品ではなく、一般的なRDBMSの利用可能な機能を利用して動作します。
一度作成すれば、自動的に動作し、その後は人間の介入は一切不要です。
データベースのテーブルやデータモデルに関する深い知識は必要ありません。
現行の番組編成に変更を加える必要はありません。
既存のテーブルに変更を加える必要はありません。なぜなら、どのテーブルのログデータも別のテーブルに保存されるからです。
これは、プログラムされたステートメントとアドホックなステートメントの両方に対応しています。
変更（INSERT、UPDATE、DELETE操作）のみが記録されるため、履歴テーブルの増加率は変更に比例します。
トリガーをデータベース上のすべてのテーブルに適用する必要はなく、特定のテーブル、またはテーブルの特定の列に適用することもできます。

デメリット

変更を行ったユーザー（データベースユーザーではなく、情報システムユーザー）に関する情報は自動的に保存されません。この情報は明示的に提供される場合があります。情報システムでは強制的に適用される可能性がありますが、アドホッククエリでは適用されません。

使用例

テーブルの現在のバージョンを取得する

SELECT Column1 , Column2 , ..., Columnn FROM HistoryTable WHERE EndDate IS NULL

元のテーブル全体の同じ結果セットを返す必要があります。

特定の時点におけるテーブルのバージョンを取得する

@DATE変数に、関心のある時点または時刻が含まれていると仮定します。

SELECT Column1 , Column2 , ..., Columnn FROM HistoryTable WHERE @ Date >= StartDate AND ( @ Date < EndDate OR EndDate IS NULL )

ある時点におけるエンティティの情報を取得する

@DATE変数には関心のある時点または時刻が含まれており、変数には関心のあるエンティティの主キー@KEYが含まれているとします。

SELECT Column1 , Column2 , ..., Columnn FROM HistoryTable WHERE Column1 = @ Key AND @ Date >= StartDate AND ( @ Date < EndDate OR EndDate IS NULL )

エンティティの履歴を取得する

その@KEY変数には、対象となるエンティティの主キーが含まれていると仮定します。

SELECT Column1 , Column2 , ..., Columnn , StartDate , EndDate FROM HistoryTable WHERE Column1 = @ Key ORDER BY StartDate

エンティティがいつ、どのように作成されたかを取得する

その@KEY変数には、対象となるエンティティの主キーが含まれていると仮定します。

SELECT H2.Column1 , H2.Column2 , ... , H2.Columnn , H2.StartDate FROM HistoryTable AS H2 LEFT OUTER JOIN HistoryTable AS H1 ON H2.Column1 = H1.Column1 AND H2.Column1 = @Key AND H2.StartDate = H1.EndDate WHERE H2.EndDate IS NULL

プライマリキーの不変性

トリガーは主キーが常に同じであることを要求するため、主キーの不変性を確保または最大限に高めることが望ましい。主キーの値が変更されると、それが表すエンティティは自身の履歴を破壊してしまうからである。

主キーの不変性を実現または最大化するには、いくつかの方法があります。

代理キーを主キーとして使用する。識別と一意性以外の意味を持たない値を変更する理由はないため、その値が変更されることはありません。
不変の自然キーを主キーとして使用すること。優れたデータベース設計では、変更可能な自然キーは「真の」主キーとはみなすべきではありません。
変更可能な自然キーを主キーとして使用する場合（これは広く推奨されません）、そのキーが外部キーとなっているすべての箇所に変更が反映されます。このような場合、履歴テーブルも影響を受けるはずです。

データウェアハウジング

徐々に変化しているディメンション管理手法によれば、ログトリガーは以下のように分類されます。

タイプ2（タプルバージョン管理バリアント）
タイプ4（履歴テーブルの使用）

注記

Logトリガーは、トランザクションデータベースの履歴を自動的に生成するためにLaurence R. Ugalde ^{[ 3 ]}によって設計されました。

外部リンク

GitHubのログトリガー

参考文献

↑「データベースの基礎」ナリージ・シャルマ他著（初版、IBM社著作権、2010年）
↑「Microsoft SQL Server 2008 - データベース開発」Thobias Thernström 他著（Microsoft Press、2009年）
↑ 「R. Ugalde、Laurence; Log trigger」 . GitHub . 2022年6月26日取得。

[1] 「データベースの基礎」ナリージ・シャルマ他著（初版、IBM社著作権、2010年）

[2] 「Microsoft SQL Server 2008 - データベース開発」Thobias Thernström 他著（Microsoft Press、2009年）

[GitHub-3] 「R. Ugalde、Laurence; Log trigger」 . GitHub . 2022年6月26日取得。

[ 1 ]

[ 2 ]

[ 3 ]

導入

意味