Trigger de eliminacion

Checa los siguiente segmentos de la ayuda de sql server:
Trigger Constants (SQLDMO_TRIGGER_TYPE)
Trigger constants enumerate the kind of Transact-SQL data modification statement that will cause a trigger to fire.
¿Microsoft® SQL Server? Cursors may fire when an INSERT, UPDATE, or DELETE statement modifies data in a table on which an enabled trigger is defined. Separate triggers may be created to implement behavior for any one or a combination of Transact-SQL DML statements.
Constant Value Description
SQLDMOTrig_All 7 Trigger is fired by any data modification statement.
SQLDMOTrig_Delete 4 Trigger is fired by a DELETE statement.
SQLDMOTrig_Insert 1 Trigger is fired by an INSERT statement.
SQLDMOTrig_Unknown 0 Bad or invalid value.
SQLDMOTrig_Update 2 Trigger is fired by an UPDATE statement.
See Also
Type Property (Trigger)
©1988-2000 Microsoft Corporation. All Rights Reserved.
Create trigger
Crea un desencadenador, que es una clase especial de procedimiento almacenado que se ejecuta automáticamente cuando un usuario intenta la instrucción especificada de modificación de datos en la tabla indicada. ¿Microsoft® SQL Server? permite crear varios desencadenadores para cualquier instrucción INSERT, UPDATE o DELETE.
Sintaxis
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
Argumentos
trigger_name
Es el nombre del desencadenador. Un nombre de desencadenador debe cumplir las reglas de los identificadores y debe ser único en la base de datos. Especificar el propietario del desencadenador es opcional.
Table | view
Es la tabla o vista en que se ejecuta el desencadenador; algunas veces se denomina tabla del desencadenador o vista del desencadenador. Especificar el nombre del propietario de la tabla o vista es opcional.
WITH ENCRYPTION
Codifica las entradas syscomments que contienen el texto de CREATE TRIGGER. El uso de WITH ENCRYPTION impide que el desencadenador se publique como parte de la duplicación de SQL Server.
After
Especifica que el desencadenador sólo se activa cuando todas las operaciones especificadas en la instrucción SQL desencadenadora se han ejecutado correctamente. Además, todas las acciones referenciales en cascada y las comprobaciones de restricciones deben ser correctas para que este desencadenador se ejecute.
AFTER es el valor predeterminado, si sólo se especifica la palabra clave FOR.
Los desencadenadores AFTER no se pueden definir en las vistas.
Instead of
Especifica que se ejecuta el desencadenador en vez de la instrucción SQL desencadenadora, por lo que se suplantan las acciones de las instrucciones desencadenadoras.
Como máximo, se puede definir un desencadenador INSTEAD OF por cada instrucción INSERT, UPDATE o DELETE en cada tabla o vista. No obstante, en las vistas es posible definir otras vistas que tengan su propio desencadenador INSTEAD OF.
Los desencadenadores INSTEAD OF no se permiten en las vistas actualizables WITH CHECK OPTION. SQL Server emitirá un error si se agrega un desencadenador INSTEAD OF a una vista actualizable donde se ha especificado WITH CHECK OPTION. El usuario debe quitar esta opción mediante ALTER VIEW antes de definir el desencadenador INSTEAD OF.
{ [DELETE] [,] [INSERT] [,] [UPDATE] }
Son palabras clave que especifican qué instrucciones de modificación de datos activan el desencadenador cuando se intentan en esta tabla o vista. Se debe especificar al menos una opción. En la definición del desencadenador se permite cualquier combinación de éstas, en cualquier orden. Si especifica más de una opción, sepárelas con comas.
Para los desencadenadores INSTEAD OF, no se permite la opción DELETE en tablas que tengan una relación de integridad referencial que especifica una acción ON DELETE en cascada. Igualmente, no se permite la opción UPDATE en tablas que tengan una relación de integridad referencial que especifica una acción ON UPDATE en cascada.
WITH APPEND
Especifica que debe agregarse un desencadenador adicional de un tipo existente. La utilización de 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 necesaria la cláusula WITH APPEND para agregar un desencadenador adicional de un tipo existente (éste es el comportamiento predeterminado de CREATE TRIGGER cuando el nivel de compatibilidad es 70 o superior). Para obtener más información, consulte sp_dbcmptlevel.
WITH APPEND no se puede utilizar con desencadenadores INSTEAD OF o cuando se ha declarado AFTER explícitamente. WITH APPEND sólo se puede utilizar si se especificó FOR (sin INSTEAD OF ni AFTER) por motivos de compatibilidad con versiones anteriores. WITH APPEND y FOR (que se interpreta como AFTER) no se admitirán en futuras versiones.
NOT FOR REPLICATION
Indica que el desencadenador no debe ejecutarse cuando un proceso de duplicación modifica la tabla involucrada en el mismo.
AS
Son las acciones que va a llevar a cabo el desencadenador.
sql_statement
Son las condiciones y acciones del desencadenador. Las condiciones del desencadenador especifican los criterios adicionales que determinan si los intentos de las instrucciones DELETE, INSERT o UPDATE hacen que se lleven a cabo las acciones del desencadenador.
Las acciones del desencadenador especificadas en las instrucciones Transact-SQL entran en efecto cuando se intenta la operación DELETE, INSERT o UPDATE.
Los desencadenadores pueden incluir cualquier número y clase de instrucciones Transact-SQL. Un desencadenador está diseñado para comprobar o cambiar los datos en base a una instrucción de modificación de datos; no debe devolver datos al usuario. Las instrucciones Transact-SQL de un desencadenador incluyen a menudo lenguaje de control de flujo. En las instrucciones CREATE TRIGGER se utilizan unas cuantas tablas especiales:
Deleted e inserted son tablas lógicas (conceptuales). Son de estructura similar a la tabla en que se define el desencadenador, es decir, la tabla en que se intenta la acción del usuario, y guarda los valores antiguos o nuevos de las filas que la acción del usuario puede cambiar. Por ejemplo, para recuperar todos los valores de la tabla deleted, utilice:
SELECT *
FROM deleted
En un desencadenador DELETE, INSERT o UPDATE, SQL Server no admite referencias de columnas text, ntext o image en las tablas inserted y deleted si el nivel de compatibilidad es igual a 70. No se puede tener acceso a los valores text, ntext e image de las tablas inserted y deleted. Para recuperar el nuevo valor de un desencadenador INSERT o UPDATE, combine la tabla inserted con la tabla de actualización original. Cuando el nivel de compatibilidad es 65 o inferior, se devuelven valores NULL para las columnas inserted o deleted text, ntext o image que admiten valores NULL; si las columnas no permiten valores NULL, se devuelven cadenas de longitud cero.
Si el nivel de compatibilidad es 80 o superior, SQL Server permite actualizar las columnas text, ntext o image mediante el desencadenador INSTEAD OF en tablas o vistas.
n
Se trata de un marcador de posición que indica que se pueden incluir varias instrucciones Transact-SQL en el desencadenador. Para la instrucción IF UPDATE (column), se pueden incluir varias columnas repitiendo la cláusula UPDATE (column).
IF UPDATE (column)
Prueba una acción INSERT o UPDATE en una columna especificada y no se utiliza con operaciones DELETE. Se puede especificar más de una columna. Como el nombre de la tabla se especifica en la cláusula ON, no lo incluya antes del nombre de la columna en una cláusula IF UPDATE. Para probar una acción INSERT o UPDATE para más de una columna, especifique una cláusula UPDATE(column) separada a continuación de la primera. IF UPDATE devolverá el valor TRUE en las acciones INSERT porque en las columnas se insertaron valores explícitos o implícitos (NULL).
Nota La cláusula IF UPDATE (column) funciona de forma idéntica a una instrucción IF, IF...ELSE o WHILE, y puede utilizar el bloque BEGIN...END. Para obtener más información, consulte Lenguaje de control de flujo.
UPDATE(column) puede utilizarse en cualquier parte dentro del cuerpo del desencadenador.
Column
Es el nombre de la columna que se va a probar para una acción INSERT o UPDATE. Esta columna puede ser de cualquier tipo de datos admitido por SQL Server. No obstante, no se pueden utilizar columnas calculadas en este contexto. Para obtener más información, consulte Tipos de datos.
IF (COLUMNS_UPDATED())
Prueba, sólo en un desencadenador INSERT o UPDATE, si la columna o columnas mencionadas se insertan o se actualizan. COLUMNS_UPDATED devuelve un patrón de bits varbinary que indica qué columnas de la tabla se insertaron o se actualizaron.
La función COLUMNS_UPDATED devuelve los bits en orden de izquierda a derecha, siendo el bit menos significativo el primero de la izquierda. El primer bit de la izquierda representa la primera columna de la tabla, el siguiente representa la segunda columna, etc. COLUMNS_UPDATED devuelve varios bytes si la tabla en que se ha creado el desencadenador contiene más de 8 columnas, siendo el menos significativo el primero de la izquierda. COLUMNS_UPDATED devolverá el valor TRUE en todas las columnas de las acciones INSERT porque en las columnas se insertaron valores explícitos o implícitos (NULL).
COLUMNS_UPDATED puede utilizarse en cualquier parte dentro del cuerpo del desencadenador.
bitwise_operator
Es el operador de bits que se utilizará en las comparaciones.
updated_bitmask
Es la máscara de bits de enteros de las columnas realmente actualizadas o insertadas. Por ejemplo, la tabla t1 contiene las columnas C1, C2, C3, C4 y C5. Para comprobar si las columnas C2, C3 y C4 se han actualizado (con un desencadenador UPDATE en la tabla t1), especifique un valor de 14. Para comprobar si sólo se ha actualizado la columna C2, especifique un valor de 2.
comparison_operator
Es el operador de comparación. Utilice el signo igual (=) para comprobar si todas las columnas especificadas en updated_bitmask se han actualizado. Utilice el símbolo mayor que (>) para comprobar si alguna de las columnas especificadas en updated_bitmask se han actualizado.
column_bitmask
Es la máscara de bits de enteros de las columnas que hay que comprobar para ver si se han actualizado o insertado.
Observaciones
A menudo se utilizan desencadenadores para exigir las reglas de empresa y la integridad de los datos. SQL Server proporciona integridad referencial declarativa (DRI, Declarative Referential Integrity) a través de las instrucciones de creación de tabla (ALTER TABLE y CREATE TABLE); sin embargo, DRI no proporciona integridad referencial entre bases de datos. Para exigir la integridad referencial (reglas acerca de la relación entre la clave principal y la clave externa de las tablas), utilice las restricciones de clave principal y externa (las palabras clave PRIMARY KEY y FOREIGN KEY de ALTER TABLE y CREATE TABLE). Si existen restricciones en la tabla de desencadenadores, se comprueban después de la ejecución del desencadenador INSTEAD OF y antes de la de AFTER. Si no se respetan las restricciones, las acciones del desencadenador INSTEAD OF se deshacen y no se ejecuta (activa) el desencadenador AFTER.
El primer y último desencadenador AFTER que se ejecuta en una tabla se puede especificar mediante sp_settriggerorder. Sólo se puede especificar el primer y último desencadenador AFTER para cada una de las operaciones INSERT, UPDATE y DELETE de una tabla; si hay otros desencadenadores AFTER en la misma tabla, se ejecutan aleatoriamente.
Si una instrucción ALTER TRIGGER modifica el primer o último desencadenador, se elimina el primer o último atributo establecido en el desencadenador modificado, y el valor del orden se debe restablecer con sp_settriggerorder.
Un desencadenador AFTER se ejecuta sólo después de ejecutar correctamente la instrucción SQL desencadenadora, incluidas todas las acciones referenciales en cascada y las comprobaciones de restricciones asociadas con el objeto actualizado o eliminado. El desencadenador AFTER ve los efectos de la instrucción desencadenadora así como todas las acciones UPDATE y DELETE con referencias en cascada que causa la instrucción desencadenadora.
Limitaciones de los desencadenadores
CREATE TRIGGER debe ser la primera instrucción en el proceso por lotes y sólo se puede aplicar a una tabla.
Un desencadenador se crea solamente en la base de datos actual; sin embargo, un desencadenador puede hacer referencia a objetos que están fuera de la base de datos actual.
Si se especifica el nombre del propietario del desencadenador (para calificar el desencadenador), califique el nombre de la tabla de la misma forma.
La misma acción del desencadenador puede definirse para más de una acción del usuario (por ejemplo, INSERT y UPDATE) en la misma instrucción CREATE TRIGGER.
Los desencadenadores INSTEAD OF DELETE/UPDATE no pueden definirse en una tabla con una clave externa definida en cascada en la acción DELETE/UPDATE.
En un desencadenador se puede especificar cualquier instrucción SET. La opción SET elegida permanece en efecto durante la ejecución del desencadenador y, después, vuelve a su configuración anterior.
Cuando se activa un desencadenador, los resultados se devuelven a la aplicación que llama, exactamente igual que con los procedimientos almacenados. Para impedir que se devuelvan resultados a la aplicación debido a la activación de un desencadenador, no incluya las instrucciones SELECT que devuelven resultados ni las instrucciones que realizan una asignación variable en un desencadenador. Un desencadenador que incluya instrucciones SELECT que devuelven resultados al usuario o instrucciones que realizan asignaciones de variables requiere un tratamiento especial; estos resultados devueltos tendrían que escribirse en cada aplicación en la que se permiten modificaciones a la tabla del desencadenador. Si es preciso que existan asignaciones de variable en un desencadenador, utilice una instrucción SET NOCOUNT al principio del mismo para eliminar la devolución de cualquier conjunto de resultados.
Un desencadenador DELETE no captura una instrucción TRUNCATE TABLE. Aunque una instrucción TRUNCATE TABLE es, de hecho, un desencadenador DELETE sin una cláusula WHERE (quita todas las filas), no se registra y, por tanto, no puede ejecutar un desencadenador. Dado que el permiso de la instrucción TRUNCATE TABLE es, de forma predeterminada, el del propietario de la tabla y no se puede transferir, sólo el propietario de la tabla debe preocuparse de invocar sin darse cuenta una instrucción TRUNCATE TABLE que no producirá la ejecución del desencadenador DELETE.
La instrucción WRITETEXT, ya se registre o no, no activa un desencadenador.
Las siguientes instrucciones Transact-SQL no están permitidas en un desencadenador:
ALTER DATABASE CREATE DATABASE DISK INIT
DISK RESIZE DROP DATABASE LOAD DATABASE
LOAD LOG RECONFIGURE RESTORE DATABASE
RESTORE LOG
Nota Debido a que SQL Server no admite desencadenadores definidos por el usuario en tablas del sistema, se recomienda que no se creen desencadenadores definidos por el usuario en tablas del sistema.
Desencadenadores múltiples
SQL Server permite que se creen varios desencadenadores por cada evento de modificación (DELETE, INSERT o UPDATE). Por ejemplo, si se ejecuta CREATE TRIGGER FOR UPDATE para una tabla que ya tiene un desencadenador UPDATE, se creará un desencadenador de actualización adicional. En las versiones anteriores, sólo se permitía un desencadenador por cada evento de modificación (INSERT, UPDATE, DELETE) en cada tabla.
Nota El comportamiento predeterminado de CREATE TRIGGER (con un nivel de compatibilidad de 70) es agregar desencadenadores adicionales a los ya existentes si los nombres de desencadenadores son distintos. Si el nombre de los desencadenadores es el mismo, SQL Server devuelve un mensaje de error. Sin embargo, si el nivel de compatibilidad es igual o menor que 65, cualquier desencadenador creado con la instrucción CREATE TRIGGER substituirá a los desencadenadores existentes del mismo tipo, incluso si los nombres de los desencadenadores son distintos. Para obtener más información, consulte sp_dbcmptlevel.
Desencadenadores recursivos
SQL Server permite también la invocación recursiva de desencadenadores cuando el valor recursive triggers está habilitado en sp_dboption.
Los desencadenadores recursivos permiten dos tipos de repetición:
Repetición indirecta
Repetición directa
Con la repetición indirecta, una aplicación actualiza la tabla T1, que activa el desencadenador TR1 para actualizar la tabla T2. En esta situación, el desencadenador T2 activa y actualiza la tabla T1.
Con la repetición directa, la aplicación actualiza la tabla T1, que activa el desencadenador TR1 para actualizar la tabla T1. Debido a que la tabla T1 se ha actualizado, el desencadenador TR1 se activa de nuevo, y así sucesivamente.
Este ejemplo utiliza ambas repeticiones de desencadenador, directa e indirecta. Suponga que en la tabla T1 se han definido dos desencadenadores de actualización, TR1 y TR2. El desencadenador TR1 actualiza la tabla T1 recursivamente. Una instrucción UPDATE ejecuta cada TR1 y TR2 una vez. Además, la ejecución de TR1 desencadena la ejecución de TR1 (recursivamente) y TR2. Las tablas inserted y deleted de un desencadenador dado contienen filas que corresponden sólo a la instrucción UPDATE que invocó al desencadenador.
Nota El comportamiento anterior sólo se produce si el valor recursive triggers de sp_dboption está habilitado. No hay un orden definido en el que se ejecuten los distintos desencadenadores definidos de un evento dado. Cada desencadenador debe ser independiente.
Deshabilitar recursive triggers sólo evita las repeticiones directas. Para deshabilitar también la repetición indirecta, establezca la opción de servidor nested triggers como 0 utilizando sp_configure.
Si alguno de los desencadenadores realiza una instrucción ROLLBACK TRANSACTION, no se ejecuta ningún desencadenador posterior, independientemente del nivel de anidamiento.
Desencadenadores anidados
Los desencadenadores pueden anidarse hasta un máximo de 32 niveles. Si un desencadenador cambia una tabla en la que hay otro desencadenador, el segundo se activa y puede, entonces, llamar a un tercero, y así sucesivamente. Si algún desencadenador de la cadena causa un bucle infinito, el nivel de anidamiento se habrá sobrepasado, con lo que se cancela el desencadenador. Para deshabilitar los desencadenadores anidados, establezca la opción nested triggers de sp_configure en 0 (desactivada). La configuración predeterminada permite desencadenadores anidados. Si los desencadenadores anidados están desactivados, los desencadenadores recursivos también se deshabilitan, independientemente del valor de recursive triggers de sp_dboption.
Resolución diferida de nombres
SQL Server permite que los procedimientos almacenados, desencadenadores y procesos por lotes de Transact-SQL hagan referencia a tablas que no existen en el momento de la compilación. Esta capacidad se denomina resolución diferida de nombres. Sin embargo, si los procedimientos almacenados, desencadenadores y procesos por lotes de Transact-SQL hacen referencia a una tabla definida en el procedimiento almacenado o desencadenador, se emitirá una advertencia en el momento de la creación sólo si el valor de nivel de compatibilidad (que se establece al ejecutar sp_dbcmptlevel) es igual a 65. Si se utiliza un proceso por lotes, la advertencia se emite en el momento de la compilación. Si la tabla a la que se hace referencia no existe, se devuelve un mensaje de error en tiempo de ejecución. Para obtener más información, consulte Resolución diferida de nombres y compilación.
Permisos
De forma predeterminada, los permisos CREATE TRIGGER son del propietario de la tabla en la que se ha definido el desencadenador, de la función de servidor fija sysadmin y de los miembros de funciones fijas de base de datos db_owner y db_ddladmin, y no se pueden transferir.
Para recuperar datos de una tabla o vista, un usuario debe tener permisos de la instrucción SELECT sobre la tabla o vista. Para actualizar el contenido de una tabla o vista, un usuario debe tener permisos de las instrucciones INSERT, DELETE y UPDATE sobre la tabla o vista.
Si existe un desencadenador INSTEAD OF en una vista, el usuario debe tener privilegios INSERT, DELETE y UPDATE sobre esa vista para ejecutar las instrucciones INSERT, DELETE y UPDATE en la vista, independientemente de si la ejecución realiza realmente esa operación en la vista.
Ejemplos
A. Utilizar un desencadenador con un mensaje de aviso
El siguiente desencadenador de ejemplo imprime un mensaje en el cliente cuando alguien intenta agregar o cambiar datos en la tabla titles.
Nota El mensaje 50009 es un mensaje definido por el usuario en sysmessages. Para obtener más información acerca de la creación de mensajes definidos por el usuario, consulte sp_addmessage.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS RAISERROR (50009, 16, 10)
GO
B. Utilizar un desencadenador con un mensaje de correo electrónico de aviso
Este ejemplo envía un mensaje de correo electrónico a una persona especificada (MaryM) cuando cambia la tabla titles.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
EXEC master.. xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
GO
C. Utilizar una regla de empresa desencadenador entre las tablas employee y jobs
Debido a que las restricciones CHECK sólo pueden hacer referencia a las columnas en que se han definido las restricciones de columna o de tabla, cualquier restricción de referencias cruzadas, en este caso, reglas de empresa, debe definirse como desencadenadores.
Este ejemplo crea un desencadenador que, cuando se inserta o se cambia un nivel de trabajo de empleado, comprueba que el nivel especificado del trabajo del empleado (job_lvls) en el que se basan los salarios se encuentra en el intervalo definido para el trabajo. Para obtener el intervalo adecuado, debe hacerse referencia a la tabla jobs.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'employee_insupd' AND type = 'TR')
DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
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
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
BEGIN
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
RAISERROR ('The level for job_id:%d should be between %d and %d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
END
D. Utilizar la resolución diferida de nombres
El ejemplo siguiente crea dos desencadenadores para ilustrar la resolución diferida de nombres.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig1' AND type = 'TR')
DROP TRIGGER trig1
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER trig1
On authors
FOR INSERT, UPDATE, DELETE
AS
SELECT a.au_lname, a.au_fname, x.info
FROM authors a INNER JOIN does_not_exist x
ON a.au_id = x.au_id
GO
-- Here is the statement to actually see the text of the trigger.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR' and o.name = 'trig1'
-- Creating a trigger on an existing table, but with a nonexistent
-- Column.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig2' AND type = 'TR')
DROP TRIGGER trig2
GO
CREATE TRIGGER trig2
ON authors
FOR INSERT, UPDATE
AS
DECLARE @fax varchar(12)
SELECT @fax = phone
FROM authors
GO
-- Here is the statement to actually see the text of the trigger.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR' and o.name = 'trig2'
E. Utilizar COLUMNS_UPDATED
En este ejemplo se crean dos tablas: una tabla employeeData y una tabla auditEmployeeData. La tabla employeeData, que contiene información confidencial de los sueldos de los empleados, puede ser modificada por los miembros del departamento de recursos humanos. Si se cambia el número de seguridad social del empleado, el sueldo anual o el número de cuenta bancaria, se genera un registro de auditoría y se inserta en la tabla de auditoría auditEmployeeData.
Con la función COLUMNS_UPDATED(), es posible comprobar rápidamente cualquier cambio en estas columnas que contienen información confidencial de los empleados. COLUMNS_UPDATED() sólo se puede utilizar de esta manera para intentar detectar modificaciones 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
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'auditEmployeeData')
DROP TABLE auditEmployeeData
GO
CREATE TABLE employeeData (
emp_id int NOT NULL,
emp_bankAccountNumber char (10) NOT NULL,
emp_salary int NOT NULL,
emp_SSN char (11) NOT NULL,
emp_lname nchar (32) NOT NULL,
emp_fname nchar (32) NOT NULL,
emp_manager int NOT NULL
)
GO
CREATE TABLE auditEmployeeData (
audit_log_id uniqueidentifier 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 sysname DEFAULT SUSER_SNAME(),
audit_changed datetime DEFAULT GETDATE()
)
GO
CREATE TRIGGER updEmployeeData
ON employeeData
FOR update AS
/*Check whether columns 2, 3 or 4 has been updated. If any or all of columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/
IF (COLUMNS_UPDATED() & 14) > 0
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/
BEGIN
-- Audit OLD record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'OLD',
del.emp_id,
del.emp_bankAccountNumber,
del.emp_salary,
del.emp_SSN
FROM deleted del
-- Audit NEW record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'NEW',
ins.emp_id,
ins.emp_bankAccountNumber,
ins.emp_salary,
ins.emp_SSN
FROM inserted ins
END
GO
/*Inserting a new employee does not cause the UPDATE trigger to fire.*/
INSERT INTO employeeData
VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32)
GO
/*Updating the employee record for employee number 101 to change the salary to 51000 causes the UPDATE trigger to fire and an audit trail to be produced.*/
UPDATE employeeData
SET emp_salary = 51000
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO
/*Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.*/
UPDATE employeeData
SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO
F. Utilizar COLUMNS_UPDATED para probar más de 8 columnas
Si tiene que probar actualizaciones que afectan a otras columnas que no sean las 8 primeras de una tabla, debe utilizar la función SUBSTRING para probar si COLUMNS_UPDATED devuelve el bit correcto. Este ejemplo prueba las actualizaciones que afectan a las columnas 3, 5 o 9 de la tabla Northwind.dbo.Customers.
USE Northwind
DROP TRIGGER tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
)
PRINT 'Columns 3, 5 and 9 updated'
GO
UPDATE Customers
SET ContactName=ContactName,
Address=Address,
Country=Country
GO
Véase también
Alter table
Alter trigger
Create table
DROP TRIGGER
Programar procedimientos almacenados
sp_depends
sp_help
sp_helptext
sp_rename
sp_settriggerorder
sp_spaceused
Utilizar identificadores
©1988-2000 Microsoft Corporation. Reservados todos los derechos.