[アーティクル]
10/31/2012

INSTEAD OF トリガーの式と計算列

ビューの選択リストには、列名のみで構成される単純式以外の式を含めることができます。このビューの INSTEAD OF トリガーは、INSERT や UPDATE に指定した値からベーステーブルの列に設定する必要がある値を正確に判断するロジックを備えている必要があります。たとえば、次のような式を設定できます。

定数やある種の関数のように、どのテーブルのどの列にもマップされないビューの式
2 つ以上の列の文字列を連結して形成される複合式など、複数の列にマップされるビューの式
関数内の列を参照するなど、1 つのベーステーブルの列の値を変換するビューの式

ベーステーブルの計算列を参照する単純式のビュー列にも同じことが適用されます。計算列を定義する式には、ビューの選択リスト内の高度な複合式と同じ形式を指定できます。

ビューの選択リストには、次のような、どのベーステーブルの列にもマップされない式を含めることができます。

CREATE VIEW dbo.ExpressionView
AS
SELECT BusinessEntityID, JobTitle, GETDATE() AS TodaysDate
FROM AdventureWorks2008R2.HumanResources.Employee;

TodaysDate 列は、テーブルのどの列にもマップされませんが、SQL Server では、TodaysDate 列を inserted テーブルに作成し、ExpressionView に定義された INSTEAD OF トリガーに渡す必要があります。ただし、inserted.TodaysDate 列は NULL 値を許容するので、ExpressionView を参照する INSERT ステートメントは、この列に値を指定する必要はありません。式がテーブルの列にマップされないため、このトリガーはこの列の INSERT で指定されたすべての値を無視できます。

この方法を、他の列に依存しない結果を作成するベーステーブルの計算列を参照するビューの単純式に、次のように適用できます。

CREATE TABLE dbo.ComputedExample
   (
    PrimaryKey    int PRIMARY KEY,
    ComputedCol   AS SUSER_NAME()
   );

一部の複合式は、次のように、複数の列にマップされます。

CREATE TABLE dbo.SampleTable
     (
      PriKey    int,
      FirstName nvarchar(20),
      LastName  nvarchar(30)
     );
GO
CREATE VIEW dbo.ConcatView
AS
SELECT PriKey, FirstName + ' ' + LastName AS CombinedName
FROM SampleTable;

ConcatView の式 CombinedName には、FirstName 値と LastName 値を連結した値が含まれます。INSTEAD OF INSERT トリガーが ConcatView に定義されている場合は、INSERT ステートメントで CombinedName 列に値を指定する方法についての規則を設定し、そのトリガーが FirstName 列に挿入する文字列の一部と LastName 列に挿入する文字列の一部を決定できるようにする必要があります。規則 'first_name;last_name' を使用して、INSERT ステートメントで CombinedName の値を指定するという規則を選択すると、次のように、このトリガーは INSERT 処理に成功します。

CREATE TRIGGER InsteadSample on dbo.ConcatView
INSTEAD OF INSERT
AS
BEGIN

   INSERT INTO dbo.SampleTable
      SELECT PriKey,
         -- Pull out the first name string.
         SUBSTRING(
            CombinedName,
            1,
            (CHARINDEX(';', CombinedName) - 1)
            ),
         -- Pull out the last name string.
         SUBSTRING(
            CombinedName,
            (CHARINDEX(';', CombinedName) + 1),
            DATALENGTH(CombinedName) - (CHARINDEX(';', CombinedName) + 1)
            )
      FROM inserted
END;

これと同様のロジックが、複合式を含む計算列を参照する単純式のビュー列の処理にも必要です。

ビューの式の中には、たとえば、数学的な演算を実行して、または関数へのパラメーターとして列を使用して、ベーステーブルの列の値を変換できるものもあります。この場合、INSTEAD OF INSERT トリガーでのロジックの使用方法には、次の 2 つがあります。

すべての INSERT ステートメントでベーステーブルに指定される基になる値を指定し、トリガーのロジックによって、その値を inserted テーブルからベーステーブルに移動するという規則を作成します。

すべての INSERT ステートメントで、そのビューの SELECT が返すと予想される値を指定するという規則を作成します。ただし、この場合トリガーのロジックは、操作を逆にする必要があります。次に例を示します。

CREATE TABLE dbo.BaseTable
  (
   PrimaryKey   int PRIMARY KEY,
   ColumnB      int,
   ColumnC      decimal(19,3)
  );

CREATE VIEW dbo.SquareView AS
SELECT PrimaryKey, ColumnB,
       -- Square the value of ColumnC
       SQUARE(ColumnC) AS SquareC
FROM BaseTable;

CREATE TRIGGER SquareTrigger ON dbo.SquareView
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO dbo.BaseTable
     SELECT PrimaryKey, ColumnB,
             -- Perform logical inverse of function in view.
             SQRT(SquareC)
     FROM inserted
END;

加算や減算のような数学演算を使用する複合式などでは、対象のベーステーブルの列に正確な値を作成するためにトリガーが使用する値を、ユーザーが指定できない場合があります。たとえば、ビューの選択リストに IntColA + IntColB AS AddedColumns という式が含まれているとします。inserted.AddedColumns の値が 10 の場合、どのような意味になるでしょうか。3 + 7、2 + 8、または 5 + 5 でしょうか。inserted.AddedColumns の値だけでは、IntColA と IntColB がどの値なのかわかりません。

このような場合、トリガーをコード化して、代替ソースの情報を使用し、ベーステーブルの列に設定する値を決めることができます。INSTEAD OF トリガーを含むビューの場合は、ビューの選択リストに必要な情報を格納して、トリガーによって変更されるベーステーブルの NULL 値を許容しないすべての列に値を作成できるようにする必要があります。すべてのデータを inserted テーブルから直接取得する必要はありません。場合によっては、inserted テーブルの値をキー値にして、トリガーがそれを使用して他のベーステーブルから関連データを取得することができます。

INSTEAD OF トリガーの式と計算列

関連項目

概念

その他のリソース