SQL Server で特定のセッションのトリガーを無効にする

この記事では、SQL Server でトリガーを無効にして、一括挿入などの特定の操作に干渉しないようにするさまざまな方法を取り上げます。

Problem
Solution
レプリケーションエージェントとしてログインする手順：
Using BCP utility
Using BULK INSERT

Problem

あるデータベースにあるテーブルにデータをロードしなければならない状況がありました。これらのテーブルには、挿入、更新、および削除のためのいくつかのトリガーが作成されており、これらのトリガーは “NOT FOR REPLICATION” として設定されています。テーブルにデータをロードしている間、これらのトリガーを起動させたくありません。テーブルのトリガーを無効にすることを考えましたが、これは他のユーザトランザクションでも同様にトリガーを無効にしてしまいます。データローディングスクリプトを実行している自分のセッションだけで、トリガーを起動させないようにしたいのです。この記事では、SQL Server のトリガーを特定のユーザーに対して無効にする方法を説明します。

Solution

まず、SQL Server の “NOT FOR REPLICATION” コンセプトについて理解する必要があります。これは、レプリケーションエージェントがテーブル上でデータ変更（INSERT / UPDATE / DELETE）を行ったときに、これらのトリガーが起動しないことを示します。

SQL Server のトリガーだけではなく、このヒントは外部キー、ID 列、チェック制約の作成時にも使用することができます。

外部キーの場合、ユーザーがテーブルのデータを変更したときのみ外部キーのチェックが行われ、レプリケーションエージェントがこれらの変更を他方のエンドに同期するとき（構成されたレプリケーションのタイプに基づいて、サブスクライバーまたはサブスクライバーとパブリッシャーの両方に）外部キー検証は行われません。

SQL Server のトリガーは、これらのテーブルに “NOT FOR REPLICATION” で作成されているため、通常のユーザーとしてテーブルにデータを挿入すると、これらのトリガーが起動されました。これらのトリガーは、レプリケーションエージェントがテーブルにデータを挿入、削除、および更新しても、起動されませんでした。そこで、レプリケーション・エージェントのふりをすることで、私の仕事をこなすことができます。 (つまり、トリガはすべてのユーザ・セッションで起動され、レプリケーション・エージェントとしてログインした特定のセッションでは起動されませんでした。)

これを説明するために、2つのサンプルテーブル “Emp” と “EmpJoining” を作成し、 “Emp” テーブルに新しい行が挿入されると結合日を挿入するために起動されるトリガを “Emp” テーブルに設定したいと思います。

CREATE TABLE Emp ( Empid int , 名前 varchar (50) )

CREATE TABLE EmpJoining ( Empid int, JD datetime)

CREATE TRIGGER TR_INSEMPDETAILS ON Emp

FOR INSERT

NOT FOR REPLICATION

BEGIN

SET NOCOUNT ON

INSERT INTO EmpJoining

SELECT Empid .JD

INSERT TRIGGER ORINS

FOR INSERT

NOT FOR REPLICATION

BEGIN

SET NOCOUNT ON

SELECT Empid , GETDATE() FROM inserted

END

さて例えば、私は他のシステムで従業員のID、名前とその入社日を持っており、上記の既存のテーブルへのインポートが必要である。

基本的に、私はこれらのテーブルの両方にT-SQL挿入スクリプトを持っており、従業員ID、名前を「Emp」テーブルに、その入社日を「EmpJoining」テーブルに挿入します。 SQL Serverに通常のユーザーでログインし、テーブルを作成したデータベースを使用します。以下のスクリプトを使用して、Empテーブルにデータを挿入し、トリガーが発生するかどうかを確認します。

INSERT INTO Emp ( Empid , name ) values ( 1 , ‘Adam’ )

select * from Emp

select * from EmpJoining

トリガーが起動しEmpJoiningテーブルへデータが挿入されていることが確認できます。

以下のスクリプトを使用して、セッションプロパティを確認することができます。

1	select SESSIONPROPERTY ( ‘replication_agent’ )

通常ユーザーとしてログインすると0、複製エージェントとしてログインすると1を返すことになります。

レプリケーションエージェントとしてログインする手順：

では、レプリケーションエージェントとしてログインし、スクリプトを使用してデータを挿入してみましょう。

使用するサーバー、ログイン名、パスワードを入力し、[オプション]をクリックします。

追加接続パラメータタブに移動します。

Windows認証またはSQL Server認証のいずれかを使用して、お好みのログイン方法を使用することができます。

ここで以下のクエリを実行し、レプリケーションエージェントとしてログインしているかどうかを確認します。

1	select SESSIONPROPERTY ( ‘replication_agent’ )

これは1が返されるはずです。

では、以下のスクリプトを使って数行を挿入してください。

INSERT INTO Emp ( Empid , name ) values ( 2 , ‘Greg’ )

select * from Emp

select * from EmpJoining

レプリケーションエージェントとしてログインしているため、トリガーが起動しないことが確認できますし、トリガーの設定は “NOT FOR REPLCIATION” とされています。

データは Emp テーブルにのみ挿入されます。

この場合、トリガは無効ではなく、INSERTがあったときにトリガを起動する他のユーザートランザクションで利用可能です。トリガは、レプリケーションエージェントとして実行されるトランザクションに対してのみ起動されません。

以下のクエリを使用して、トリガが “NOT FOR REPLCIATION” としてマークされているかどうかを確認します。

1	SELECT name,is_not_for_replication FROM SYS.triggers

SQL Serverでセッションに対して、条件を指定してコードを処理してトリガーを無効にするには他の方法があります。

たとえば、トリガーコードで CONTEXT_INFO() を使用して、返すことができます。この場合、CONTEXT_INFOがトリガーで指定された値と一致すれば、トリガーは戻り、以下のコードは実行されません。以下のコードを参照してください。

CREATE TRIGGER TR_INSEMPDETAILS ON Emp

FOR INSERT

BEGIN

SET NOCOUNT on

DECLARE @CONT_INFO VARBINARY(128)

SELECT @CONT_INFO = CONTEXT_INFO()

IF @CONT_INFO = 0x1256698456

RETURN

INSERT INTO EmpJoining

SELECT Empid , GETDATE() FROM inserted

END

この場合、テーブル “Emp” にデータを挿入する前に context_info 値を 0x1256698456 に設定する必要があります。

以下のシステムビューもコンテキスト情報を保存しますが、これらのビューを直接照会するには、SELECT および VIEW SERVER STATE 権限が必要です。

sys.dm_exec_requests
sys.dm_exec_sessions
sys.sysprocesses

T-SQL INSERT スクリプトではなく、データを .txt ファイルまたは .csv ファイルとして想定してみましょう。 1132>

Using BCP utility

BCP ユーティリティを使用して、SQL Server でトリガーを起動せずにテーブルにバルクデータをロードすることが可能です。この方法は、テーブルへの INSERTS に対してのみ機能し、「挿入のために」および「挿入の代わりに」作成されたトリガーを起動しません。

デフォルトでは、BCP ユーティリティはテーブルへのデータのロード時にトリガーを起動しないようになっています。トリガーを強制的に実行させるには、テーブルにデータをロードしている間にBCPで-h “FIRE_TRIGGERS “を使用する必要があります。

CREATE TABLE USERS

(ID int。),

NAME varchar(50))

CREATE TABLE USERHIST

(USERID int,

CREATEDDATE datetime )

CREATE TRIGGER INS_USERS ON USERS

FOR INSERT

BEGIN

INSERT INTO USERHIST

SELECT ID.USER_DATE

CREATE TRIGGER USERHIST

SELECT ID.USERID

BEGIN

INSERT INTO USERHIST

DATE

END

SQL Server でトリガーを起動しない “USERS” テーブルにデータをロードするデフォルト BCP の例を以下に示しますので参照してください。

bcp .dbo.USERS in D:\bcp.txt -T -c -S “SERVERNAME” -Uusername -Ppassword

BCP にヒント “FIRE_TRIGGERS” を指定して USERS テーブルにデータをロードし、トリガーを発生させる例も以下に紹介します。USERS in D:\bcp.txt -T -c -S “SERVERNAME” -Uusername -Ppassword -h “FIRE_TRIGGERS”

Using BULK INSERT

このオプションもテーブルへの INSERTS に対してのみ機能するので「挿入用」および「挿入以外」作成したトリガーを起動させません。 FIRE_TRIGGERS “を指定することで、トリガーを強制的に実行させることができます。

FIELDTERMINATORとして”, “を含むデータを含むtest.txtファイルがあります。

USERSテーブルにデータをロードし、SQL Serverでトリガーを起動しないデフォルトBULK INSERTについては以下のコードを参照して下さい。

bulk

insert users

from ‘d.D.’:\test.txt’ –location with filename

WITH

(

FIELDTERMINATOR = ‘,’,

ROWTERMINATOR = ‘\n’

以下のコードにヒント “FIRE_TRIGGERS” を記述すると test.LD からデータを読み込む際に SQL Server でトリガーを起動しますから参照してください。を参照してください。

bulk

insert users

from ‘d.D.’:\test.txt’ –location with filename

WITH

(

FIELDTERMINATOR = ‘,’,

ROWTERMINATOR = ‘\n’,

FIRE_TRIGGERS

この記事では、SQL Serverにおけるトリガーの動作について、以下のケースを想定して説明しました。

通常のユーザーとしてログインします。
レプリケーションエージェントとしてログインし、トリガはレプリケーション用でないとして設定されています。
トリガコードで CONTEXT_INFO 関数を使用しています。
BCP のデフォルトとヒント “FIRE_TRIGGER” の使用
BULK INSERT のデフォルトとヒント “FIRE_TRIGGER” の使用

ポイント 3, 4, 5 は “NOT FOR REPLICATION” で作成するかどうかに関係なく、トリガーを使用することができました。

著者
最近の投稿

SQL Server DBA、開発者でSQL Serverの管理、開発、パフォーマンスチューニング、モニタリングに良い経験をしています。高可用性および災害復旧技術

Ranga Babuの最新の投稿 (全て見る)

Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases – October 24, 2019年
Collate SQL コマンドの概要 – 2019年10月22日
失った SA パスワードを回復する – 2019年9月20日

SQLShack

Problem