Triggers en Transact SQL

Un trigger( o desencadenador) es una clase especial de procedimiento almacenado que se ejecuta automáticamente cuando se produce un evento en el servidor de bases de datos.

SQL Server proporciona los siguientes tipos de triggers:

Trigger DML, se ejecutan cuando un usuario intenta modificar datos mediante un evento de lenguaje de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE de una tabla o vista.
Trigger DDL, se ejecutan en respuesta a una variedad de eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROP de Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL.

Trigger DML.

Los trigger DML se ejecutan cuando un usuario intenta modificar datos mediante un evento de lenguaje de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE de una tabla o vista.

La sintaxis general de un trigger es la siguiente.

CREATE TRIGGER <Trigger_Name, sysname, Trigger_Name>
ON <Table_Name, sysname, Table_Name>
AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
END

Antes de ver un ejemplo es necesario conocer las tablas inserted y deleted.

Las instrucciones de triggers DML utilizan dos tablas especiales denominadas inserted y deleted. SQL Server 2005 crea y administra automáticamente ambas tablas. La estructura de las tablas inserted y deleted es la misma que tiene la tabla que ha desencadenado la ejecución del trigger.

La primera tabla (inserted) solo está disponible en las operaciones INSERT y UPDATE y en ella están los valores resultantes despues de la inserción o actualización. Es decir, los datos insertados. Inserted estará vacia en una operación DELETE.

En la segunda (deleted), disponible en las operaciones UPDATE y DELETE, están los valores anteriores a la ejecución de la actualización o borrado. Es decir, los datos que serán borrados. Deleted estará vacia en una operacion INSERT.

¿No existe una tabla UPDATED? No, hacer una actualización es lo mismo que borrar (deleted) e insertar los nuevos (inserted). La sentencia UPDATE es la única en la que inserted y deleted tienen datos simultaneamente.

No puede se modificar directamente los datos de estas tablas.

El siguiente ejemplo, graba un historico de saldos cada vez que se modifica un saldo de la tabla cuentas.

CREATE TRIGGER TR_CUENTAS
ON CUENTAS
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON impide que se generen mensajes de texto
-- con cada instrucción
SET NOCOUNT ON;
INSERT INTO HCO_SALDOS
(IDCUENTA, SALDO, FXSALDO)
SELECT IDCUENTA, SALDO, getdate()
FROM INSERTED
END

La siguiente instrucción provocará que el trigger se ejecute:

UPDATE CUENTAS
SET SALDO = SALDO + 10
WHERE IDCUENTA = 1

Una consideración a tener en cuenta es que el trigger se ejecutará aunque la instruccion DML (UPDATE, INSERT o DELETE ) no haya afectado a ninguna fila. En este caso inserted y deleted devolveran un conjunto de datos vacio.

Podemos especificar a que columnas de la tabla debe afectar el trigger.

ALTER TRIGGER TR_CUENTAS
ON CUENTAS
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON impide que se generen mensajes de texto
-- con cada instrucción
SET NOCOUNT ON;

IF UPDATE(SALDO) -- Solo si se actualiza SALDO
BEGIN
INSERT INTO HCO_SALDOS
(IDCUENTA, SALDO, FXSALDO)
SELECT IDCUENTA, SALDO, getdate()
FROM INSERTED
END
END

Los trigger están dentro de la transacción original (Insert, Delete o Update) por lo cual si dentro de nuestro trigger hacemos un RollBack Tran, no solo estaremos echando atrás nuestro trigger sino también toda la transacción; en otras palabras si en un trigger ponemos un RollBack Tran, la transacción de Insert, Delete o Update volverá toda hacia atrás.

ALTER TRIGGER TR_CUENTAS
ON CUENTAS
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON impide que se generen mensajes de texto
-- con cada instrucción
SET NOCOUNT ON;
INSERT INTO HCO_SALDOS
(IDCUENTA, SALDO, FXSALDO)
SELECT IDCUENTA, SALDO, getdate()
FROM INSERTED

ROLLBACK
END

En este caso obtendremos el siguiente mensaje de error:

La transacción terminó en el desencadenador. Se anuló el lote.

Podemos activar y desactivar Triggers a tarvés de las siguientes instrucciones.

-- Desactiva el trigger TR_CUENTAS
DISABLE TRIGGER TR_CUENTAS ON CUENTAS
GO
-- activa el trigger TR_CUENTAS
ENABLE TRIGGER TR_CUENTAS ON CUENTAS
GO
-- Desactiva todos los trigger de la tabla CUENTAS
ALTER TABLE CUENTAS DISABLE TRIGGER ALL
GO
-- Activa todos los trigger de la tabla CUENTAS
ALTER TABLE CUENTAS ENABLE TRIGGER ALL

Trigger DDL

Los trigger DDL se ejecutan en respuesta a una variedad de eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROP de Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL.

La sintaxis general de un trigger es la siguiente.

CREATE TRIGGER <trigger_name, sysname, table_alter_drop_safety>
ON DATABASE
FOR <data_definition_statements, , DROP_TABLE, ALTER_TABLE>
AS
BEGIN
...
END

La siguiente instrucción impide que se ejecuten sentencias DROP TABLE y ALTER TABLE en la base de datos.

CREATE TRIGGER TR_SEGURIDAD
ON DATABASE FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
RAISERROR ('No está permitido borrar ni modificar tablas !' , 16, 1)
ROLLBACK TRANSACTION
END

Inicio | Tutorial de Transact SQL

Funciones integradas de Transact SQL (I)

Versión para imprimir

Foros de consulta

		Muy Buena Informacion por Christian Respuesta recibida el [14/03/2008 05:25:00]
		Hola como estas solo keria decirte que tu trabajo es muy bueno y q me sirvio de mucho, resolvi muchas de mis dudas.

		MUY BIEN por ESTEBAN Respuesta recibida el [26/03/2008 07:07:10]
		Esta muuy toda esta informacion que tienes me saco de varias dudas. Tambien me gustaria que combinaras Triggers y Cursores eso esta estaria mucho mejor

		3158488742 por lesly Respuesta recibida el [02/04/2008 08:48:57]
		como hago para crear un tipo de dato AUTO NUMERICO EN SQL SERVER 2005

		Gracias por Favio Respuesta recibida el [09/04/2008 09:56:53]
		Seguí así

		impecable por romamuy Respuesta recibida el [20/05/2008 02:41:42]
		me ayudo pila gracias ojala los tutoriales q encuentro fueran tan claros xD saludos

		Me gusto, pero... por Ale Respuesta recibida el [29/05/2008 08:59:38]
		Esta muy bien explicado pero me gustaria que agregaran mas cosas sobre triggers y como combinarlos con procedimientos almacenados....

		Muy Bien por Johnfer Respuesta recibida el [30/05/2008 11:15:16]
		La explicación es Excelente, gracias, desearía saber como se puede hacer los Trigger con la utilización de varias tablas y como combinarlos con procedimientos

		Excelente =) por BrAuLio Respuesta recibida el [06/06/2008 04:02:08]
		Solo queria comentar que gracias a esto resolvi grandes dudas a resolver os agradesco gracias.

		Felicidades por cristhian Respuesta recibida el [08/06/2008 11:18:00]
		Tienes muy buenos conocientos

		Muchas gracias por Aterelo Respuesta recibida el [09/06/2008 04:27:48]
		Es grato encontrar informacion que realmente ayuda, pues en muchos sitios explican y explican 20 mil cosas y uno termina por no entender nada. Muy buena informacion, clara y al grano.

Replica de BD - SQL 2005 por Emerson
Respuesta recibida el [10/06/2008 03:52:50]

hola amigo, a todos tengo un tarea en el lugar que estudio el cual es Replica de Base de Datos SQL Server Management Studio 2005, y la verdad no se a que se refiere con eso estube buscando informacion per nada entendi, el trabajo que me dejo el profesor es la implementacion de replica de base de datos obviamente sql. si tu o alguien tiene algun archivo o tutorial que me permita lograr esto le estare muy agradecido mi correo es rebraqui@gmail.com.
gracias

		Gracias por Pedro Hernandez (mx) Respuesta recibida el [17/09/2008 10:50:36]
		gracias por tu publicación, me ha servido bastante, he aclarado varias dudas acerca de los triggers. Sigue asi.

		buen trabajo por rodrigo lara Respuesta recibida el [02/10/2008 01:28:35]
		gracias!, me sirvio de mucho tu ayuda, sique asi.

		Excelente por Eidem Respuesta recibida el [13/10/2008 04:09:15]
		Excelente Mi Buen. Claro y Conciso... Te sacaste un 10 con este tutorial. Gracias !!!

		Gracias por Karlos Respuesta recibida el [14/10/2008 09:23:19]
		muy buen minitutorial sobre el manejo de desencadenantes en SQLServer.

		como se modifica un trigger y ... por Anónimo Respuesta recibida el [17/10/2008 12:19:24]
		como se modifica un trigger y que agregue despues de un insert y update haga un select en una tabla

Trigger para formar Llave por antonio de Guatemala
Respuesta recibida el [29/10/2008 05:39:39]

Rapidamente, necesito crear una llave con 2 campos, anio y un correlativo por año,  si fuera 2008+00025, queraria 200800025, el 25 es un numero que deberia incrementarse por año.

La verdad no se si se hace en triger o se hace en otra parte, espero tener un camino por donde tomar.

		Triggers por JP Respuesta recibida el [05/11/2008 01:30:16]
		Está todo barbaro, pero cuales son las variables que tengo para manipular la tupla que agregué o borré de la tabla. Ej. en Oracle tengo :new y :old... cuales son los equivalentes aca??

		RE:Triggers por JP por TOXIKO Respuesta recibida el [06/11/2008 12:15:31]
		"NEW" HACE REFERENCIA A LA TABLA NUEVA, LO QUE EN T-SQL VENDRIA A SER "INSERTED". "OLD" HACE REFERENCIA A LA TABLA ANTES DE SER MODIFICADA O BORRADA, LO QUE EN T-SQL VENDRIA A SER "DELETED".

		se agradese por marciano_l Respuesta recibida el [19/11/2008 10:53:33]
		es muy buena la pagina me oarece que es la mejor que encontre sobre el tema te detalle todo lo necesario para cada consulta, compadre sique asi.

		Muy Claro el mini Tutorial por EDITH Respuesta recibida el [03/12/2008 07:58:44]
		En verdad exelente lo tuyo,e me aclararon muchas dudas.Thank you

		Excelente por Vicent Respuesta recibida el [18/12/2008 11:55:32]
		Esta muy bueno, esta info. sobre los TRIGGER, ahora lo que yo nesecito saber es, se puede obteber la info. que se va a modificar y la que info. nueva. es para una bitacora...

		Enhorabuena por Luis Respuesta recibida el [28/01/2009 04:56:43]
		Enhorabuena por tu web. Este tutorial me parece magnífico. Claro, bastante completo y al mismo tiempo conciso. Muchas gracias, me ha sido de gran ayuda.

		Felicitaciones por Miguel Respuesta recibida el [30/01/2009 07:58:39]
		Men esta pagina es excelente no se imaginas la ayuda que me a dado para solucionar mis problemas muchas gracias de verdad es muy buena esta pagina

		delete por hector Respuesta recibida el [11/02/2009 04:44:35]
		quisiera que pongas un ejemplo de como puedo usar un trigger para impedir que se eliminen los 'values' de una tabla

tbi por alicia
Respuesta recibida el [18/02/2009 05:33:04]

buenos dias, quisiera hacer una consulta,se hizo unos trigger(trigger before insert),q se puso por ejempl (ALTER Trigger [dbo].[TBI_PROPAUTO] ON [dbo].[PROPAUTO]
instead of INSERT) y pensamos q iba a insertar, pero q ahora nos dimos cuenta q no funciona, como podemos hacer un before insert en sql server?

instead of INSERT por devjoker
Respuesta recibida el [18/02/2009 05:40:00]

Un trigger instead of INSERT sustituye el insert realizado por las acciones que haye el trigger. Se usan sobre vistas, de forma que usuario inserta contra una vista y el trigger se encarga de grabar en la(s) tablas.

		Lista de Triggers por Artgax Respuesta recibida el [27/02/2009 12:38:22]
		como puedo saber cuales y cuantos triggers tengo activados en una base de datos

		buen aportee por lalo Respuesta recibida el [22/04/2009 01:34:36]
		me servio de mucho la neta

		tiggers 2005 a 2008 por ss Respuesta recibida el [24/05/2009 02:33:15]
		bueno dime man hay diferencias haciendo tiggers en el 2005 a 2008 osea si 2008 trae algo nuevo x favor contestar

		Triggers por Mr Purple Respuesta recibida el [26/06/2009 02:13:54]
		Hola buenas noches... Muchas gracias por la informacion, no sabes cuanto sufri por este tema, pero ahora lo he entendido... Muchas gracias...

		ARIGATO por SHURA DES Respuesta recibida el [28/06/2009 06:13:31]
		GRAN INFO LA QUE PONES MUY AGREDECIDO DIME ESTO TAMBIEN FUNCIONA CON LA NUEVA VERCION EL SQL2008

		NO ME FUNCIONA por BEYA Respuesta recibida el [03/07/2009 05:56:13]
		MUCHAS GRACIAS POR ESTA INFORMACIÓN PERO, NO SE MUCHO DE TIGGER Y ESTOY BUSCANDO ALGO QUE IMPIDA QUE SE BORREN LAS TABLAS TRANSACCIONALES PERO ME DA ERROR EN LA PALABRA FOR

		fierro por rosamel Respuesta recibida el [06/07/2009 02:36:10]
		vale compi

		Trigger de Update por Ringo MB Respuesta recibida el [29/07/2009 09:54:38]
		Me sirvio muchisimo lo ue explica Pedro y tambien los comentarios. muchas gracias MB

		encontre lo que buscaba, hay c... por Angel Respuesta recibida el [05/08/2009 07:28:38]
		encontre lo que buscaba, hay cosas que no merecen dedicarles tiempo pero el tutorial vale la pena, lo leere completo, gracias

		Ubicación de los triggers por krad 962 Respuesta recibida el [03/09/2009 09:19:32]
		Buenas queria preguntarles donde se almacenan lostriggers. ya que he creado algunos, funcionan pero a la hora de buscarlos no se donde se almacenan para poder realizarles alguna modificacion

confusion :( por xero
Respuesta recibida el [07/09/2009 11:25:31]

holaaaa es qeu quede con la duda de como haria el equivalente de old y new de oracle ejemplo la tabla se llama clientes y tiene id, nombre, edad

si queiro hacer una comparacion
if :new.edad > old.edad 

como haria eso en sql 
gracias

		isi por graciala Respuesta recibida el [16/09/2009 10:13:40]
		como hago para mejorar en conocimientos de sql server

		Gracias por David Respuesta recibida el [13/11/2009 09:03:49]
		Me sirvio de mucho era exactamente loe que necesitaba para empezar

		Formidable, muchas gracias... por Misterandovas Respuesta recibida el [02/12/2009 02:05:38]
		Formidable, muchas gracias

		Distributed transaction completed por Eduardo French Respuesta recibida el [04/01/2010 11:11:58]
		Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

		ano... por chet Respuesta recibida el [15/01/2010 06:44:29]
		tmr.. no dijo nada

		gracias mejor explicacion imposible por Eder Respuesta recibida el [18/01/2010 07:54:58]
		gracias por todo la mejor pagina de trigger que encontre

		EXCELENTE por VIKLO Respuesta recibida el [28/01/2010 07:02:11]
		EXCELENTE PAGINA E INFORMACION, FUE LA MEJOR PAGINA QUE ENCONTRE DE SQL. MUCHAS GRACIAS.

		Graciaaas por Daniel Respuesta recibida el [02/02/2010 12:35:56]
		Gracias amigo, me sirvio BASTANTE, no tenia idea de como hacer un TRIGGER =), y aqui ya aprendi hacer algunos, incluso lo de ROLLBACK TRAN, me saco del agujero

		Felicidades, que explicacion m... por Houseman Respuesta recibida el [02/02/2010 01:56:33]
		Felicidades, que explicacion mas precisa, clara, sencilla y practica.

		replicas entre sql server 2000 y mysql 5.0 por david Respuesta recibida el [03/02/2010 12:39:16]
		como configuro una replica entre sql server 2000 y mysql 5.0

Contenidos

Foros

Triggers en Transact SQL

Trigger DML.

Trigger DDL