CREATE TRIGGER (Transact-SQL)

Sintaxis

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ] 
[ NOT FOR REPLICATION ] 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name 
ON { ALL SERVER | DATABASE } 
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name 
ON ALL SERVER 
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Argumentos

• schema_name
  Es el nombre del esquema al que pertenece un desencadenador DML. Los desencadenadores DML se limitan al esquema de la tabla o vista en la que se crearon. No se puede especificar schema_name para desencadenadores DDL o logon.

• trigger_name
  Es el nombre del desencadenador. El parámetro trigger_name debe cumplir con las reglas de los identificadores, con la excepción de que trigger_name no puede comenzar con los símbolos # o ##.

• table | view
  Es la tabla o vista en que se ejecuta el desencadenador DML; algunas veces se denomina tabla del desencadenador o vista del desencadenador. Especificar el nombre completo de la tabla o vista es opcional. Sólo se puede hacer referencia a una vista mediante un desencadenador INSTEAD OF. No es posible definir desencadenadores DML en tablas temporales locales o globales.

• DATABASE
  Aplica el ámbito de un desencadenador DDL a la base de datos actual. Si se especifica, el desencadenador se activa cada vez que event_type o event_group tienen lugar en la base de datos actual.

• ALL SERVER
  Aplica el ámbito de un desencadenador DDL o logon al servidor actual. Si se especifica, el desencadenador se activa cada vez que event_type o event_group tienen lugar en la base de datos actual.

• WITH ENCRYPTION
  Ofusca el texto de la instrucción CREATE TRIGGER. El uso de WITH ENCRYPTION impide que el desencadenador se publique como parte de la replicación de SQL Server. WITH ENCRYPTION no se puede especificar para desencadenadores CLR.

• EXECUTE AS
  Especifica el contexto de seguridad en el que se ejecuta el desencadenador. Permite controlar qué cuenta de usuario utiliza la instancia de SQL Server para validar los permisos sobre cualquier objeto de base de datos al que haga referencia el desencadenador.

  Para obtener más información, vea EXECUTE AS (cláusula de Transact-SQL).

• FOR | AFTER
  AFTER especifica que el desencadenador DML 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 cuando 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 DML en vez de la instrucción SQL desencadenadora, por lo que se suplantan las acciones de las instrucciones desencadenadoras. INSTEAD OF no se puede especificar para los desencadenadores DDL o logon.

  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 pueden utilizar en vistas actualizables que usan WITH CHECK OPTION. SQL Server genera un error cuando se agrega un desencadenador INSTEAD OF a una vista actualizable para la que se especificó WITH CHECK OPTION. El usuario debe quitar esta opción mediante ALTER VIEW antes de definir el desencadenador INSTEAD OF.

• { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
  Especifica las instrucciones de modificación de datos que activan el desencadenador DML cuando se intenta 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 estas opciones, en cualquier orden.

  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.

• event_type
  Es el nombre de un evento de lenguaje Transact-SQL que hace que, una vez ejecutado, se active un desencadenador DDL. Los eventos válidos para los desencadenadores DDL se enumeran en Eventos DDL.

• event_group
  Es el nombre de un agrupamiento predefinido de eventos de lenguaje de Transact-SQL. El desencadenador DDL se activa tras la ejecución de cualquier evento de lenguaje Transact-SQL que pertenezca a event_group. Los grupos de eventos válidos para los desencadenadores DDL se enumeran en Grupos de eventos DDL.

  Una vez que CREATE TRIGGER ha terminado de ejecutarse, event_group también actúa como una macro al agregar los tipos de eventos que comprende a la vista de catálogo sys.trigger_events.

• WITH APPEND
  Especifica que debe agregarse un desencadenador adicional de un tipo existente. 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 no se puede especificar si se ha especificado EXTERNAL NAME (es decir, si el desencadenador es de tipo CLR).

  Importante
  WITH APPEND se quitará en la siguiente versión de MicrosoftSQL Server. No use WITH APPEND en un nuevo trabajo de desarrollo y piense en modificar las aplicaciones que actualmente utilizan esta función.

• NOT FOR REPLICATION
  Indica que el desencadenador no debe ejecutarse cuando un agente de replicación modifica la tabla involucrada en el mismo. Para obtener más información, vea Controlar restricciones, identidades y desencadenadores con NOT FOR REPLICATION.

• sql_statement
  Son las condiciones y acciones del desencadenador. Las condiciones del desencadenador especifican los criterios adicionales que determinan si los intentos de los eventos DML, DDL o logon hacen que se lleven a cabo las acciones del desencadenador.

  Las acciones del desencadenador especificadas en las instrucciones Transact-SQL surten efecto cuando se intenta la operación.

  Los desencadenadores pueden incluir cualquier número y clase de instrucciones Transact-SQL, con excepciones. Para obtener más información, vea la sección Notas. Un desencadenador está diseñado para comprobar o cambiar los datos en base a una instrucción de modificación o definición de datos; no debe devolver datos al usuario. Las instrucciones Transact-SQL de un desencadenador incluyen a menudo lenguaje de control de flujo.

  Los desencadenadores DML usan las tablas lógicas (conceptuales) insertadas y eliminadas. 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. Las tablas insertadas y eliminadas contienen los valores antiguos o nuevos de las filas que pueden modificarse mediante la acción del usuario. Por ejemplo, para recuperar todos los valores de la tabla deleted, utilice:

  SELECT *
  FROM deleted
  

  Para obtener más información, vea Usar las tablas inserted y deleted.

  Los desencadenadores DDL y logon capturan información acerca del evento desencadenador mediante el uso de la función EVENTDATA (Transact-SQL). Para obtener más información, vea Usar la función EVENTDATA.

  SQL Server permite actualizar las columnas text, ntext o image mediante el uso del desencadenador INSTEAD OF en tablas o vistas.

  Importante

  Los tipos de datos ntext, text e image se quitarán en una versión futura de MicrosoftSQL Server. Evite su uso en nuevos trabajos de desarrollo y piense en modificar las aplicaciones que los usan actualmente. Utilice nvarchar(max), varchar(max) y varbinary(max) en su lugar. Tanto los desencadenadores AFTER como INSTEAD OF admiten los datos varchar(MAX), nvarchar(MAX) y varbinary(MAX) en las tablas insertadas y eliminadas.

• <method_specifier>
  En el caso de un desencadenador CLR, especifica el método de enlace de un ensamblado con el desencadenador. El método no puede utilizar argumentos y debe devolver void. class_name debe ser un identificador de SQL Server válido y debe existir como una clase en el ensamblado con visibilidad de ensamblado. Si la clase tiene un nombre completo del espacio de nombres que usa '.' para separar los elementos del espacio de nombres, el nombre de clase debe delimitarse mediante [ ] o " ". La clase no puede ser anidada.

  [!NOTA]

  De manera predeterminada, la capacidad de SQL Server de ejecutar código CLR está desactivada. Se puede crear, modificar y quitar objetos de bases de datos que hagan referencia a módulos de código administrados, pero estas referencias no se ejecutarán en una instancia de SQL Server a menos que la opción clr enabled esté habilitada mediante el uso de sp_configure.

Notas

Desencadenadores DML

Desencadenadores DDL

Los desencadenadores DDL, al igual que los estándar, ejecutan procedimientos almacenados como respuesta a un evento. Pero a diferencia de los desencadenadores estándar, no se ejecutan como respuesta a instrucciones UPDATE, INSERT o DELETE en una tabla o vista. En cambio, se ejecutan principalmente como respuesta a instrucciones de lenguaje de definición de datos (o DDL). Entre ellas se incluyen instrucciones CREATE, ALTER, DROP, GRANT, DENY, REVOKE y UPDATE STATISTICS. Algunos procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL también pueden activar desencadenadores DDL.

Importante
Pruebe los desencadenadores DDL para determinar sus respuestas a la ejecución de los procedimientos almacenados del sistema. Por ejemplo, la instrucción CREATE TYPE y los procedimientos almacenados sp_addtype y sp_rename activarán un desencadenador DDL creado en un evento CREATE_TYPE.

Para obtener más información acerca de los desencadenadores DDL, vea Desencadenadores DDL.

Los desencadenadores DDL no se activan como respuesta a eventos que afectan a procedimientos almacenados y tablas temporales, ya sean locales o globales.

A diferencia de los desencadenadores DML, los desencadenadores DDL no tienen como ámbito los esquemas. Por tanto, las funciones como OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY y OBJECTPROPERTYEX no se pueden usar para efectuar consultas en metadatos sobre desencadenadores DDL. Utilice en su lugar las vistas de catálogo. Para obtener más información, vea Obtener información acerca de los desencadenadores DDL.

[!NOTA]

Los desencadenadores DDL con ámbito en el servidor aparecen en el Explorador de objetos de SQL Server Management Studio, en la carpeta Triggers. Dicha carpeta se encuentra en la carpeta Server Objects. Los desencadenadores DDL con ámbito en la base de datos aparecen en la carpeta Database Triggers. Esta carpeta se encuentra en la carpeta Programación de la base de datos correspondiente.

Desencadenadores logon

Consideraciones generales sobre los desencadenadores

Permisos

Para crear un desencadenador DML, es necesario contar con permiso ALTER sobre la tabla o vista en la que se crea el desencadenador.

Para crear un desencadenador DDL con ámbito de servidor (ON ALL SERVER) o un desencadenador logon se requiere el permiso CONTROL SERVER en el servidor. Para crear un desencadenador DDL con ámbito en la base de datos (ON DATABASE) es necesario un permiso ALTER ANY DATABASE DDL TRIGGER en la base de datos actual.

Ejemplos

A. Utilizar un desencadenador DML con un mensaje de aviso

USE AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
   DROP TRIGGER Sales.reminder1;
GO
-- This trigger raises a message whenever a row is inserted or modified in Sales.Customer.

CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE 
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO

B. Utilizar un desencadenador DML con un mensaje de correo electrónico de aviso

USE AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
-- This trigger sends an e-mail message to a specified account whenever
-- a row is inserted, updated or deleted from the Sales.Customer table.

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE 
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

C. Utilizar un desencadenador DML AFTER para exigir una regla de empresa entre las tablas PurchaseOrderHeader y Vendor

IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).

CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
IF EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.VendorID = p.VendorID
           WHERE v.CreditRating = 5
          )
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;

-- This statement attempts to insert a row into the PurchaseOrderHeader table
-- for a vendor that has a below average credit rating.
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES(
2
,3
,261    
,1652   
,4  
,GETDATE()
,GETDATE()
,44594.55   
,3567.564   
,1114.8638);
GO

D. Utilizar la resolución diferida de nombres

USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig1','TR') IS NOT NULL
   DROP TRIGGER HumanResources.trig1;
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER HumanResources.trig1
on HumanResources.Employee
AFTER INSERT, UPDATE, DELETE
AS 
   SELECT e.EmployeeID, e.BirthDate, x.info 
   FROM HumanResources.Employee AS e INNER JOIN does_not_exist AS x 
      ON e.EmployeeID = x.xID
GO

-- This statement displays the text of the trigger.
SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m 
   ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig1'
AND t.parent_class = 1
GO

-- Creating a trigger on an existing table, but with a nonexistent 
-- column.
USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig2','TR') IS NOT NULL
   DROP TRIGGER HumanResources.trig2
GO

CREATE TRIGGER HumanResources.trig2 
ON HumanResources.Employee
AFTER INSERT, UPDATE
AS 
   DECLARE @fax varchar(12)
   SELECT @fax = 'AltPhone'
   FROM HumanResources.Employee
GO

-- This statement displays the text of the trigger.

SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m 
   ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig2'
AND t.parent_class = 1
GO

E. Utilizar un desencadenador DDL con ámbito en la base de datos

USE AdventureWorks;
GO

IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety
ON DATABASE;
GO

CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_SYNONYM
AS 
   RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
   ROLLBACK
GO

DROP TRIGGER safety
ON DATABASE;
GO

E. Utilizar un desencadenador DDL con ámbito en el servidor

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

CREATE TRIGGER ddl_trig_database 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
    PRINT 'Database Created.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO

DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

G. Usar un desencadenador logon

USE master;
GO

CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;

H. Ver los eventos que hacen que se active un desencadenador

SELECT TE.*
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 0
AND T.name = 'safety'
GO

Historial de cambios

Vea también

Referencia

Conceptos