INSTEAD OF トリガの式と計算列

[アーティクル]
12/16/2008

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

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

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

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

CREATE VIEW ExpressionView
AS
SELECT *, GETDATE() AS TodaysDate
FROM AdventureWorks.HumanResources.Employee

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

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

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

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

CREATE TABLE SampleTable
     (
      PriKey    int,
      FirstName nvarchar(20),
      LastName  nvarchar(30)
     )
GO
CREATE VIEW 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 ConcatView
INSTEAD OF INSERT
AS
BEGIN

   INSERT INTO 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 BaseTable
  (
   PrimaryKey   int PRIMARY KEY,
   ColumnB      int,
   ColumnC      decimal(19,3)
  )

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

CREATE TRIGGER SquareTrigger ON SquareView
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO 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 トリガの式と計算列

参照

概念

ヘルプおよび情報

その他のリソース