Pregunta sobre triggers

De acuerdo a lo que realizas de trigger, inserción, con los valores de lo que insertas puedes utilizar la instrucción execute para procesar un stored procedure.
Checa lo siguiente de la ayuda:
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.
Execute
Ejecuta una función definida por el usuario y que devuelve valores escalares, un procedimiento del sistema, un procedimiento almacenado definido por el usuario o un procedimiento almacenado extendido. Admite también la ejecución de una cadena de caracteres en un proceso por lotes Transact-SQL.
Para llamar a una función, utilice la sintaxis que se describe para EXECUTE stored_procedure.
Sintaxis
Ejecutar un procedimiento almacenado:
[ [ EXEC [ UTE ] ]
{
[ @return_status = ]
{ procedure_name [ ;number ] | @procedure_name_var
}
[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ]
[ ,...n ]
[ WITH RECOMPILE ]
Ejecutar una cadena de caracteres:
EXEC [ UTE ] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )
Argumentos
@return_status
Es una variable entera opcional que almacena el estado de retorno de un procedimiento almacenado. Esta variable debe declararse en el proceso por lotes, en el procedimiento almacenado o en la función para que se pueda utilizar en una instrucción EXECUTE.
Cuando se utiliza para llamar a una función definida por el usuario que devuelve valores escalares, la variable @return_status puede ser de cualquier tipo de datos escalar.
procedure_name
Es el nombre, completamente calificado o no, del procedimiento almacenado al que debe llamar. Los nombres de los procedimientos deben seguir las reglas para los identificadores. Para obtener más información, consulte Utilizar identificadores. Los nombres de los procedimientos almacenados extendidos distinguen siempre entre mayúsculas y minúsculas, sin tener en cuenta la página de códigos o el orden del servidor.
Un procedimiento que se haya creado en otra base de datos se puede ejecutar si el usuario que lo ejecuta es el propietario del mismo o dispone de los permisos adecuados para ejecutarlo en esa base de datos. ¿Un procedimiento puede ejecutarse en otro servidor que esté ejecutando Microsoft® SQL Server? Si el usuario que ejecuta el procedimiento tiene los permisos adecuados para utilizar ese servidor (acceso remoto) y para ejecutar el procedimiento en esa base de datos. Si se especifica un nombre de servidor, pero no se especifica nombre de base de datos, SQL Server busca el procedimiento en la base de datos predeterminada del usuario.
;number
Es un entero opcional que se emplea para agrupar procedimientos del mismo nombre, de forma que se puedan quitar con una única instrucción DROP PROCEDURE. Este parámetro no se utiliza para los procedimientos almacenados extendidos.
Los procedimientos utilizados en la misma aplicación se agrupan a menudo de esta forma. Por ejemplo, los procedimientos empleados con la aplicación de pedidos se pueden llamar orderproc;1, orderproc;2, etc. La instrucción DROP PROCEDURE orderproc quita el grupo completo. Una vez agrupados los procedimientos, los procedimientos individuales pertenecientes a un grupo no se pueden quitar. Por ejemplo, la instrucción DROP PROCEDURE orderproc;2 no está permitida. Para obtener más información acerca de los grupos de procedimientos, consulte CREATE PROCEDURE.
@procedure_name_var
Es el nombre de la variable definida localmente, que representa el nombre de un procedimiento almacenado.
@parameter
Es el parámetro de un procedimiento, tal como se define en la instrucción CREATE PROCEDURE. Los nombres de parámetro deben precederse del signo (@). Cuando se utilice con la forma @parameter_name = value, los nombres de parámetro y las constantes no tienen que proporcionarse en el orden en el que se han definido en la instrucción CREATE PROCEDURE. No obstante, si se emplea la forma @parameter_name = value para cualquier parámetro, debe utilizarse para todos los parámetros siguientes.
De forma predeterminada, los parámetros pueden aceptar valores NULL. Si un parámetro tiene valor NULL y se utiliza en una instrucción CREATE o ALTER TABLE en la que la columna a la que se hace referencia no admite NULL (por ejemplo, insertar en una columna que no permite NULL), SQL Server generará un error. Para impedir que se pase un valor de parámetro NULL a una columna que no admita NULL, agregue lógica de programación al procedimiento o utilice un valor predeterminado (con la palabra clave DEFAULT de CREATE o ALTER TABLE) para la columna.
value
Es el valor del parámetro para el procedimiento. Si no se especifican los nombres de los parámetros, sus valores deben proporcionarse en el orden definido en la instrucción CREATE PROCEDURE.
Si el valor de un parámetro es un nombre de objeto o cadena de caracteres, o está calificado mediante un nombre de base de datos o nombre de propietario, el nombre completo debe escribirse entre comillas simples. Si el valor de un parámetro es una palabra clave, ésta debe escribirse entre comillas dobles.
Si se define un valor predeterminado en la instrucción CREATE PROCEDURE, un usuario podrá ejecutar el procedimiento sin especificar ningún parámetro. El valor predeterminado debe ser una constante y puede incluir los caracteres comodín %, _, [ ] y [^] si el procedimiento utiliza el nombre del parámetro con la palabra clave LIKE.
El valor predeterminado puede ser también NULL. Normalmente, la definición del procedimiento especifica la acción que debe realizarse si el valor del parámetro es NULL.
@variable
Es la variable que almacena un parámetro o un parámetro devuelto.
Output
Especifica que el procedimiento almacenado devuelve un parámetro. El parámetro coincidente del procedimiento almacenado debe haberse creado también con la palabra clave OUTPUT. Utilice esta palabra clave cuando emplee variables de cursor como parámetros.
Si se están utilizando parámetros OUTPUT y la intención es usar los valores de retorno en otras instrucciones del proceso por lotes o procedimiento que realiza la llamada, el valor del parámetro debe pasarse como una variable (es decir, @parameter = @variable). No se puede ejecutar un procedimiento en el que se especifique OUTPUT para un parámetro que no se ha definido como parámetro OUTPUT en la instrucción CREATE PROCEDURE. Las constantes no se pueden pasar a los procedimientos almacenados mediante OUTPUT; el parámetro devuelto requiere un nombre de variable. El tipo de datos de la variable debe estar declarado y se le debe haber asignado un valor para poder ejecutar el procedimiento. Los parámetros devueltos pueden ser de cualquier tipo de datos, excepto del tipo text o image.
DEFAULT
Proporciona el valor predeterminado del parámetro tal como se define en el procedimiento. Cuando el procedimiento espera un valor para un parámetro que no tiene un valor predeterminado definido y, o bien falta el parámetro o se especifica la palabra clave DEFAULT, se produce un error.
n
Es un marcador de posición que indica que los elementos anteriores pueden repetirse muchas veces. Por ejemplo, EXECUTE puede especificar uno o más elementos @parameter, value o @variable.
WITH RECOMPILE
Exige que se compile un nuevo plan. Utilice esta opción si el parámetro que está proporcionando es atípico o si los datos han cambiado de forma significativa. El plan cambiado se emplea en las ejecuciones siguientes. Esta opción no se utiliza para los procedimientos almacenados extendidos. Se recomienda que emplee esta opción con cautela, porque es costosa.
@string_variable
Es el nombre de una variable local. @string_variable puede ser del tipo de datos char, varchar, nchar o nvarchar, con un valor máximo igual a la memoria disponible del servidor. Si la cadena es superior a 4000 caracteres, concatene múltiples variables locales para utilizar con la cadena EXECUTE. Para obtener más información acerca de los tipos de datos de SQL Server proporcionados por el sistema, consulte Tipos de datos.
[N]'tsql_string'
Es una cadena de constante. Tsql_string puede ser del tipo de datos nvarchar o varchar. Si se incluye la N, la cadena se interpreta como del tipo de datos nvarchar, con un valor máximo igual a la memoria disponible del servidor. Si la cadena es superior a 4000 caracteres, concatene múltiples variables locales para utilizar con la cadena EXECUTE.
Observaciones
Si los tres primeros caracteres del nombre de procedimiento son sp_, SQL Server busca el procedimiento en la base de datos master. Si no se proporciona ningún nombre calificado de procedimiento, SQL Server busca el procedimiento como si el nombre del propietario fuese dbo. Para resolver el nombre del procedimiento almacenado como si fuese un procedimiento almacenado definido por el usuario con el mismo nombre que un procedimiento almacenado del sistema, proporcione el nombre del procedimiento completamente calificado.
Los parámetros se pueden proporcionar mediante value o con @parameter_name = value. Un parámetro no es parte de una transacción; por tanto, si un parámetro se cambia en una transacción que posteriormente se deshace, el valor del parámetro no vuelve a su valor anterior. El valor devuelto al procedimiento llamante es siempre el valor del parámetro en el momento en que finaliza el procedimiento llamado.
El anidamiento se produce cuando un procedimiento almacenado llama a otro. El nivel de anidamiento se aumenta cuando el procedimiento llamado comienza la ejecución y disminuye cuando el procedimiento llamado termina. Si se excede el máximo de 32 niveles de anidamiento, habrá un error de la cadena completa de procedimientos de llamada. El nivel actual de anidamiento se almacena en la función @@NESTLEVEL.
SQL Server utiliza en la actualidad los valores de retorno 0 a -14 para indicar el estado de ejecución de los procedimientos almacenados. Los valores de -15 a -99 están reservados para uso futuro. Para obtener más información acerca de la lista de valores reservados para estados de retorno, consulte RETURN.
Debido a que ni los procedimientos almacenados ni los procedimientos almacenados extendidos se encuentran dentro del alcance de una transacción (a menos que se ejecuten en una instrucción BEGIN DISTRIBUTED TRANSACTION o que se utilicen con varias opciones de configuración), los comandos que se ejecutan mediante llamadas a ellos no se pueden deshacer. Para obtener más información, consulte Procedimientos almacenados del sistema y BEGIN DISTRIBUTED TRANSACTION.
Se genera un error si utiliza y ejecuta un procedimiento que pasa, como parámetro de entrada, una variable de cursor con cursor asignado.
Cuando ejecute procedimientos almacenados, no es necesario que especifique la palabra clave EXECUTE si la instrucción es la primera de un proceso por lotes.
Utilizar EXECUTE con una cadena de caracteres
Utilice el operador de concatenación de cadenas (+) para crear grandes cadenas para ejecución dinámica. Cada expresión de cadena puede ser una mezcla de tipos de datos Unicode y no Unicode.
Aunque cada [N] 'tsql_string' o @string_variable debe contener menos de 8000 bytes, la concatenación se realiza lógicamente en el analizador de SQL Server y nunca llega a materializarse en memoria. Por ejemplo, esta instrucción no produce nunca la cadena concatenada esperada de 16000 caracteres:
EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')
Las instrucciones incluidas en la instrucción EXECUTE no se compilan hasta que se ejecute la instrucción EXECUTE.
Los cambios en el contexto de la base de datos sólo duran hasta el final de la instrucción EXECUTE. Por ejemplo, después de la instrucción EXEC de este ejemplo, el contexto de base de datos es master.
USE master EXEC ("USE pubs") SELECT * FROM authors
Permisos
Los permisos EXECUTE de un procedimiento almacenado son, de forma predeterminada, del propietario del procedimiento almacenado, que puede transferirlos a otros usuarios. Los permisos para utilizar las instrucciones de la cadena EXECUTE se comprueban en el momento en que se detecta EXECUTE, incluso si la instrucción EXECUTE se incluye en un procedimiento almacenado. Cuando se ejecuta un procedimiento almacenado que ejecuta una cadena, los permisos se comprueban en el contexto del usuario que ejecuta el procedimiento, no en el contexto del usuario que creó el procedimiento. Sin embargo, si un usuario es el propietario de dos procedimientos almacenados en que el primero llama al segundo, entonces la comprobación de permisos de EXECUTE no se realiza para el segundo procedimiento almacenado.
Ejemplos
A. Utilizar EXECUTE para pasar un único parámetro
El procedimiento almacenado showind espera un parámetro (@tabname), un nombre de tabla. El siguiente ejemplo ejecuta el procedimiento almacenado showind con titles como valor del parámetro.
Nota El procedimiento almacenado showind se muestra únicamente con propósitos ilustrativos y no existe en la base de datos pubs.
EXEC showind titles
La variable se puede llamar explícitamente en la ejecución:
EXEC showind @tabname = titles
Si esta es la primera instrucción en un proceso por lotes o en una secuencia de comandos isql, no se requiere EXEC:
Showind titles
- O bien -
showind @tabname = titles
B. Utilizar múltiples parámetros y un parámetro de salida
El ejemplo siguiente ejecuta el procedimiento almacenado roy_check, que pasa tres parámetros. El tercer parámetro, @pc, es un parámetro OUTPUT. Una vez que se ha ejecutado el procedimiento, el valor de retorno está disponible en la variable @percent.
Nota El procedimiento almacenado roy_check se muestra sólo con propósitos ilustrativos y no existe en la base de datos pubs.
DECLARE @percent int
EXECUTE roy_check 'BU1032', 1050, @pc = @percent OUTPUT
SET Percent = @percent
C. Utilizar EXECUTE 'tsql_string' con una variable
El ejemplo siguiente muestra cómo administra EXECUTE las cadenas construidas dinámicamente que contienen variables. El ejemplo siguiente crea el cursor tables_cursor para que contenga una lista de todas las tablas definidas por el usuario (type = U).
Nota Se muestra este ejemplo sólo con propósitos ilustrativos.
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
/* A @@FETCH_STATUS of -2 means that the row has been deleted.
There is no need to test for this because this loop drops all
user-defined tables. */.
EXEC ('DROP TABLE ' + @tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END
PRINT 'All user-defined tables have been dropped from the database.'
DEALLOCATE tables_cursor
D. Utilizar EXECUTE con un procedimiento almacenado remoto
El ejemplo siguiente ejecuta el procedimiento almacenado checkcontract en el servidor remoto SQLSERVER1 y almacena el estado de retorno, que indica éxito o fracaso, en @retstat.
DECLARE @retstat int
EXECUTE @retstat = SQLSERVER1.pubs.dbo.checkcontract '409-56-4008'
E. Utilizar EXECUTE con un procedimiento almacenado extendido
El ejemplo siguiente utiliza el procedimiento almacenado extendido xp_cmdshell para enumerar un directorio de todos los archivos con una extensión de nombre de archivo .exe.
USE master
EXECUTE xp_cmdshell 'dir *.exe'
F. Utilizar EXECUTE con una variable de procedimiento almacenado
El ejemplo siguiente crea una variable que representa un nombre de procedimiento almacenado.
DECLARE @proc_name varchar(30)
SET @proc_name = 'sp_who'
EXEC @proc_name
G. Utilizar EXECUTE con DEFAULT
El ejemplo siguiente crea un procedimiento almacenado con valores predeterminados para el primer y tercer parámetros. Cuando se ejecuta el procedimiento, estos valores predeterminados se insertan como parámetros primero y tercero si no se pasa ningún valor en la llamada o si se especifica el valor predeterminado. Observe las distintas formas en las que se puede utilizar la palabra clave DEFAULT.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc_calculate_taxes' AND type = 'P')
DROP PROCEDURE proc_calculate_taxes
GO
-- Create the stored procedure.
CREATE PROCEDURE proc_calculate_taxes (@p1 smallint = 42, @p2 char(1),
@p3 varchar(8) = 'CAR')
AS
SELECT *
FROM mytable
El procedimiento almacenado proc_calculate_taxes se puede ejecutar con muchas combinaciones:
EXECUTE proc_calculate_taxes @p2 = 'A'
EXECUTE proc_calculate_taxes 69, 'B'
EXECUTE proc_calculate_taxes 69, 'C', 'House'
EXECUTE proc_calculate_taxes @p1 = DEFAULT, @p2 = 'D'
EXECUTE proc_calculate_taxes DEFAULT, @p3 = 'Local', @p2 = 'E'
EXECUTE proc_calculate_taxes 69, 'F', @p3 = DEFAULT
EXECUTE proc_calculate_taxes 95, 'G', DEFAULT
EXECUTE proc_calculate_taxes DEFAULT, 'H', DEFAULT
EXECUTE proc_calculate_taxes DEFAULT, 'I', @p3 = DEFAULT
Véase también
+ (concatenación de cadenas)
[ ] (comodín ? carácter o caracteres de coincidencia)
@@NESTLEVEL
ALTER PROCEDURE
DECLARE @local_variable
DROP PROCEDURE
Funciones
sp_depends
sp_helptext
©1988-2000 Microsoft Corporation. Reservados todos los derechos.

No lo veo óptimo, ya que los triggers son ejecutados en el momento de la inserción, porque no generas un sp que te haga el trabajo y que sea llamado dentro del trigger.

¿Y cómo se hace eso?
Muchas gracias.
Saludos.