Sintaxis del activador SQL
CREAR DISPARADOR nombre_disparador
EN { vista de tabla }
[ CON CIFRADO ]
{
{ { PARA | DESPUÉS | EN LUGAR DE } { [ INSERTAR ] [ ELIMINAR ] [ ACTUALIZAR ] }
[ CON ANEXO ]
[ NO PARA REPLICACIÓN ]
COMO
[ { SI ACTUALIZAR ( columna )
[ { Y | O } ACTUALIZAR ( columna ) ]
[ ...n ]
| SI ( COLUMNS_UPDATED ( ) máscara_bit_actualizada )
máscara_bit_columna [ ...n ]
} ]
declaración_sql [ ...n ]
}
}
Parámetros
trigger_name
es el nombre del disparador. Los nombres de los activadores deben cumplir con las reglas de identificación y deben ser únicos dentro de la base de datos. Es opcional especificar un nombre de propietario del activador.
Vista de tabla |
Es una tabla o vista en la que se ejecuta un disparador, a veces denominada tabla de disparadores o vista de disparadores. Opcionalmente, especifique el nombre del propietario de la tabla o vista.
CON ENCRYPTION
Cifra las entradas en la tabla de comentarios del sistema que contienen el texto de la instrucción CREATE TRIGGER. Utilice CON ENCRYPTION para evitar que se publiquen desencadenadores como parte de la replicación de SQL Server.
DESPUÉS
Especifica que el activador se activa solo después de que todas las operaciones especificadas en la instrucción SQL activadora se hayan ejecutado correctamente. Todas las operaciones en cascada de referencia y las comprobaciones de restricciones también deben completarse correctamente antes de que se pueda ejecutar este activador.
AFTER es el valor predeterminado si solo se especifica la palabra clave FOR.
El disparador DESPUÉS no se puede definir en una vista.
EN LUGAR DE
Especifica que el disparador debe ejecutarse en lugar de la instrucción SQL desencadenante, reemplazando así la acción de la declaración desencadenante.
Se puede definir hasta un activador INSTEAD OF por instrucción INSERT, UPDATE o DELETE en una tabla o vista. Sin embargo, es posible definir vistas en cada vista que tenga un activador EN LUGAR DE.
EN LUGAR DE, los activadores no se pueden definir en vistas actualizables CON LA OPCIÓN DE VERIFICACIÓN. Si agrega un disparador EN LUGAR DE a una vista actualizable que especifica la opción CON OPCIÓN DE VERIFICACIÓN, SQL Server generará un error. El usuario debe eliminar esta opción con ALTER VIEW antes de definir un disparador INSTEAD OF.
{ [BORRAR] [,] [INSERT] [,] [ACTUALIZAR] }
es la clave para especificar qué declaraciones de modificación de datos activarán el disparador cuando se ejecute en la tabla o Ver personaje. Se debe especificar al menos una opción. Se permite cualquier combinación de estas palabras clave en una definición de activador. Si se especifica más de una opción, separe las opciones con comas.
Para los activadores INSTEAD OF, la opción DELETE no está permitida en tablas con relaciones de referencia de operaciones en cascada ON DELETE. Del mismo modo, la opción ACTUALIZAR no está permitida en una tabla con una relación de referencia de operación en cascada ON UPDATE.
WITH APPEND
Especifica que se deben agregar activadores adicionales de tipos existentes. Esta cláusula opcional sólo es necesaria cuando el nivel de compatibilidad es 65 o inferior. Si el nivel de compatibilidad es 70 o superior, no es necesario utilizar la cláusula FROM APPEND para agregar activadores adicionales de tipos existentes (este es el comportamiento predeterminado de CREATE TRIGGER con el nivel de compatibilidad establecido en 70 o superior). Para obtener más información, consulte sp_dbcmptlevel.
WITH APPEND no se puede usar con un disparador INSTEAD OF, o si se declara explícitamente un disparador DESPUÉS, esta cláusula no se puede usar. CON APPEND sólo se puede utilizar cuando se especifica FOR para compatibilidad con versiones anteriores (sin INSTEAD OF o AFTER). CON APPEND y FOR no serán compatibles en versiones futuras (se interpretarán como DESPUÉS).
NO PARA REPLICACIÓN
Indica que el disparador no debe ejecutarse cuando el proceso de replicación cambia las tablas involucradas en el disparador.
AS
es la acción que realizará el disparador.
sql_statement
es la condición y operación del disparador. Las condiciones de activación especifican criterios adicionales para determinar si una instrucción DELETE, INSERT o UPDATE provoca que se realice una acción de activación.
Cuando se intenta realizar una operación DELETE, INSERT o UPDATE, la acción desencadenante especificada en la instrucción Transact-SQL entrará en vigor.
Un disparador puede contener cualquier número y tipo de instrucciones Transact-SQL. Un activador está diseñado para verificar o cambiar datos basándose en declaraciones de modificación de datos; no debe devolver datos al usuario. Las declaraciones Transact-SQL en los desencadenadores suelen contener lenguaje de flujo de control. Se utilizan varias tablas especiales en la instrucción CREATE TRIGGER:
* las eliminadas y las insertadas son tablas lógicas (conceptuales). Estas tablas son similares en estructura a las tablas en las que se definen los activadores (es decir, las tablas en las que se intentan las operaciones del usuario; estas tablas se utilizan para contener valores antiguos o nuevos para las filas que pueden ser modificadas por las operaciones del usuario); Por ejemplo, para recuperar todos los valores de la tabla eliminada, utilice:
SELECT *
FROM eliminado
* Si el nivel de compatibilidad es igual a 70 , luego en los activadores DELETE , INSERT o UPDATE, SQL Server no permitirá referencias a columnas text, ntext o image en tablas insertadas y eliminadas. No se puede acceder a los valores de texto, ntext e imagen en las tablas insertadas y eliminadas. Para recuperar nuevos valores en un disparador INSERTAR o ACTUALIZAR, junte la tabla insertada con la tabla actualizada original. Cuando el nivel de compatibilidad es 65 o inferior, se devuelven valores nulos para las columnas de texto, ntext o imagen en la tabla insertada o eliminada que permiten valores nulos; si estas columnas no admiten valores NULL, se devuelve una cadena de longitud cero.
Cuando el nivel de compatibilidad es 80 o superior, SQL Server permite actualizar columnas de texto, ntext o imagen a través de un activador INSTEAD OF en una tabla o vista.
n
es un marcador de posición que indica que el desencadenador puede contener varias instrucciones Transact-SQL. Para una declaración IF UPDATE (columna), puede incluir varias columnas repitiendo la cláusula UPDATE (columna).
IF UPDATE (columna)
Prueba una operación INSERT o UPDATE en la columna especificada. No se puede utilizar para operaciones DELETE. Se pueden especificar varias columnas. Dado que el nombre de la tabla se especifica en la cláusula ON, no incluya el nombre de la tabla antes del nombre de la columna en la cláusula IF UPDATE. Para probar una operación INSERT o UPDATE en varias columnas, especifique cláusulas UPDATE(columna) separadas después de la primera operación. IF UPDATE en una operación INSERT devolverá un valor VERDADERO porque las columnas tienen valores explícitos o implícitos (NULL) insertados.
Explicación La cláusula IF UPDATE (columna) es funcionalmente equivalente a la declaración IF, IF...ELSE o WHILE, y puede usar el bloque de declaración BEGIN...END. Para obtener más información, consulte Idiomas de flujo de control.
ACTUALIZAR (columna) se puede utilizar en cualquier parte del cuerpo del disparador.
columna
es el nombre de la columna para probar la operación INSERT o UPDATE. La columna puede ser cualquier tipo de datos admitido por SQL Server. Sin embargo, las columnas calculadas no se pueden utilizar en este entorno. Consulte Tipos de datos para obtener más información.
IF (COLUMNS_UPDATED())
Prueba si la columna mencionada está insertada o actualizada, solo se usa en activadores INSERT o UPDATE. COLUMNS_UPDATED Devuelve un patrón de bits varbinary que indica qué columnas de la tabla se insertaron o actualizaron.
La función COLUMNS_UPDATED devuelve los bits en orden de izquierda a derecha, siendo el bit más a la izquierda el bit menos significativo. El bit más a la izquierda representa la primera columna de la tabla; el siguiente bit a la derecha representa la segunda columna, y así sucesivamente. Si el activador creado en una tabla contiene más de 8 columnas, COLUMNS_UPDATED devuelve varios bytes, siendo el byte más a la izquierda el menos significativo. Se devolverá COLUMNS_UPDATED para todas las columnas en una operación INSERT.
COLUMNS_UPDATED se puede utilizar en cualquier parte del cuerpo del disparador.
bitwise_operator
es un operador bit a bit utilizado para operaciones de comparación.
updated_bitmask
es una máscara de bits de número entero que representa la columna actual actualizada o insertada. Por ejemplo, la tabla t1 contiene las columnas C1, C2, C3, C4 y C5. Supongamos que hay un activador de ACTUALIZACIÓN en la tabla t1. Para verificar si las columnas C2, C3 y C4 están actualizadas, especifique un valor de 14; para verificar si solo la columna C2 está actualizada, especifique un valor de 2.
comparison_operator
es un operador de comparación. Utilice el signo igual (=) para comprobar si todas las columnas especificadas en update_bitmask se actualizaron realmente. Utilice el signo mayor que (gt;) para comprobar si alguna columna o columnas especificadas en update_bitmask se han actualizado.
column_bitmask
Es una máscara de bits entera de las columnas que se van a verificar para ver si estas columnas se han actualizado o insertado.
Notas
Los activadores se utilizan a menudo para hacer cumplir las reglas comerciales y la integridad de los datos.
SQL Server proporciona integridad referencial declarativa (DRI) a través de declaraciones de creación de tablas (ALTER TABLE y CREATE TABLE; sin embargo, DRI no proporciona integridad referencial entre bases de datos). Para hacer cumplir la integridad referencial (las reglas sobre la relación entre las claves primaria y externa de una tabla), use restricciones de clave primaria y clave externa (las palabras clave PRIMARY KEY y FOREIGN KEY de ALTER TABLE y CREATE TABLE). Si existen restricciones en la tabla de desencadenadores, estas restricciones se verifican después de que se ejecute el desencadenador INSTEAD OF y antes de que se ejecute el desencadenador DESPUÉS. Si se viola la restricción, la operación del disparador EN LUGAR DE se revierte y el disparador DESPUÉS no se ejecuta (activa).
Puedes usar sp_settriggerorder para especificar el primer y último disparador DESPUÉS que se ejecutará en la tabla. Solo se puede especificar un activador DESPUÉS de primero en ejecutarse y uno de último en ejecutarse en una tabla para cada operación INSERTAR, ACTUALIZAR y ELIMINAR. Si hay otros activadores DESPUÉS en la misma tabla, se ejecutarán en orden aleatorio.
Si la instrucción ALTER TRIGGER cambia el primer o último disparador, el primer o último atributo establecido en el disparador modificado se elimina y el valor de clasificación se debe restablecer con sp_settriggerorder.
El activador DESPUÉS se ejecutará solo después de que la instrucción SQL activadora (incluidas todas las operaciones en cascada de referencia y comprobaciones de restricciones asociadas con el objeto actualizado o eliminado) se haya ejecutado correctamente. El disparador DESPUÉS verifica los efectos de la declaración desencadenante y todas las operaciones en cascada de referencia ACTUALIZAR y ELIMINAR causadas por la declaración desencadenante.
Restricciones de activación
CREATE TRIGGER debe ser la primera declaración del lote y solo se puede aplicar a una tabla.
Los activadores solo se pueden crear en la base de datos actual, pero los activadores pueden hacer referencia a objetos externos de la base de datos actual.
Si especifica el nombre del propietario del activador para calificar el activador, califique el nombre de la tabla de la misma manera.
En la misma instrucción CREATE TRIGGER, se puede definir la misma acción desencadenante para múltiples operaciones de usuario (como INSERT y UPDATE).
No se pueden definir activadores EN LUGAR DE ELIMINAR/ACTUALIZAR en una tabla cuyas claves externas definen cascadas en operaciones ELIMINAR/ACTUALIZAR.
Cualquier instrucción SET se puede especificar dentro del disparador. La opción SET seleccionada es válida durante la ejecución del disparador y vuelve a la configuración anterior una vez que el disparador ha completado la ejecución.
Al igual que con el uso de procedimientos almacenados, cuando se activa el disparador, los resultados se devuelven a la aplicación que realiza la llamada. Para evitar devolver resultados a la aplicación cuando se activa un disparador, no incluya declaraciones SELECT que devuelvan resultados o declaraciones que realicen asignaciones de variables en el disparador. Los desencadenadores que contienen declaraciones SELECT que devuelven resultados al usuario o declaraciones que realizan asignaciones de variables requieren un manejo especial de estos resultados devueltos se deben escribir en cada aplicación que tenga permiso para modificar la tabla de desencadenadores. Si debe ocurrir una asignación de variable en un disparador, debe usar una instrucción SET NOCOUNT al comienzo del disparador para evitar devolver ningún conjunto de resultados.
Los activadores DELETE no pueden capturar declaraciones TRUNCATE TABLE. Aunque la instrucción TRUNCATE TABLE es en realidad un DELETE sin una cláusula WHERE (borra todas las filas), no está registrada y no puede ejecutar activadores.
Debido a que los permisos en la instrucción TRUNCATE TABLE se otorgan de forma predeterminada al propietario de la tabla y no son transferibles, solo el propietario de la tabla debe considerar el uso inadvertido de la instrucción TRUNCATE TABLE para eludir los activadores DELETE.
La instrucción WRITETEXT no activa el disparador independientemente de que se haya registrado o no.
Las siguientes instrucciones Transact-SQL no están permitidas en los desencadenadores:
ALTER DATABASE CREATE DATABASE DISK INIT
DISK RESIZE DROP DATABASE LOAD DATABASE
CARGAR REGISTRO RECONFIGURAR RESTAURAR BASE DE DATOS
RESTAURAR REGISTRO
Explicación Dado que SQL Server no admite activadores definidos por el usuario en las tablas del sistema, se recomienda no crear activadores definidos por el usuario en el sistema. mesas.
Múltiples disparadores
SQL Server permite la creación de múltiples disparadores para cada evento de modificación de datos (ELIMINAR, INSERTAR o ACTUALIZAR). Por ejemplo, si ejecuta CREATE TRIGGER FOR UPDATE en una tabla que ya tiene un activador de actualización, se creará otro activador de actualización. En versiones anteriores, solo se permitía un activador por evento de modificación de datos (INSERT, UPDATE o DELETE) en cada tabla.
Nota El comportamiento predeterminado de CREATE TRIGGER (nivel de compatibilidad 70) es agregar activadores adicionales a los activadores existentes si los nombres de los activadores son diferentes. Si los nombres de los desencadenadores son los mismos, SQL Server devuelve un mensaje de error. Sin embargo, si el nivel de compatibilidad es 65 o menos, un nuevo disparador creado con la instrucción CREATE TRIGGER reemplaza cualquier disparador existente del mismo tipo, incluso si el nombre del disparador es diferente. Para obtener más información, consulte sp_dbcmptlevel.
Disparadores recursivos
SQL Server también permite la invocación recursiva de desencadenadores cuando la configuración de desencadenadores recursivos está habilitada en sp_dboption.
Los desencadenadores recursivos permiten que se produzcan dos tipos de recursividad:
* Recursividad indirecta
* Recursividad directa
Cuando se utiliza la recursividad indirecta, el la aplicación actualiza la tabla T1, que activa el activador TR1, que actualiza la tabla T2. En este caso, active T2 y actualice T1.
Cuando se utiliza la recursividad directa, la aplicación actualiza la tabla T1, activando así el disparador TR1, que actualiza la tabla T1. Debido a que la tabla T1 está actualizada, el disparador TR1 se activa nuevamente, y así sucesivamente.
El siguiente ejemplo utiliza recursividad de desencadenador indirecto y recursividad de desencadenador directo. Supongamos que en la tabla T1 se definen dos activadores de actualización TR1 y TR2. El disparador TR1 actualiza recursivamente la tabla T1. La instrucción UPDATE hace que TR1 y TR2 se ejecuten una vez cada uno. Y la ejecución de TR1 desencadenará la ejecución de TR1 (recursión) y TR2. Las tablas insertadas y eliminadas para un desencadenador determinado contienen solo filas correspondientes a la instrucción UPDATE que activó el desencadenador de llamada.
Nota El comportamiento anterior solo ocurrirá si la configuración de desencadenadores recursivos de sp_dboption está habilitada. No existe un orden de ejecución definido para múltiples desencadenantes definidos para un evento determinado. Cada desencadenante debe ser autónomo.
La configuración de desactivación de activadores recursivos solo puede desactivar la recursividad directa.
Para deshabilitar también la recursividad indirecta, use sp_configure para establecer la opción del servidor de desencadenadores anidados en 0.
Si algún disparador ejecuta una instrucción ROLLBACK TRANSACTION, no se ejecutarán más disparadores independientemente del nivel de anidamiento.
Disparadores anidados
Los desencadenadores se pueden anidar hasta en 32 niveles. Si un disparador cambia una tabla que contiene otro disparador, se activa el segundo disparador, que luego puede llamar a un tercer disparador, y así sucesivamente. Si algún disparador en la cadena desencadena un bucle infinito, se excederá el límite del nivel de anidamiento, lo que provocará que se cancele el disparador. Para deshabilitar los desencadenadores anidados, use sp_configure para establecer la opción de desencadenadores anidados en 0 (desactivado). La configuración predeterminada permite activadores anidados. Si los desencadenadores anidados están desactivados, los desencadenadores recursivos también se desactivan, independientemente de la configuración de desencadenadores recursivos de sp_dboption.
Resolución de nombres diferida
SQL Server permite que los procedimientos almacenados, desencadenadores y lotes de Transact-SQL hagan referencia a tablas que no existen en el momento de la compilación. Esta capacidad se llama resolución de nombres retrasada. Sin embargo, si un procedimiento almacenado, desencadenador o lote de Transact-SQL hace referencia a una tabla definida en el procedimiento almacenado o desencadenador, la advertencia se emitirá en el momento de la creación solo si la configuración del nivel de compatibilidad (establecida mediante la ejecución de sp_dbcmptlevel) es igual a 65. Si se utiliza el procesamiento por lotes, se emite una advertencia en el momento de la compilación. Si la tabla a la que se hace referencia no existe, se devolverá un mensaje de error en tiempo de ejecución. Para obtener más información, consulte Compilación y resolución de nombres retrasados.
Permisos
Los permisos CREATE TRIGGER se otorgan de forma predeterminada al propietario de la tabla que define el activador, a los miembros de la función fija de servidor sysadmin y a los miembros de las funciones fijas de base de datos db_owner y db_ddladmin. y no son transferibles.
Para recuperar datos de una tabla o vista, el usuario debe tener permiso de instrucción SELECT en la tabla o vista. Para actualizar el contenido de una tabla o vista, el usuario debe tener permisos de instrucción INSERTAR, ELIMINAR y ACTUALIZAR en la tabla o vista.
Si existe un activador INSTEAD OF en una vista, el usuario debe tener privilegios INSERTAR, ELIMINAR y ACTUALIZAR en la vista para emitir declaraciones INSERT, ELIMINAR y ACTUALIZAR en la vista, independientemente de si realmente son en la vista Se realizó tal operación.
Ejemplo
A. Uso de un disparador con un mensaje de alerta
Cuando alguien intenta agregar o cambiar datos en la tabla de títulos, el siguiente ejemplo notificará al cliente Se muestra un mensaje en el terminal.
Descripción El mensaje 50009 es un mensaje definido por el usuario en sysmessages. Para obtener más información sobre la creación de mensajes definidos por el usuario, consulte sp_addmessage.
UTILIZAR pubs
SI EXISTE (SELECCIONE el nombre DE sysobjects
DONDE nombre = 'recordatorio' Y tipo = 'TR')
Recordatorio de DROP TRIGGER
IR
CREAR recordatorio de TRIGGER
EN títulos
PARA INSERTAR, ACTUALIZAR
COMO RAISERROR (50009, 16, 10)
GO
B. Utilice un activador con un correo electrónico de recordatorio
Cuando la tabla de títulos cambie, el siguiente ejemplo enviará el correo electrónico. enviado a la persona designada (MaryM).
UTILIZAR pubs
SI EXISTE (SELECCIONE el nombre DE sysobjects
DONDE nombre = 'recordatorio' Y tipo = 'TR')
Recordatorio de DROP TRIGGER
IR
CREAR recordatorio de TRIGGER
EN títulos
PARA INSERTAR, ACTUALIZAR, ELIMINAR
AS
EXEC master..xp_sendmail 'MaryM',
'No olvides imprimir un informe para los distribuidores.'
GO
C. Uso de reglas de negocio de activación entre tablas de empleados y trabajos
Dado que las restricciones CHECK solo pueden hacer referencia a columnas para las cuales se definen restricciones a nivel de columna o de tabla, cualquier restricción entre tablas (en lo siguiente) El ejemplo se refiere a reglas de negocio) deben definirse como desencadenantes.
El siguiente ejemplo crea un disparador que, cuando se inserta o actualiza un nivel de trabajo de empleado (job_lvls), verifica si el nivel de trabajo del empleado especificado (que determina el salario) está dentro del rango definido para el trabajo. Para obtener el rango adecuado, se debe hacer referencia a la tabla de trabajos.
USE pubs
SI EXISTE (SELECCIONE el nombre DE sysobjects
DONDE nombre = 'employee_insupd' Y tipo = 'TR')
DROP TRIGGER empleado_insupd
GO
CREAR DISPARADOR empleado_insupd
EN empleado
PARA INSERTAR, ACTUALIZAR
COMO
/* Obtenga el rango de nivel para este tipo de trabajo de la tabla de trabajos */
DECLARE @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
SELECT @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
DESDE el empleado e INNER JOIN insertado i ON e.emp_id = i.emp_id
UNIRSE trabajos j ON j.job_id = i.job_id
IF (@job_id = 1) y (@emp_lvl lt; gt; 10)
BEGIN
RAISERROR ('Id. de trabajo 1 espera el nivel predeterminado de 10.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
RAISERROR ('El nivel para job_id: d debe estar entre d y d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
END
Usar resolución de nombres retrasada
El siguiente ejemplo crea dos activadores para ilustrar la resolución de nombres retrasada.
USE pubs
SI EXISTE (SELECCIONE el nombre DE sysobjects
DONDE nombre = 'trig1' Y tipo = 'TR')
DROP TRIGGER trig1
GO
-- Creando un disparador en una tabla inexistente.
CREAR TRIGGER trig1
en autores
PARA INSERTAR, ACTUALIZAR, ELIMINAR
COMO
SELECCIONAR a. au_lname, a. au_fname, x. info
DE los autores una UNIÓN INTERNA no_existe. x
ON a. au_id = x. au_id
GO
-- Aquí está la declaración para ver el texto del activador.
SELECT o. id, c.text
DESDE sysobjects o INNER JOIN syscomments c
ON o. type = 'TR' y o. name = 'trig1'
-- Creando un disparador en una tabla existente, pero con una columna inexistente
--.
USE pubs
SI EXISTE (SELECCIONE el nombre DE sysobjects
DONDE nombre = 'trig2' Y tipo = 'TR')
DROP TRIGGER trig2
IR
CREAR DISPARADOR trig2
EN autores
PARA INSERTAR, ACTUALIZAR
COMO
DECLARAR @fax varchar(12)
SELECCIONAR @fax = teléfono
DE los autores
IR
-- Aquí está la declaración para ver realmente el texto del disparador.
SELECT o.c.text
FROM sysobjects o INNER JOIN syscomments c
ON id =. c .id
WHERE o.type = 'TR' y o. name = 'trig2'
E Utilice COLUMNS_UPDATED
El siguiente ejemplo crea dos tablas. : una tabla de datos de empleado y una tabla de datos de empleado de auditoría. Los miembros del departamento de Recursos Humanos pueden modificar la tabla EmployeeData, que contiene información confidencial sobre los salarios de los empleados.
Si se cambia el número de Seguro Social (SSN), el salario anual o la cuenta bancaria de un empleado, se genera un registro de auditoría y se inserta en la tabla de auditoría auditEmployeeData.
Puede probar rápidamente los cambios en estas columnas que contienen información confidencial de los empleados utilizando la función COLUMNS_UPDATED(). COLUMNS_UPDATED() solo funciona cuando se intenta detectar cambios en las primeras 8 columnas de la tabla.
USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'employeeData')
DROP TABLE EmployeeData
SI EXISTE(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'auditEmployeeData')
DROP TABLE auditEmployeeData
GO
CREAR TABLA datos de empleado (
emp_id int NOT NULL,
emp_bankAccountNumber char (10) NOT NULL,
emp_salary int NOT NULL,
emp_SSN char (11) NO NULO,
emp_lname nchar (32) NO NULO,
emp_fname nchar (32) NO NULO,
emp_manager int NOT NULL
)
GO
CREAR TABLA auditEmployeeData (
audit_log_id identificador único DEFAULT NEWID(),
audit_log_type char (3) NOT NULL,
audit_emp_id int NOT NULL,
audit_emp_bankAccountNumber char (10) NULL,
audit_emp_salary int NULL,
audit_emp_SSN char (11) NULL,
audit_user nombre del sistema DEFAULT SUSER_SNAME(),
audit_changed fecha y hora DEFAULT GETDATE()
)
IR
CREAR DISPARADOR updEmployeeData
ON EmployeeData
PARA actualizar AS
/*Compruebe si las columnas 2, 3 o 4 tienen Si se han modificado alguna o todas las columnas 2, 3 o 4, cree un registro de auditoría. La máscara de bits es: power(2,(2-1)) power(2,(3-1)) power(2). , (4-1)) = 14. Para verificar si todas las columnas 2, 3 y 4 están actualizadas, use = 14 en lugar de gt; ) amperio;
14) gt; 0
/*Use IF (COLUMNS_UPDATED() amp; 14) = 14 para ver si todas las columnas 2, 3 y 4 están actualizadas.*/
BEGIN
-- Auditar registro ANTIGUO.
INSERTAR EN auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'ANTIGUO',
del.emp_id,
del.emp_bankAccountNumber,
del.emp_salary,
del.emp_SSN
DE eliminado del
-- Auditar NUEVO registro.
INSERTAR EN auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECCIONE 'NUEVO',
ins.emp_id,
ins.emp_bankAccountNumber,
ins.emp_salary,
ins.emp_SSN
FROM insertados
END
GO
/*Insertar un nuevo empleado no provocar que se active el disparador ACTUALIZAR.*/
INSERTAR EN datos de empleado
VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel' , N'Roland', 32)
GO
/*La actualización del registro de empleado para el empleado número 101 para cambiar el salario a 51000 provoca que se active el activador ACTUALIZAR y se genere una pista de auditoría. ser producido.*/
ACTUALIZAR datos de empleado
SET emp_salary = 51000
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO
/*Actualizando el registro de empleado número 101 para cambiar tanto el número de cuenta bancaria como el número de seguridad social
El número (SSN) hace que se active el activador ACTUALIZAR y se genere un registro de auditoría.*/
ACTUALIZAR datos de empleado
SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
DONDE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO
Usar la prueba COLUMNS_UPDATED. Más de 8 columnas
Si debe probar actualizaciones que afectan a más de las primeras 8 columnas de la tabla, debe usar la función UBSTRING para probar los bits apropiados devueltos por COLUMNS_UPDATED. El siguiente ejemplo prueba las actualizaciones que afectan a las columnas 3, 5 o 9 de la tabla Northwind.dbo.Customers.
UTILIZAR Northwind
DROP TRIGGER tr1
GO
CREAR TRIGGER tr1 EN Clientes
PARA ACTUALIZAR COMO
IF ((SUBSTRING(COLUMNS_UPDATED(),1,1)=potencia(2,(3-1))
potencia(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(), 2, 1)=potencia(2, (1-1)))
)
PRINT 'Columnas 3, 5 y 9 actualizados'
GO
ACTUALIZAR Clientes
SET ContactName=ContactName,
Dirección=Dirección,
País=País
IR