テーブルで複数の処理を実行させるトリガー：SQL実践講座（24）

トリガーの作成

　今回は、トリガーの作成に挑戦します。トリガーは、テーブルに対する追加、更新、削除といったデータを修正する操作が行われた場合に、自動的に起動する特殊なストアドプロシージャです。関連するテーブル間でデータの整合性を保つためのルールを実装したり、または、データの修正時に自動起動するビジネスロジックを実装したりする場合に使用します。

　では早速、例題を実行しながら、SQLの確認をしていきましょう。

データの変更をログに記録するトリガー

　初めに簡単な例として、データの変更が発生したときにログを記録するトリガーを作成してみましょう。例題1は、Customersテーブルに変更が発生した場合に、SQL Serverログに「変更が発生した」ということを通知するメッセージを記録します。

　例題1では、3つのSQL文が必要になります。(1)と(2)のSQL文を順にクエリアナライザから実行し、トリガーを作成してみましょう。

 1： EXEC sp_addmessage 
 2：   @msgnum = 50001, 
 3：   @severity = 10, 
 4：   @msgtext = 'Trigger Executed',
 5：   @lang = 'us_english',
 6：   @with_log = 'with_log'
 7：
 8： EXEC sp_addmessage 
 9：   @msgnum = 50001, 
10：   @severity = 10, 
11：   @msgtext = 'トリガーが実行されました。',
12：   @with_log = 'with_log'

画面1　メッセージの追加（画面をクリックすると拡大表示します）

 1： CREATE TRIGGER trNotifyCustomerUpdate ON Customers 
 2： FOR INSERT, UPDATE, DELETE
 3： AS RAISERROR (50001,10,10)

画面2　トリガーtrNotifyCusgtomerUpdateの作成（画面をクリックすると拡大表示します）

　ここで、トリガーの実行を確認してみましょう。トリガーを発生させるために、Customersテーブルを疑似的に更新してみます。下記の例題を実行してみましょう。

UPDATE Customers SET Country = 'USA' WHERE Country = 'USA'

画面3　トリガーtrNotifyCustomerUpdateの実行結果（画面をクリックすると拡大表示します）

　例題2を実行すると、Customersテーブルに対して更新がされるため、作成したトリガーtrNotifyCustomerUpdateが実行されます。trNotifyCustomerUpdateが実行されたことは、「トリガーが実行されました。」というメッセージが表示されていることで確認できます。作成したSQLの詳細をみてみましょう。

メッセージを作成するSQL文の解説

　まず、例題1-(1)のSQL文を見てください。例題1-(1)で呼び出しているシステムストアドプロシージャの「sp_addmessage」は、ユーザー定義メッセージを作成します。ユーザー定義メッセージとは何かを説明する前に、SQL Serverログについて説明しましょう。 　

　SQL Serverは、データベースサーバの処理や環境変化の状況をログに記録しています。SQL Serverが記録したログを参照するためには、SQL Server Enterprise Managerを使用します。次のステップで、実際にログを参照してみましょう。

Enterprise Managerでログを参照する

(1) Enterprise Managerを起動する

(2) 左端に表示される「ツリー」で、以下を順に展開する

画面4　SQL Serverログの参照（画面をクリックすると拡大表示します）

　表示されたログには、SQL Serverの処理の実行状況やエラーの発生状況が記録されているのがわかると思います。このSQL Serverログは、障害が発生したときの原因究明などに利用できます。

　SQL Serverログには、SQL Serverが自動的に記録するメッセージ以外に、ユーザー定義のメッセージも記録します。ユーザー定義のメッセージは、ストアドプロシージャやトリガーの実行状況をログに記録する際に利用します。表示されるログには、「トリガーが実行されました。」というメッセージが記録されると思います。これは、例題2を実行したときに記録されたものです。

　例題1-(1)は、「トリガーが実行されました。」というユーザー定義のメッセージを作成するためのSQL文です。ここで作成したメッセージは、SQL Serverが管理しているメッセージリストに追加されますので、一度作成すると、SQL Serverの全データベースから利用が可能になります。ある特定のデータベース専用ではありません。

　sp_addmessageは、いくつかの引数を取ります。引数の指定の方法は、これまでの例題で利用してきた「定義した順番に引数を並べる」方式と、例題1‐(1)のように、「（引数名）＝（引数の値）」とする方法の2種類があります。引数が多い場合は、後者の方法で引数名を記述した方が、後で見たときに分かりやすいので便利です。

　sp_addmessageの引数を順に見ていきましょう。まず、「@msgnum」は、メッセージ番号を指定します。ユーザー定義メッセージで利用することが可能なメッセージ番号は、50001以上です。「@severity」は、メッセージの重要度で、1から25の数値でレベルを表します。今回は、エラーではなく情報レベルということで、10を指定しました。「@msgtext」が、メッセージの内容です。今回は静的なテキストのみとしましたが、パラメータを使用して動的に生成するメッセージも定義が可能です。「@lang」は、言語の指定です。省略した場合はSQL Serverのデフォルト言語になります。日本語環境では通常、日本語です。メッセージの追加は、us_englishの言語の追加がまず必要になりますので、us_englishを指定したメッセージと、@langを指定しないメッセージの2つを追加しています。最後の「@with_log」は、ログへの記録を行うか行わないかのフラグです。

　こうして追加したメッセージは、SQL Server Enterprise Managerで確認することが可能です。次のステップで実際に参照してみましょう。

SQL Server Enterprise Managerで追加メッセージを参照する

(1) Enterprise Managerを起動する

(2) メニュから、「ツール」→「SQL Serverメッセージの管理」を選択する

画面5　SQL Serverメッセージの管理（画面をクリックすると拡大表示します）

(3) 表示される画面で、「ユーザー定義メッセージのみ」を選択し、「検索」ボタンをクリックする

(4) 実行結果は、以下のとおり

画面6　SQL Serverメッセージの管理−検索結果（画面をクリックすると拡大表示します）

更新時に起動するトリガーtrNotifyCustomerUpdateの解説

　では、trNotifyCustomerUpdateの定義を見ていきましょう。

　例題1‐(2)では、1行目ではまず、「CREATE TRIGGER」として、トリガーの作成を指定しています。CREATE TRIGGERに続くtrNotifyCustomerUpdateがトリガー名称です。次に、ON句で、トリガーの対象となるテーブル名称を指定します。ここでは、Customersテーブルを指定しています。2行目では、FOR句で対象となる操作を指定しています。操作は、INSERT、 UPDATE、 DELETEの3つから任意の操作を指定します。例題1‐(2)‐(2)では、すべての操作を指定しています。そして、3行目のAS以降が、トリガーの本体です。

　例題1‐(2)では、RAISERROR関数を使用しました。RAISERROR関数は、エラーを発生し、メッセージを記録させることができます。最初の引数は、メッセージ番号です。例題1‐(2)‐(2)では、例題1‐(1)‐(1)で作成した50001番のメッセージを指定しています。2番目の引数は、重要度のレベル表す数値、3番目の引数は、状態を表す数値です。重要度のレベルを表す数値はユーザー定義メッセージに指定した重要度と同様ですが、RAISERROR時に指定することもできます。状態を表す数値は、特に定義がされていませんので、自由に使用することができます。今回は特に意味を持たせていません。

自動的にContactNameを生成するトリガーの作成

　では、もう1つ例題を作成してみましょう。Customersテーブルでは、CustomerNameは値が必須の列ですが、ContactNameはNULLが許可されている列です。ContactNameをNULLで登録しようとしたとき、CustomerNameから自動的にContactNameを生成して登録するトリガーを作成します。

　ではさっそく、次のSQL文を実行し、トリガーを作成してみましょう。

 1：  CREATE TRIGGER trGenerateContactName ON Customers 
 2：  FOR INSERT, UPDATE
 3：  AS 
 4：  BEGIN
 5：    IF UPDATE(CompanyName)
 6：      UPDATE Customers
 7：      SET ContactName = 'Contact For ' + CompanyName
 8：      WHERE CustomerID = (
 9：        SELECT CustomerID
10：        FROM inserted
11：        WHERE ContactName is NULL)
12：  END

画面7　トリガーtrGenerateContactNameの作成（画面をクリックすると拡大表示します）

　ここで、トリガーの実行を確認してみましょう。トリガーを発生させるために、Customersテーブルにデータを挿入してみます。下記の例題を実行してみてください。

INSERT INTO Customers
( CustomerID, CompanyName)
VALUES
( 'MIROS', 'Joan Miros Company')

画面8　トリガーtrGenerateContactNameの実行結果(1)（画面をクリックすると拡大表示します）

　ContactNameを指定せずにINSERTを実行しました。ContactNameが想定どおりに生成されているかを、SELECT文で確認してみましょう。

SELECT * FROM Customers WHERE CustomerID = 'MIROS'

画面9　トリガーtrGenerateContactNameの実行結果(2)（画面をクリックすると拡大表示します）

更新時に起動するトリガーtrGenerateContactNameの解説

　では、トリガーtrGenerateContactNameの定義をみてみましょう。

　例題3の1行目のトリガーの宣言は、例題1‐(2)と同様です。例題3では、trGenerateContactNameがトリガー名です。2行目の操作の指定は、INSERTとUPDATEの2種類を指定しています。ContactNameの自動生成ですので、DELETEは必要ないですね。

　3行目以降がトリガーの本体です。5行目はIF UPDATE句で更新された列の検査をしています。IF UPDATE句は、トリガーの構文の一部で特殊なIF文です。UPDATE句には対象となるテーブルの列を指定することができます。ここでは、ContactName列が更新されているかを検査し、更新されていた場合はそれ以降の処理を実行します。複数の列の更新を検査する場合は、

というようにUPDATE句をANDもしくはORでつないで記述します。

　6行目から11行目は、UPDATE文でCustomersテーブルを更新します。サブクエリに登場する「inserted」というテーブルは、トリガーで使用する特殊な内部テーブルです。今回は使用していないですが、対となる「deleted」というテーブルもあります。これらの内部テーブルは、対象となった操作において「挿入されたデータ」と「削除されたデータ」を保存しているテーブルです。例題6においては、INSERT文でデータを追加しましたので、トリガーが呼び出されたときのinsertedテーブルとdeletedテーブルの状態は、insertedテーブルに挿入した行のデータが格納されており、deletedテーブルには何もデータが格納されていない状態になります。UPDATE文の場合には、deletedテーブルに更新前のデータが格納され、insertedテーブルに更新後のデータが格納されます。

　例題3のサブクエリは、insertedテーブルのContactName列を検査して、ContactName列がNULLの行のCustomerIDを返します。トリガーの基となる操作がUPDATEの場合、複数行が同時に更新されることがあります。その場合も、トリガーの呼び出しは1回限りで、insertedテーブルに格納されているデータは対象となったすべての行になるので、注意が必要です。

　サブクエリで更新後のContactNameがNULLであった行に対して、UPDATE文でContactName列を更新しています。ContactNameは、「Contact For」という接頭辞にCompanyNameを追加するようにしました。この例では、ContactNameの文字数をテーブル定義の30文字以内にする処理は行っていません。

　例題4で、実行した結果のメッセージに「トリガーが実行されました。」と「（1件処理されました）」が2度ずつ表示されたことに気が付きましたか？　これは、トリガーの中でCustomersテーブルを更新したときに、トリガーが実行されたためです。この例のように、トリガーは連鎖で実行されます。ただし、32階層以上は連鎖されないようにSQL Serverで制限が掛かっています。トリガーの定義に、トリガーの対象テーブルと同じｓテーブルに対してINSERT、UPDATE、DELETEなどの操作を記述する場合は、IF UPDATE句などで制限をかけないと何度も同じトリガーが実行されることになりますので、注意が必要です。