トリガーは大まかにベトナム語にトリガーとして変換されます。つまり、トリガーのようなもので、トリガーを引くアクションがあるとアクティブになります。

SQLでは、トリガーは、トリガーが作成されたテーブルのデータを変更しようとしたときに実行されるストアドプロシージャの一種です。通常のシステムストアドプロシージャとは異なり、トリガーを直接実行したり、パラメータを渡したり受け取ったりすることはできません。

各テーブルには通常、データを変更するUPDATE、INSERT、DELETEの3つの操作があります。また、そのようなアクションごとに、データを保持するためにテーブルに制約が設定される場合があります。現在、トリガーを使用することをお勧めします。

トリガーの種類

テーブルまたはビューでイベントが発生すると、トリガーが自動的に発生します。イベント言語は、DMLイベントとDDLイベントの2つのタイプに分けられます。 DMLイベントに関連付けられたトリガーは、DMLトリガーと呼ばれ、それ以外の場合はDDLトリガーと呼ばれます。

DMLトリガー

DMLトリガーは、DMLイベントの完了時、またはDMLイベントに代わって実行されます。これらのトリガーは、行が変更されたときに関連するテーブルへの変更をカスケードすることにより、参照整合性を保証します。

DMLトリガーには、主に3つのタイプがあります。

• INSERTトリガー
• UPDATEトリガー
• DELETEトリガー

挿入および削除されたテーブルの概要

DMLトリガーのSQLステートメントは、2つの特殊なタイプのテーブルを使用してデータベースを変更します。データが追加、更新、または削除されると、SQL Serverはこれらのテーブルを自動的に作成および管理し、クリップボードには元のデータと変更されたデータが次のように保存されます。

挿入されたテーブル

挿入されたテーブルには、INSERTおよびUPDATEステートメントを介して適用された行のコピーが格納されます。挿入または更新中に、挿入されたテーブルとトリガーテーブルの両方のテーブルに新しい行が追加されます。挿入されたテーブルの行は、トリガーテーブルの新しい行のコピーです。

削除されたテーブル

Deletedテーブルには、DELETEステートメントとUPDATEステートメントを介して適用された行のコピーが格納されます。 DELETEまたはUPDATEの実行中に、行はトリガーテーブルから削除され、削除されたテーブルに移動されます。

注：挿入されたテーブルと削除されたテーブルは一時的なものであり、メモリを格納し、特定のデータ変更の影響を確認し、DMLトリガーアクションの条件を設定します。 SQL Server 2019では、 AFTERトリガーの挿入および削除されたテーブルで、テキスト、nxtext、または画像列の参照を許可していません。

トリガーを挿入

INSERTトリガーは、新しいレコードがテーブルに挿入されたときに発生します。 INSERTトリガーは、入力が満たされていることを確認しますが、制約はテーブルで定義されています。

ユーザーがテーブルにレコードを入力すると、INSERTトリガーはこれらのレコードのコピーを挿入されたテーブルに保存します。次に、moiws値がテーブルの制約を満たしているかどうかを確認します。

レコードが有効な場合、INSERTトリガーは行をテーブルに追加します。そうでない場合、エラーメッセージを表示します。 INSERTトリガーは、CREATETRIGGERおよびALTERTRIGGERステートメントのINSERTキーワードを介して作成されます。

構文INSERT|UPDATE | DELETEトリガー：

 CREATE TRIGGER [schema_name.]trigger_name ON [schema_name.]table_name [WITH ENCRYPTION] [FOR INSERT] AS [IF UPDATE (column_name)] [{AND|OR} UPDATE (colum_name)] AS BEGIN <sql_statement> END;

例えば：

ステップ1-次の情報に従って2つのテーブルを作成します。

 CREATE TABLE Locations (LocationID int, LocName varchar(100)); CREATE TABLE LocationHistory (LocationID int, ModifedDate DATETIME);

ステップ2-Locations.tableに対してTRIGGER_INSERT_Locationsという名前のINSERTトリガーを作成します

CREATE TRIGGER TRIGGER_INSERT_Locations ON Locations FOR INSERT NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHistory SELECT LocationID , getdate() FROM inserted END;

次のステップでは、トリガーステートメントをテストします。

 insert into dbo.Locations values(1,'Ha Noi');

上記のトリガーでは、ロケーションのデータを挿入すると、ロケーションのデータに基づいてLocationHistoryデータが追加されます。

これには、新しいキーワードがあります。 レプリケーションはデータレプリケーションおよび配布テクノロジであり、SSMSで利用可能なディザスタリカバリソリューションの1つであり、2番目またはバックアップコピーを維持するのに非常に役立ちます。オブジェクトの部屋（テーブル、ビュー、保存された手順）およびデータベース。 NOT FOR REPLICATIONの意味は、レプリケーションの状況では使用されません。

トリガーの更新

UPDATEトリガーステートメントは、元のレコードを削除済みテーブルにコピーし、レコードが更新されると、新しいレコードを挿入済みテーブルにコピーします。次に、新しいレコードを評価して、値がテーブルの制約を満たしているかどうかを判断します。

新しい値が有効な場合、挿入されたテーブルのレコードがトリガーテーブルにコピーされます。ただし、新しい値が有効でない場合は、エラーメッセージがユーザーに出力されます。したがって、元のレコードは削除済みテーブルからトリガーテーブルにコピーされます。

UPDATEトリガーは、CREATETRIGGERまたはALTERTRIGGERコマンドのUPDATEキーワードを使用して作成できます。

例えば：

 CREATE TRIGGER TRIGGER_UPDATE_Locations ON Locations FOR UPDATE NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHistory SELECT LocationID, getdate() FROM inserted END; GO update Locations SET LocName = 'Ho Chi Minh' where [LocName] = 'Ha Noi';

上記のトリガー作成コマンドを実行し、データをLocationsテーブルに更新した後、Locationsテーブルのデータが更新されると、LocationHistoryテーブルに新しいデータが追加されます。

トリガーを削除する

削除トリガーは、ユーザーがテーブルから特定のレコードを削除することを制限するために作成されます

ユーザーがレコードを削除しようとすると、次の一連の実行が発生します。

• レコードはトリガーテーブルから削除され、削除済みテーブルに追加されます。
• データを削除するときに制約の一致をチェックします
• レコードに削除を妨げる制約がある場合、DELETEトリガーはエラーメッセージを表示します
• Deletedテーブルに保存されている削除済みレコードは、トリガーテーブルにコピーされます。

例えば：

 CREATE TRIGGER TRIGGER_DELETE_Locations ON Locations FOR DELETE NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHistory SELECT LocationID, getdate() FROM deleted END; GO DELETE FROM dbo.Locations WHERE LocName = 'Ho Chi minh';

削除を実行した後、トリガーはLocationsHistoryテーブルに新しいレコードを追加します。

トリガー後

AFTERトリガーは、INSERT、UPDATE、DELETE操作の完了後に実行されます。テーブルには、INSERT、UPDATE、DELETE操作ごとに複数のAFTERトリガーを定義できます。同じテーブルに複数のトリガーが作成される場合、ユーザーはトリガーの実行方法を自分で定義する必要があります。チェック制約が完了するとAFTERトリガーが実行されるため、 InsertedテーブルとDeletedテーブルが作成された後にトリガーが実行されます。

構文：

 CREATE TRIGGER <trigger_name> ON <table_name> [WITH ENCRYPTION] [FOR | AFTER] { [INSERT] [,] [UPDATE] [,] [DELETE] } AS BEGIN <sql_statement> END;

例えば：

 CREATE TRIGGER AFTER_INSERT_Locations ON Locations AFTER INSERT AS BEGIN INSERT INTO LocationHistory SELECT LocationID ,getdate() FROM inserted END;

実行後、Locationsが正常に挿入された後、LocationHistoryテーブルに挿入されます。トリガー後とトリガーオンの違いは、トリガーの実行時間のみです。

トリガーの代わりに

INSTEAD OFトリガーを使用すると、INSERT、UPDATE、DELETE操作の代わりに実行されます。テーブルまたはビューには、INSERT、UPDATE、DELETE操作ごとに1つのINSTEADOFトリガーのみを定義できます。

INSTEAD OFトリガーは、テーブルで制約チェックが実行される前に実行されます。このトリガーは、挿入されたテーブルと削除されたテーブルが作成された後に実行されます。

例えば：

 CREATE TRIGGER INSTEAD_OF_DELETE_Locations ON Locations INSTEAD OF DELETE AS BEGIN SELECT 'example instead trigger' as Message END; GO DELETE FROM dbo.Locations WHERE LocName='Ha Noi'

deleteステートメントが実行されると、INSTEAD OFトリガーが起動され、トリガー内のブロックが実行され、削除アクションは発生しません。

DMLトリガーの実行順序

SQL Serverを使用すると、ユーザーは、どのAFTERトリガーを最初に実行し、どのトリガーを後で実行するかを定義できます。最初のトリガーと最後のトリガーの間で呼び出されたすべてのAFTERトリガーには、明確な実行順序はありません。

sp_settriggerorderストアドプロシージャを使用して、DMLトリガードアの順序を定義します。

AFTER DMLトリガーの実行順序を決定するための構文：

 sp_settriggerorder [@triggername=] '[triggerschema.]triggername', [@order=]'value', [@stmmttype=]'statement_type'

例えば：

 EXEC sp_settriggerorder @triggername = 'TRIGGER_DELETE_Locations', @order='FIRST', @stmttype='DELETE'

DMLトリガーの定義を参照してください

sp_hepltext 。ストアドプロシージャを介してトリガー定義を表示するには
構文：

 sp_helptext '<trigger_name>';

例えば：

 sp_helptext TRIGGER_DELETE_Locations

ALTERDMLトリガー

トリガーを編集するには、次の2つの方法があります。

• DROPおよびRECREATEは、新しいパラメーターで再度トリガーされます
• ALTERTRIGGERを使用してトリガーを変更します。構文

構文：

 ALTER TRIGGER <trigger_name> ON {<table_name>|<view_name>} [WITH ENCRYPTION] {FOR|ALTER|INSTEAD OF} { [INSERT] [,] [UPDATE] [,] [DELETE] } AS <sql_statement>

例えば：

 ALTER TRIGGER TRIGGER_UPDATE_Locations ON Locations WITH ENCRYPTION FOR INSERT AS IF 'Ho Chi Minh' IN (SELECT LocName FROM inserted) BEGIN PRINT 'Location can not be updated' ROLLBACK TRANSACTION END;

DROPDMLトリガー

構文：

 DROP TRIGGER <DML_trigger_name> [,...n]

例えば：

 DROP TRIGGER TRIGGER_UPDATE_Locations

DDLトリガー

データ定義言語（DDL）トリガーは、CREATE、ALTER、DROPなどのDDLイベントがデータベースまたはサーバーで発生すると、ストアドプロシージャを実行します。 DDLトリガーは、DDLイベントが完了したときにのみ動作できます。
DDLトリガーを使用して、データベーススキーマの変更を防ぐことができます。
DDLトリガーは、スキーマメッセージに基づいて、イベントを呼び出したり、メッセージを表示したりできます。 DDLトリガーは、データベース層またはサーバー層のいずれかで定義されます。

構文：

 CREATE TRIGGER <trigger_name> ON {ALL SERVER | DATABASE} [WITH ENCRYPTON] {FOR | AFTER} <event_type> } AS <sql_statement>

例えば：

 CREATE TRIGGER Secure ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable trigger scure to drop or alter table' ROLLBACK;

このコードを使用すると、DROPTABLEまたはALTERTABLEステートメントに対してDDLトリガーが生成されます。

DDLトリガーの範囲

DDDLトリガーは、現在のデータベースまたはサーバーで実行されるsqlステートメントによって呼び出されます。たとえば、データベースのCREATETABLEイベントで実行されるCREATETABLEステートメントに対してDDLトリガーが作成されます。
DDLトリガーにより、CREATELOGINステートメントがサーバーのLOGINイベントで実行されます。

DDLトリガーのスコープは、トリガーがデータベースイベントに対して実行されるか、サーバーイベントに対して実行されるかによって異なります。トリガーは、次の2つのタイプに分けられます。

データベーススコープのDDLトリガー

データベーススコープのDDLトリガーは、データベーススキーマ編集イベントによって呼び出されます。これらのトリガーはデータベースに保存され、クリップボードに関連付けられているものを除いて、DDLイベントで実行されます。

サーバースコープのDDLトリガー

サーバースコープのDDLトリガーは、サーバーレイヤーのDDLイベントによって呼び出されます。これらのトリガーはデータベースマスターに保存されます。

ネストされたトリガー

トリガーが別のトリガーを開始するアクションを実装する場合、DDLトリガーとDMLトリガーの両方にこの概念があります。 DDLおよびDMLトリガーは、最大32層までネストできます。トリガーが別のトリガーがあるテーブルを編集する場合、2番目のトリガーが初期化され、次に3番目のトリガーを呼び出します。

ネストされたトリガーの実行が許可されている場合、トリガーは無限ループとして順次開始され、最後のフェザーに到達すると、トリガーは終了します。

ネストされたトリガーは、前のアクションの影響を受けたレコードのアーカイブやバックアップなどの機能を処理するために使用できます。

ユーザーは、 sp_configureストアドプロシージャを介してネストされたトリガーオプションを0またはOFFに構成することにより、ネストされたトリガーを無効にできます。デフォルト設定では、ネストされたトリガーが許可されます。

例えば：

 GO CREATE TRIGGER Employee_Deletion ON HumanResources.Employee AFTER DELETE AS BEGIN PRINT 'Deletion will affect EmployeePayHistory table' DELETE FROM EmployeePayHistory WHERE BusinessEntityID IN (SELECT BusinessEntityID FROM deleted) END; GO CREATE TRIGGER Deletion_Comnfirmation ON HumanResources.EmployeePayHistory AFTER DELETE AS BEGIN PRINT 'Employe details successfully deleted from EmployeePayHistory table' END; DELETE FROM HumanResources.EmployeePayHistory WHERE BusinessEntityID=1

UPDATE（）関数

UPDATE（）関数は、テーブルのビューまたは列に対してUPDATEまたはINSERTが実行されたかどうかを判別するブール値を返します。

UPDATE（）関数は、UPDATEまたはINSERTトリガーの本体内の任意の場所で使用して、トリガーが何らかのアクションを実行する必要があるかどうかを確認できます。

構文：

 UPDATE (column);

例えば：

 CREATE TRIGGER Accounting ON Production.TransactionHistory AFTER UPDATE AS IF (UPDATE(TransactionID) OR UPDATE(ProductID)) BEGIN RAISEERROR(5009,16,10) END; GO

1つのセッションで複数の行（レコード）を処理する

ユーザーがDMLトリガーのコードを作成すると、ステートメントはトリガーに依存して単一のステートメントをトリガーします。 1つのステートメントで、データ内の複数のレコードが更新されます。これはDELETEおよびUPDATEトリガーの一般的な動作であり、多くの場合、ステートメントは複数のレコードに影響を与えます。 INSERTステートメントは通常1レコードしか追加しないため、INSERTトリガーの動作はあまり一般的ではありません。

DMLトリガーの機能が自動的に呼び出して、テーブルの値の要約を再計算し、結果を別のテーブルに格納する場合、複数行の考慮事項が重要です。

たとえば、単一行の挿入合計結果を格納する状況を考えてみます。

 USE AdventureWorks2019; GO CREATE TRIGGER PODetails ON Purchasing.PurchaseOrderDetail AFTER INSERT AS UPDATE PurchaseOrderHeader SET SubTotal = Subtotal + LineTotal FROM INSERTED WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID;

以下のコードでは、小計は、単一行の挿入コマンド操作ごとに計算および保存されます。
次のコードは、複数行または単一行の状況で実行されます。

 USE AdventureWorks2019; GO CREATE TRIGGER PODetailMultiple ON Purchasing.PurchaseOrderDetail AFTER INSERT AS UPDATE Purchasing.PurchaseOrderHeader SET Subtotal = SubTotal + (SELECT SUM(LineTotal) FROM inserted WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID) WHERE PurchaseOrderHeader.PurchaseOrderID IN (SELECT PurchaseOrderID FROM inserted);

ログオントリガー

これらのトリガーは、SQLServerのLOGONイベントに応答して起動します。ログオントリガーは、LOGONイベントでストアドプロシージャをトリガーします。このイベントは、SQLServerのインスタンスとのユーザーセッションが確立されたときにキャッチされます。ログオントリガーは、ログイン認証が終了した後、ユーザーセッションが実際に確立される前に発生します。検証が失敗すると、ログオントリガーは実行されません。

Loggonトリガーはサーバー層で作成され、次の場合に役立ちます。

• ログインアクティビティを確認してください
• ログインアクションを制御する

たとえば、次のようにadventureworksで作成されたLOGONEvent列とLogintime列を使用してLoginActivityテーブルを作成します。

 CREATE TABLE LoginActivity (LOGONEvent XML, Logintime datetime)

ユーザーがログインしたときに、ログイン履歴をLoginActivityテーブルに挿入するようにトリガーを作成します。

 CREATE TRIGGER [track_login] ON ALL SERVER FOR LOGON AS BEGIN INSERT INTO LoginActivity SELECT EVENTDATA(), GETDATE() END;

注： LOGONトリガーを使用するときは十分に注意してください。論理エラーTRIGGERにログインできない場合があり、接続するにはDACを開く必要があるため、実行する前に慎重にテストしてください。

トリガーパフォーマンス

実際には、トリガーはコストをかけず、代わりに非常にうまく機能します。ただし、多くのパフォーマンスの問題は、トリガー内に存在するロジックによって引き起こされる可能性があります。トリガーがポインターを作成し、多くの行を反復すると、実行速度が低下します。

同様に、トリガーが複数のSQLステートメントを実行して、挿入されたテーブルと削除されたテーブルで別々のテーブルが作成されない状況を考えてみます。結果が再び返され、トリガーを使用しない場合よりもクエリの速度が低下します。

したがって、トリガーロジックを単純に保ち、テーブル内のステートメントやシステムパフォーマンスを低下させるさまざまなタスクを実行するときにポインターを使用しないようにすることをお勧めします。