Este artículo se centrará en las diversas formas de desactivar los triggers en SQL Server para que no interfieran con ciertas operaciones como inserciones masivas.
Problema
Tenemos una situación en la que necesitamos cargar datos en las tablas de una de nuestras bases de datos. Hubo algunos desencadenantes creados en estas tablas para insertar, actualizar y eliminar y estos desencadenantes se establecen como «NO PARA REPLICAR». Mientras se cargan los datos en las tablas no queremos que estos triggers se disparen. Pensamos en desactivar los disparadores en las tablas, pero esto los desactivará también para otras transacciones de usuario. Queremos que no se disparen sólo para mi sesión desde la que estoy ejecutando el script de carga de datos. En este artículo vamos a ver cómo desactivar los desencadenantes en SQL Server para un specific.
Solution
Tenemos que entender acerca de «NO PARA REPLICACIÓN» concepto en SQL Server first.
NO PARA REPLICACIÓN se puede utilizar al crear desencadenantes en SQL Server. Esto indica que estos desencadenantes no se disparan cuando el agente de replicación hace modificaciones de datos (INSERT / UPDATE / DELETE) en la tabla.
No sólo para los desencadenantes en SQL Server, esta pista también se puede utilizar durante la creación de claves externas, columnas de identidad y restricciones de verificación.
En el caso de las claves foráneas, la comprobación de la clave foránea ocurre sólo cuando un usuario modifica los datos en la tabla y la validación de la clave foránea no ocurre cuando el agente de replicación sincroniza estas modificaciones con el otro extremo (ya sea con el suscriptor o con el suscriptor y el publicador, según el tipo de replicación configurada)
En el caso de las columnas de identidad, no se genera un nuevo valor de identidad cuando el agente de replicación inserta datos en la tabla y se utiliza el valor de identidad original que se genera en el origen.
Como los triggers en SQL Server fueron creados con «NOT FOR REPLICATION» en estas tablas, si insertamos datos en la tabla como un usuario normal estos triggers se disparan. Estos triggers no se disparan cuando el agente de replicación inserta, borra y actualiza datos en las tablas. Por lo tanto, fingir como agente de replicación hará mi trabajo. (es decir, los triggers se dispararon para todas las sesiones de usuario y no se dispararon para la sesión específica que inicié como agente de replicación.)
Para ilustrar esto, voy a crear dos tablas de ejemplo «Emp» y «EmpJoining» y un trigger en la tabla «Emp» que se dispara para insertar la fecha de unión cuando se inserta una nueva fila en la tabla «Emp».
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE TABLE Emp ( Empid int , name varchar (50) )
GO
CREAR TABLA EmpJoining ( Empid int, JD datetime)
GO
CREAR 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
|
Ahora, por ejemplo, tengo el id del empleado, el nombre y su fecha de incorporación en otro sistema que necesita ser importado en las tablas existentes arriba. En este caso no quiero que la lógica de disparo se disparó cuando estoy insertando datos en la tabla Emp.
Básicamente, tengo T-SQL insertar secuencia de comandos para estas dos tablas que inserta el nombre de identificación de los empleados en la tabla «Emp» y sus fechas de unión en la tabla «EmpJoining».
Vamos a iniciar sesión como usuario normal y ejecutar la secuencia de comandos T-SQL para insertar datos en la tabla.
Login como usuario normal:
Abrir SQL Server management studio. Ahora inicie sesión en el servidor SQL como un usuario normal y utilice la base de datos donde creó la tabla. Utilice la siguiente secuencia de comandos para insertar datos en la tabla Emp y comprobar si el gatillo se dispara o no.
|
1
2
3
4
5
|
INSERT INTO Emp ( Empid , name ) values ( 1 , ‘Adam’ )
select * from Emp
select * from EmpJoining
|
podemos ver que se dispara el trigger y se insertan los datos en la tabla EmpJoining.
Podemos comprobar la propiedad de sesión utilizando el siguiente script.
|
1
|
select SESSIONPROPERTY ( ‘replication_agent’ )
|
devolverá cero si entramos como usuario normal y devolverá uno si entramos como agente de replicación.
Pasos para iniciar sesión como agente de replicación:
Ahora vamos a iniciar sesión como agente de replicación e insertar los datos utilizando el script.
Cerrar SQL server management studio y volver a abrirlo.
Introduzca el servidor, el nombre de usuario y la contraseña que desea utilizar. haga clic en las opciones. Por favor, consulte la siguiente imagen.
Navegar a la pestaña de parámetros de conexión adicionales. En la tabla de parámetros de conexión adicionales, introduzca REPLICATION=TRUE en el cuadro de texto como se muestra en la siguiente imagen.
Puede utilizar el método de inicio de sesión que prefiera, ya sea la autenticación de Windows o la autenticación de SQL Server.
Esto le permitirá iniciar sesión como agente de replicación y cualquier operación DML que realice en la tabla se ejecutará como agente de replicación.
Ahora ejecute la siguiente consulta para comprobar si ha iniciado sesión como agente de replicación o no.
|
1
|
select SESSIONPROPERTY ( ‘replication_agent’ )
|
Debería devolver 1.
Ahora inserta algunas filas usando el siguiente script.
|
1
2
3
4
|
INSERT INTO Emp ( Empid , name ) values ( 2 , ‘Greg’ )
select * from Emp
select * from EmpJoining
|
Podemos ver que el trigger no se dispara ya que hemos entrado como agente de replicación y el trigger está configurado como «NOT FOR REPLCIATION».
Los datos se insertan sólo en la tabla Emp. Por favor, refiérase a la siguiente imagen.
Aquí, en este caso, el gatillo no está deshabilitado y disponible para otras transacciones de usuario que se disparará gatillo cuando hay un INSERT. El trigger no se dispara sólo para la transacción que se ejecuta como agente de replicación.
Utilice la siguiente consulta para comprobar si su trigger está marcado como «NO PARA REPLICACIÓN» o no.
|
1
|
SELECT name,is_not_for_replication FROM SYS.triggers
|
Hay otras formas de deshabilitar triggers en SQL Server para una sesión manejando código con condiciones.
Por ejemplo se puede utilizar CONTEXT_INFO() en el código del trigger y devolverlo. El uso de CONTEXT_INFO() no requiere permisos especiales.
En este caso, si el CONTEXT_INFO coincide con el valor especificado en el trigger el trigger retorna y no ejecuta el código de abajo. Por favor, consulte el siguiente código.
|
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
|
En este caso debemos establecer el valor de context_info a 0x1256698456 antes de insertar los datos en la tabla «Emp».
Las siguientes vistas del sistema también almacenan la información de contexto, pero la consulta de estas vistas directamente requiere permisos de SELECT y VIEW SERVER STATE.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
En lugar del script T-SQL INSERT, supongamos que los datos son un archivo .txt o un archivo .csv. Podemos usar las opciones BCP UTILITY o BULK INSERT para cargar los datos en las tablas sin disparar los triggers.
Using BCP utility
Podemos usar la utilidad BCP para cargar datos a granel en la tabla sin disparar triggers en SQL Server. Este método sólo funcionará para los INSERTS en la tabla y no dispara los triggers creados «para insertar» y «en lugar de insertar».
Por defecto, la utilidad BCP no dispara los triggers al cargar los datos en las tablas. Para forzar la ejecución del trigger, debemos utilizar -h «FIRE_TRIGGERS» en BCP mientras se cargan los datos en la tabla.
A continuación se muestran las tablas de ejemplo utilizadas en este ejemplo.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE TABLE USERS
(ID int,
NOMBRE 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
|
Por favor, consulte el siguiente ejemplo de BCP por defecto para cargar los datos en la tabla «USERS» que no disparará triggers en SQL Server. He enmascarado el servidor original, los nombres de la base de datos y las credenciales de inicio de sesión.
bcp .dbo.USERS en D:\bcp.txt -T -c -S «SERVERNAME» -Uusername -Ppassword
Consulte el siguiente ejemplo de BCP con pista «FIRE_TRIGGERS» para cargar datos en la tabla USERS que disparará triggers.
bcp .dbo.USERS en D:\bcp.txt -T -c -S «SERVERNAME» -Uusername -Ppassword -h «FIRE_TRIGGERS»
Usando BULK INSERT
Esta opción también funcionará sólo para INSERTS en la tabla y no dispara triggers creados «para insertar» y «en lugar de insertar».
Por defecto, BULK INSERT no dispara triggers en SQL Server. Podemos forzar la ejecución del disparador especificando «FIRE_TRIGGERS»
Tengo el archivo test.txt que como datos con «,» como FIELDTERMINATOR.
Por favor, consulte el siguiente código para BULK INSERT por defecto para cargar los datos en la tabla USERS que no disparará disparadores en SQL Server.
|
1
2
3
4
5
6
7
8
9
|
BULK
INSERT USERS
FROM ‘D:\N-prueba.txt’ –location with filename
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
|
Por favor, refiérase al siguiente código con la pista «FIRE_TRIGGERS» que disparará triggers en SQL Server cuando se carguen los datos del archivo test.txt.
|
1
2
3
4
5
6
7
8
9
10
|
BULK
INSERT USERS
FROM ‘D:\N-prueba.txt’ –location with filename
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’,
FIRE_TRIGGERS
)
GO
|
En este artículo discutimos el comportamiento de los triggers en SQL Server para los siguientes casos.
- Inicio de sesión como usuario normal.
- Iniciar sesión como agente de replicación y el disparador se establece como no para la replicación.
- El uso de la función CONTEXT_INFO en el código del disparador.
- Usando BCP por defecto y con hint «FIRE_TRIGGER»
- Usando BULK INSERT por defecto y con hint «FIRE_TRIGGER»
Para los puntos 3, 4 y 5 no importa si creamos triggers con «NOT FOR REPLICATION» o no.
- Autor
- Puestos recientes
- Geo Replicación en bases de datos Azure SQL habilitadas para Transparent Data Encryption (TDE) – 24 de octubre, 2019
- Descripción general del comando Collate SQL – 22 de octubre de 2019
- Recuperar una contraseña SA perdida – 20 de septiembre de 2019
.