トリガーの作成
トリガー は、データベースで特定のイベントが発生したときに自動的に実行される特別なストアド プロシージャです。 アプリケーション レベルのコードを必要とせずに、データの整合性を維持し、ビジネス ルールを適用し、データベース操作を自動化するトリガーを定義します。
トリガーの基礎を理解する
トリガーは、データベース内のデータ変更またはスキーマの変更に応答します。 トリガーを作成するときは、トリガーをアクティブにするイベントと、トリガーが実行するアクションを指定します。
トリガーは自動的に実行されます。 明示的に呼び出すストアド プロシージャとは異なり、トリガーは、 INSERT、 UPDATE、 DELETE、または DDL ステートメントに応答して起動します。 この自動実行により、すべてのデータ変更に一貫して適用する必要があるルールを適用するための強力な機能が提供されます。
SQL Server では、 DML (データ操作言語) トリガーと DDL (データ定義言語 ) トリガーの 2 つの主要なカテゴリのトリガーがサポートされています。 DML トリガーはテーブル データの変更に応答し、DDL トリガーは CREATE、 ALTER、 DROP ステートメントなどのスキーマの変更に応答します。
データ変更用の DML トリガーを作成する
DML トリガーは、テーブルまたはビューのデータ変更を監視し、対応します。 これらを、AFTER トリガーまたは INSTEAD OF トリガーとして定義します。
AFTER トリガーは、トリガーステートメントの完了後に実行されます。 データベースは最初にデータの変更を実行し、次にトリガー コードを実行します。 AFTER トリガーを使用して、変更の検証、関連テーブルの更新、またはログの変更を行います。
CREATE TRIGGER tr_UpdateInventory
ON Sales.OrderDetails
AFTER INSERT
AS
BEGIN
UPDATE Inventory.Products
SET QuantityInStock = QuantityInStock - i.Quantity
FROM Inventory.Products p
INNER JOIN inserted i ON p.ProductID = i.ProductID;
END;
INSTEAD OF トリガーは、元のデータ変更ステートメントを置き換えます。 トリガー コードは、 INSERT、 UPDATE、または DELETE 操作の代わりに実行されます。 INSTEAD OF トリガーを使用して、通常は直接変更を受け入れないビューを変更したり、複雑なビジネス ロジックを実装したりします。
CREATE TRIGGER tr_UpdateOrderView
ON Sales.OrderSummaryView
INSTEAD OF UPDATE
AS
BEGIN
UPDATE Sales.Orders
SET OrderStatus = i.OrderStatus,
ModifiedDate = GETDATE()
FROM Sales.Orders o
INNER JOIN inserted i ON o.OrderID = i.OrderID;
END;
DML トリガーを使用すると、 挿入 および 削除された 擬似テーブルにアクセスできます。 これらの一時テーブルには、影響を受ける行のコピーが格納されます。
INSERT 操作によって 挿入 されたテーブルが設定され、 DELETE 操作によって 削除された テーブルが設定され、 UPDATE 操作によって、両方のテーブルに 削除された 値と新しい値が挿入された古い値が設定 されます。
特定のイベントのトリガーを実装する
トリガーをアクティブにするデータ変更イベントを指定します。 1 つのトリガーは、トリガー定義で INSERT、 UPDATE、および DELETE を組み合わせることによって、複数のイベントに応答できます。
正確な制御を行うには、操作ごとに個別のトリガーを作成します。 この方法により、コードが簡略化され、トリガーの保守が容易になります。
CREATE TRIGGER tr_LogPriceChanges
ON Products.Catalog
AFTER UPDATE
AS
BEGIN
IF UPDATE(Price)
BEGIN
INSERT INTO Audit.PriceHistory (ProductID, OldPrice, NewPrice, ChangeDate)
SELECT d.ProductID, d.Price, i.Price, GETDATE()
FROM deleted d
INNER JOIN inserted i ON d.ProductID = i.ProductID
WHERE d.Price <> i.Price;
END;
END;
同時に、同じロジックが複数の操作に適用される場合に、イベントを結合できます。 たとえば、 INSERT、 UPDATE、 DELETEに応答する 1 つの監査トリガーを作成できます。
CREATE TRIGGER tr_AuditEmployeeChanges
ON HR.Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @Operation NVARCHAR(10);
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
SET @Operation = 'INSERT';
ELSE IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
SET @Operation = 'UPDATE';
ELSE
SET @Operation = 'DELETE';
INSERT INTO Audit.EmployeeLog (EmployeeID, Operation, ChangeDate)
SELECT COALESCE(i.EmployeeID, d.EmployeeID), @Operation, GETDATE()
FROM inserted i
FULL OUTER JOIN deleted d ON i.EmployeeID = d.EmployeeID;
END;
UPDATE()関数は、変更された列を特定するのに役立ちます。 特定のフィールドのみがビジネス ロジックにとって重要な場合は、不要な処理を回避するために、特定の列を確認します。
トリガーのベスト プラクティスを適用する
トリガーは、条件を満たすすべての操作で実行されるため、データベースのパフォーマンスに影響します。 トランザクションのスループットへの影響を最小限に抑えるために、効率的なトリガー コードを記述します。
トリガー ロジックに重点を置き、最小限に抑えることができます。 トリガー本体内で重要な操作のみを実行します。 複雑な操作や時間のかかる操作の場合は、イベントの詳細をログに記録し、別のジョブを使用して非同期的に処理することを検討してください。
CREATE TRIGGER tr_QueueLargeOrders
ON Sales.Orders
AFTER INSERT
AS
BEGIN
INSERT INTO Processing.OrderQueue (OrderID, TotalAmount, QueuedDate)
SELECT OrderID, TotalAmount, GETDATE()
FROM inserted
WHERE TotalAmount > 10000;
END;
トリガーの変更によって同じトリガーが再度起動される再帰的な操作は避けてください。
RECURSIVE_TRIGGERS データベース オプションを適切に設定し、無限ループを防ぐためにトリガーを設計します。
トリガー内でエラーを適切に処理します。 トランザクションの動作は、エラー処理によって異なります。 トリガーでエラーが発生し、それを処理しない場合、SQL Server はトリガーと元のステートメントの両方をロールバックします。
CREATE TRIGGER tr_ValidateOrderDate
ON Sales.Orders
AFTER INSERT, UPDATE
AS
BEGIN
IF EXISTS (SELECT * FROM inserted WHERE OrderDate > GETDATE())
BEGIN
THROW 50001, 'Order date cannot be in the future', 1;
END;
END;
トリガーを十分に文書化します。 他の開発者は、通常のデータベース操作中に目に見えない方法で実行されるため、トリガーが存在する理由と実行内容を理解する必要があります。
トリガーを作成して実装する方法を理解したら、他のプログラミング オブジェクトと統合して包括的なデータベース ソリューションを構築する方法を調べる準備ができました。