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.
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.
Solution
We need to understand about “NOT FOR REPLICATION” concept in SQL Server first.
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.
Not only for triggers in SQL Server, this hint can also be used while creating foreign keys, identity columns and check constraints.
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.)
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
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.
Let us log in as normal user and execute T-SQL script to insert data into the table.
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.
|
1 2 3 4 5 |
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.
We can check the session property using below script.
|
1 |
select SESSIONPROPERTY ( 'replication_agent' ) |
it will return zero if we login as normal user and it returns one if we login as replication agent.
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.
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.
You can use your preferred login method either windows authentication or SQL Server authentication.
This will login you as replication agent and any DML operation you perform on table will be executed as replication agent.
Now execute below query to check whether you are logged in as a replication agent or not.
|
1 |
select SESSIONPROPERTY ( 'replication_agent' ) |
It should return 1.
Now insert few rows using below script.
|
1 2 3 4 |
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”.
The data is inserted in only Emp table. Please refer to below image.
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.
Use below query to check if your trigger is marked as “NOT FOR REPLCIATION” or not.
|
1 |
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.
For example you can use CONTEXT_INFO() in the trigger code and return. Using CONTEXT_INFO() requires no special permissions.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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”.
The following system views also store the context information, but querying these views directly requires SELECT and VIEW SERVER STATE permissions.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- 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.
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”.
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.
Below are sample tables used in this example.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 [testdb].dbo.USERS in D:\bcp.txt -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.
bcp [testdb].dbo.USERS in D:\bcp.txt -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”.
By default, BULK INSERT does not fire triggers in SQL Server. We can force the execution of trigger by specifying “FIRE_TRIGGERS”
I have test.txt file which as data with “,” as FIELDTERMINATOR.
Please refer to below code for default BULK INSERT to load data into USERS table which will not fire triggers in SQL Server.
|
1 2 3 4 5 6 7 8 9 |
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.
|
1 2 3 4 5 6 7 8 9 10 |
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.
- Login as normal user.
- Login as replication agent and trigger is set as not for replication.
- Using CONTEXT_INFO function in the trigger code.
- Using BCP default and with hint “FIRE_TRIGGER”
- Using BULK INSERT default and with hint “FIRE_TRIGGER”
For points 3, 4 and 5 no matter if we create triggers with “NOT FOR REPLICATION” or not.
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019