sql 会话_在特定会话中禁用SQL Server中的触发器

sql 会话

This article will focus on the various ways to disable triggers in SQL Server so they won’t interfere with certain operations like bulk inserts.

本文将重点介绍在SQL Server中禁用触发器的各种方法，以使它们不会干扰诸如批量插入之类的某些操作。

问题 (Problem)

We had a situation where we need to load data into the tables in one of our database. There were a few triggers created on these tables for insert, update and delete and these triggers are set as “NOT FOR REPLICATION”. While loading data into the tables we do not want these triggers to be fired. We thought of disabling the triggers on the tables but this will disable them for other user transactions as well. We want them not to be fired only for my session from which I am executing data loading script. In this article let see how to disable triggers in SQL Server for a specific.

我们遇到一种情况，需要将数据加载到数据库之一的表中。 在这些表上创建了一些用于插入，更新和删除的触发器，这些触发器设置为“ NOT FOR REPLICATION”。 在将数据加载到表中时，我们不希望触发这些触发器。 我们曾考虑过禁用表上的触发器，但这也会对其他用户事务也禁用它们。 我们希望不要仅针对我正在执行数据加载脚本的会话触发它们。 在本文中，让我们看看如何在特定SQL Server中禁用触发器。

解 (Solution)

We need to understand about “NOT FOR REPLICATION” concept in SQL Server first.

我们首先需要了解SQL Server中的“ NOT FOR REPLICATION”概念。

NOT FOR REPLICATION can be used while creating triggers in SQL Server. This indicates that these triggers are not fired when replication agent do data modifications (INSERT / UPDATE / DELETE) on the table.

在SQL Server中创建触发器时可以使用NOT FOR REPLICATION。 这表明当复制代理对表进行数据修改（INSERT / UPDATE / DELETE）时，不会触发这些触发器。

Not only for triggers in SQL Server, this hint can also be used while creating foreign keys, identity columns and check constraints.

不仅对于SQL Server中的触发器，此提示还可以在创建外键，标识列和检查约束时使用。

In case of foreign keys, the foreign key check happens only when a user modify data on the table and foreign key validation does not happen when the replication agent sync’s these modifications to the other end (either to subscriber or both subscriber and publisher based on the type of replication configured)

如果是外键，则仅当用户修改表上的数据时才进行外键检查，并且当复制代理将这些修改同步到另一端（基于订阅者的订阅者或订阅者和发布者）时，不进行外键验证。配置的复制类型）

In case of identity columns, a new identity value is not generated when replication agent insert data into the table and the original identity value which is generated at source is used.

对于标识列，当复制代理将数据插入表中并且使用在源处生成的原始标识值时，不会生成新的标识值。

As the triggers in SQL Server were created with “NOT FOR REPLICATION” on these tables, if we insert data into the table as a normal user these triggers were fired. These triggers were not fired when replication agent inserts, deletes and updates data on the tables. So, pretending as a replication agent will do my job. (i.e. the triggers were fired for all user sessions and not fired for specific session which I logged in as replication agent.)

由于SQL Server中的触发器是在这些表上使用“ NOT FOR REPLICATION”创建的，因此，如果我们以普通用户的身份将数据插入表中，则会触发这些触发器。 当复制代理在表中插入，删除和更新数据时，不会触发这些触发器。 因此，假装为复制代理将完成我的工作。 （即，触发器是针对所有用户会话触发的，而不是针对我以复制代理身份登录的特定会话触发的。）

To illustrate this, I will create two sample tables “Emp” and “EmpJoining” and a trigger on “Emp” table which is fired to insert joining date when a new row is inserted in “Emp” table.

为了说明这一点，我将创建两个示例表“ Emp”和“ EmpJoining”，并在“ Emp”表上创建一个触发器，当在“ Emp”表中插入新行时，触发该触发器以插入加入日期。

CREATE TABLE Emp ( Empid int , name varchar (50) )
GO
CREATE TABLE EmpJoining ( Empid int, JD datetime)
GO
CREATE TRIGGER TR_INSEMPDETAILS ON Emp
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN
SET NOCOUNT ON
INSERT INTO EmpJoining
SELECT Empid , GETDATE() FROM inserted
END
GO

Now for example, I have the employee id, name and their joining date in other system which needs to be imported into above existing tables. In this case I do not want the trigger logic to be fired when I am inserting data into Emp table.

现在，例如，在其他系统中，我需要输入员工ID，姓名及其加入日期，该系统需要导入到现有表中。 在这种情况下，我不希望在将数据插入Emp表时触发触发器逻辑。

Basically, I have T-SQL insert script for both these tables which inserts employee id name in “Emp” table and their joining dates in “EmpJoining” table.

基本上，对于这两个表，我都有T-SQL插入脚本，它们在“ Emp”表中插入员工ID名称，并在“ EmpJoining”表中插入其加入日期。

Let us log in as normal user and execute T-SQL script to insert data into the table.

让我们以普通用户身份登录并执行T-SQL脚本以将数据插入表中。

以普通用户身份登录： (Login as normal user:)

Open SQL Server management studio. Now login to the SQL Server as a normal user and use the database where you created table. Use below script to insert data into Emp table and check whether trigger is fired or not.

打开SQL Server管理工作室。 现在，以普通用户身份登录到SQL Server，并使用创建表的数据库。 使用以下脚本将数据插入到Emp表中，并检查是否触发了触发器。

INSERT INTO Emp ( Empid , name ) values ( 1 , 'Adam' )
 
select * from Emp
 
select * from EmpJoining

we can see the trigger is fired and inserted data in EmpJoining table.

我们可以看到触发了触发器并在EmpJoining表中插入了数据。

We can check the session property using below script.

我们可以使用以下脚本检查session属性。

select SESSIONPROPERTY ( 'replication_agent' )

it will return zero if we login as normal user and it returns one if we login as replication agent.

如果以普通用户身份登录，它将返回零；如果以复制代理身份登录，则返回1。

作为复制代理登录的步骤： (Steps to Login as replication agent:)

Now let us login as replication agent and insert data using script.

现在，让我们以复制代理身份登录并使用脚本插入数据。

Close SQL server management studio and re-open it.

关闭SQL Server Management Studio，然后重新打开。

Input server, login, and password you want to use. click on options. Please refer to below image.

输入您要使用的服务器，登录名和密码。 单击选项。 请参考下图。

Navigate to additional connection parameters tab. In additional connection parameters table enter REPLICATION=TRUE in text box as shown in the below image.

导航到其他连接参数选项卡。 在其他连接参数表中，在文本框中输入REPLICATION = TRUE，如下图所示。

You can use your preferred login method either windows authentication or SQL Server authentication.

您可以使用Windows身份验证或SQL Server身份验证的首选登录方法。

This will login you as replication agent and any DML operation you perform on table will be executed as replication agent.

这将使您登录为复制代理，并且您在表上执行的任何DML操作都将作为复制代理执行。

Now execute below query to check whether you are logged in as a replication agent or not.

现在，执行以下查询以检查您是否以复制代理身份登录。

select SESSIONPROPERTY ( 'replication_agent' )

It should return 1.

它应该返回1。

Now insert few rows using below script.

现在，使用以下脚本插入几行。

INSERT INTO Emp ( Empid , name ) values ( 2 , 'Greg' )
select * from Emp
 
select * from EmpJoining

We can see the trigger is not fired as we logged in as replication agent and trigger is set as “NOT FOR REPLCIATION”.

我们可以看到以复制代理身份登录时触发器未触发，并且触发器设置为“ NOT FOR REPLCIATION”。

The data is inserted in only Emp table. Please refer to below image.

数据仅插入到Emp表中。 请参考下图。

Here in this case the trigger is not disabled and available for other user transactions which will fire trigger when there is an INSERT. The trigger is not fired only for the transaction which is executed as replication agent.

在这种情况下，触发器不会被禁用，并且可用于其他用户事务，当存在INSERT时将触发触发器。 仅针对作为复制代理执行的事务不会触发该触发器。

Use below query to check if your trigger is marked as “NOT FOR REPLCIATION” or not.

使用以下查询来检查触发器是否标记为“ NOT FOR REPLCIATION”。

SELECT name,is_not_for_replication FROM SYS.triggers

There are other ways to disable triggers in SQL Server for a session by handling code with conditions.

还有其他方法可以通过处理有条件的代码来禁用SQL Server中的会话触发器。

For example you can use CONTEXT_INFO() in the trigger code and return. Using CONTEXT_INFO() requires no special permissions.

例如，您可以在触发代码中使用CONTEXT_INFO（）并返回。 使用CONTEXT_INFO（）不需要特殊权限。

In this case, If the CONTEXT_INFO matches with value specified in the trigger the trigger returns and does not execute code below. Please refer to below code.

在这种情况下，如果CONTEXT_INFO与触发器中指定的值匹配，则触发器将返回并且不执行下面的代码。 请参考下面的代码。

CREATE TRIGGER TR_INSEMPDETAILS ON Emp
FOR INSERT
AS
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

In this case we must set the context_info value to 0x1256698456 before inserting data into the table “Emp”.

在这种情况下，我们必须在将数据插入表“ Emp”之前将context_info值设置为0x1256698456。

The following system views also store the context information, but querying these views directly requires SELECT and VIEW SERVER STATE permissions.

以下系统视图也存储上下文信息，但是直接查询这些视图需要SELECT和VIEW SERVER STATE权限。

• sys.dm_exec_requests
  sys.dm_exec_requests
• sys.dm_exec_sessions
  sys.dm_exec_sessions
• sys.sysprocesses
  sys.sysprocesses

Instead of T-SQL INSERT script, let us assume the data is .txt file or .csv file. We can use BCP UTILITY or BULK INSERT options to load data into tables without firing the triggers.

代替T-SQL INSERT脚本，让我们假设数据是.txt文件或.csv文件。 我们可以使用BCP UTILITY或BULK INSERT选项将数据加载到表中，而无需触发触发器。

使用BCP实用程序 (Using BCP utility)

We can use BCP utility to load bulk data into table without firing triggers in SQL Server. This method will work only for INSERTS on the table and do not fire triggers created “for insert” and “instead of insert”.

我们可以使用BCP实用程序将批量数据加载到表中，而无需在SQL Server中触发触发器。 此方法仅适用于表上的INSERTS，并且不会触发“用于插入”和“代替插入”创建的触发器。

By default, BCP utility does not fire triggers on loading data into tables. To force trigger execution, we should use -h “FIRE_TRIGGERS” in BCP while loading data into table.

默认情况下，BCP实用程序不会在将数据加载到表中时触发触发器。 为了强制执行触发器，在将数据加载到表中时，我们应该在BCP中使用-h“ FIRE_TRIGGERS”。

Below are sample tables used in this example.

以下是此示例中使用的示例表。

CREATE TABLE USERS
(ID int,
NAME varchar(50))
 
 
CREATE TABLE USERHIST
(USERID int,
CREATEDDATE datetime )
 
 
CREATE TRIGGER INS_USERS ON USERS
FOR INSERT
AS
BEGIN
INSERT INTO USERHIST
SELECT ID,GETDATE() FROM INSERTED
END

Please refer to the below example of default BCP to load data into “USERS” table which will not fire triggers in SQL Server. I have masked the original server, database names and login credentials.

请参考下面的默认BCP示例，以将数据加载到“ USERS”表中，该表不会在SQL Server中触发触发器。 我掩盖了原始服务器，数据库名称和登录凭据。

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

D：\ bcp.txt中的bcp [testdb] .dbo.USERS -T -c -S“ SERVERNAME” -Uusername -Ppassword

Please refer to the below example of BCP with hint “FIRE_TRIGGERS” to load data into USERS table which will fire triggers.

请参考以下带有提示“ FIRE_TRIGGERS”的BCP示例，将数据加载到USERS表中，这将触发触发器。

bcp [testdb].dbo.USERS in D:\bcp.txt -T -c -S”SERVERNAME” -Uusername -Ppassword -h “FIRE_TRIGGERS”

D：\ bcp.txt中的bcp [testdb] .dbo.USERS：-T -c -S“ SERVERNAME” -Uusername -Ppassword -h“ FIRE_TRIGGERS”

使用批量插入 (Using BULK INSERT)

This option also will work only for INSERTS on the table and do not fire triggers created “for insert” and “instead of insert”.

此选项也仅适用于表上的INSERTS，并且不会触发“用于插入”和“代替插入”创建的触发器。

By default, BULK INSERT does not fire triggers in SQL Server. We can force the execution of trigger by specifying “FIRE_TRIGGERS”

默认情况下，BULK INSERT不会在SQL Server中触发触发器。 我们可以通过指定“ FIRE_TRIGGERS”来强制执行触发器

I have test.txt file which as data with “,” as FIELDTERMINATOR.

我有test.txt文件，其中的数据以“，”作为FIELDTERMINATOR。

Please refer to below code for default BULK INSERT to load data into USERS table which will not fire triggers in SQL Server.

请参考下面的代码以获取默认的BULK INSERT，以将数据加载到USERS表中，该表不会在SQL Server中触发触发器。

BULK 
INSERT USERS
FROM 'D:\\test.txt' --location with filename
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Please refer to the below code with hint “FIRE_TRIGGERS” which will fire triggers in SQL Server when loading data from test.txt file.

请参考以下带有提示“ FIRE_TRIGGERS”的代码，当从test.txt文件加载数据时，它将触发SQL Server中的触发器。

BULK 
INSERT USERS
FROM 'D:\\test.txt' --location with filename
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRE_TRIGGERS
)
GO

In this article we discussed the behavior of triggers in SQL Server for below cases.

在本文中，我们讨论了以下情况下SQL Server中触发器的行为。

1. Login as normal user.
   以普通用户身份登录。
2. Login as replication agent and trigger is set as not for replication.
   以复制代理身份登录，并将触发器设置为不复制。
3. Using CONTEXT_INFO function in the trigger code.
   在触发代码中使用CONTEXT_INFO函数。
4. Using BCP default and with hint “FIRE_TRIGGER”
   使用默认的BCP并带有提示“ FIRE_TRIGGER”
5. Using BULK INSERT default and with hint “FIRE_TRIGGER”
   使用默认的BULK INSERT并带有提示“ FIRE_TRIGGER”

For points 3, 4 and 5 no matter if we create triggers with “NOT FOR REPLICATION” or not.

对于第3点，第4点和第5点，无论我们是否创建带有“ NOT FOR REPLICATION”的触发器。

翻译自: https://www.sqlshack.com/disabling-triggers-in-sql-server-for-a-specific-session/

sql 会话