Capitulo 5 – Lección 3: Programación de Microsoft SQL Server con Desencadenadores (Triggers)

Los factores desencadenantes son un tipo especial de procedimiento almacenado que se ejecutan automáticamente cuando una sentencia DML o DDL asociada con el disparador se ejecuta. Veremos como crear desencadenadores DML que se ejecutan al agregar, modificar o eliminar filas de una tabla. También a crear desencadenadores DDL que se ejecutan cuando se ejecutan comandos DDL o los usuarios se conectan a una instancia de SQL Server.

Desencadenadores DML (Más información AQUI)
A pesar de un disparador es un objeto programable que podemos crear, no se puede ejecutar un disparo directamente.
Los desencadenadores DML se crean en contra de una tabla o una vista y se definen para un evento específico: INSERT, UPDATE o DELETE. Cuando se ejecuta el evento para el cual se define un disparador, SQL Server se ejecuta automáticamente el código en el desencadenador, que también se conoce como "disparar" el trigger.
La sintaxis genérica para crear un desencadenador es



Cuando un desencadenador se define como AFTER, el desencadenador se activa después que la modificación ha pasado todas las restricciones. Si una modificación falla ante un obstáculo, como una restricción de comprobación, la restricción de clave principal o restricción de clave externa, el desencadenador no se ejecuta.  Desencadenadores AFTER se definen únicamente para las tablas, y varios desencadenadores AFTER se pueden definir para la misma acción. Si usted tiene múltiples desencadenantes creado para la misma acción, puede especificar los factores desencadenantes primero y el último a disparar mediante el procedimiento almacenados del sistema sp_settriggerorder.
Sin embargo, cualquier otros factores desencadenantes de la misma acción que son ejecutados en orden aleatorio.
Un disparador que define con la cláusula INSTEAD OF hace que el código de activación ejecutarse como un reemplazo para INSERT, UPDATE o DELETE. Se puede definir un solo desencadenador INSTEAD OF para una acción determinada. Aunque los disparadores INSTEAD OF pueden ser creados contra las tablas y vistas, los desencadenadores INSTEAD OF son casi siempre creados en contra de vistas.

Independientemente del número de filas que se ven afectados, se activa un desencadenador sólo una vez para una acción.

La opción NOT FOR REPLICATION controla el comportamiento del desencadenador cuando el motor de replicación está aplicando los cambios. Por defecto, las instrucciones INSERT, UPDATE, o DELETE se ejecuta en un suscriptor por el motor de replicación haciendo que el desencadenador correspondiente se active. Si usted no quiere que los desencadenadores en el suscriptor se activen cuando el motor de replicación está aplicando los cambios, se puede especificar la opción NOT FOR REPLICATION. Si un cambio se hace por cualquier otro procedimiento en el motor de replicación, el desencadenador sigue activo.

Cuando un desencadenador se ejecuta, dos tablas especiales denominadas inserted y deleted están disponibles.

Desencadenadores DDL (Mas Información AQUI)
Los desencadenadores DDL se puede ejecutar ya sea cuando una sentencia DDL se ejecuta o cuando el usuario inicia sesión en la instancia de SQL Server. 
La sintaxis general para la creación de un desencadenador DDL es la siguiente:



Los desencadenadores DDL pueden estar al alcance tanto a nivel de base de datos o instancia. Al ámbito de un desencadenador DDL a nivel de instancia, se utiliza la opción ON ALL SERVER. Al ámbito de un desencadenador DDL a nivel de base de datos, utilice la opción ON DATABASE.
El siguiente es un ejemplo de un desencadenador DDL:

CREATE TRIGGER tddl_tabledropalterprevent
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT ‘Está intentando eliminar o modificar las tablas de producción!'
ROLLBACK;

Casi todos los comandos DDL se ejecutan en el contexto de una transacción.Debido a que un desencadenador DDL también se ejecuta en el contexto de la transacción misma, cualquier sentencia DDL se ejecutan en el contexto de una transacción se puede revertir. ALTER DATABASE es uno de los comandos que no se ejecuta en el contexto de una transacción, ya que la orden afecta a los objetos fuera de SQL Server que no obedecen a la semántica transaccional. Por lo tanto, un comando ALTER DATABASE no se puede revertir.

El valor para el tipo de evento se deriva de la sentencia DDL que se está ejecutando. La siguiente tabla muestra algunos ejemplos:

Comando DDL	Tipo de evento
CREATE DATABASE	CREATE_DATABASE
DROP LOGIN	DROP_LOGIN
UPDATE STATISTICS	UPDATE_STATISTICS
DROP TRIGGER	DROP_TRIGGER
ALTER TABLE	ALTER_TABLE

Se puede activar un desencadenador DDL cuando se concede, revocar o deniega permisos en un servidor o a nivel base de datos. Para ejecutar una instrucción de permiso de un desencadenador DDL para  de nivel de base, utilice los tipos de evento GRANT_DATABASE, REVOKE_DATABASE o DENY_DATABASE . una instrucción de permiso de un desencadenador DDL para  de nivel de instancia, utilice el GRANT_SERVER, REVOKE_SERVER o DENY_SERVER.
Los tipos de eventos están dentro de una jerarquía de mando llamados grupos de eventos. Por ejemplo, los tipos de eventos CREATE_TABLE, ALTER_TABLE y DROP_TABLE se encuentran dentro del grupo de eventos DDL_TABLE_EVENTS. Tipos de eventos y grupos de eventos le permite crear desencadenadores DDL flexibles y compactos
.Mientras que los desencadenadores DML tienen acceso a las tablas insertadas y eliminadas, desencadenadores DDL tienen acceso a la función EVENTDATA.EVENTDATA devuelve el siguiente Extensible Markup Language (XML), que se puede consultar utilizando el método value() disponible a través de XQuery:

<EVENT_INSTANCE>
  <EventType>type</EventType>
  <PostTime>date-time</PostTime>
  <SPID>spid</SPID>
  <ServerName>name</ServerName>
  <LoginName>name</LoginName>
  <UserName>name</UserName>
  <DatabaseName>name</DatabaseName>
  <SchemaName>name</SchemaName>
  <ObjectName>name</ObjectName>
  <ObjectType>type</ObjectType>
  <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

El documento XML disponible varía según el tipo de evento que causó el desencadenador DDL. Los esquemas XML para cada evento DDL se documentan en http://schemas.microsoft.com/sqlserver. Por ejemplo, para un evento CREATE_TABLE, ALTER_TABLE o DROP_TABLE, puede utilizar la siguiente consulta para recuperar la base de datos, esquemas, objetos y ejecuta el comando:

SELECT EVENTDATA().value
  ('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)'),
  EVENTDATA().value
  ('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)'), 
  EVENTDATA().value
  ('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'),
  EVENTDATA().value
  ('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)')

Desencadenadores de inicio de sesión

Además de responder a los eventos DDL, puede crear un desencadenador que se active a un inicio de sesión a la instancia de SQL Server. Los desencadenadores logon se activan después de que la autenticación tiene éxito, pero antes de la sesión del usuario se establezca en realidad. Usted no puede devolver ningún mensaje a un usuario desde un desencadenador de inicio de sesión.
La sintaxis genérica para un disparo de inicio de sesión:



Disparadores de inicio de sesión se utilizan para restringir el acceso y realizar auditoría. Por ejemplo, se podría limitar el número de conexiones que un usuario puede realizar a la instancia. Si se ejecuta una instrucción ROLLBACK en un desencadenador de inicio de sesión, la conexión a la instancia termina.
Supongamos un ejemplo practico: Una de las tareas más difíciles que los administradores de base de datos de enfrentan, especialmente con aplicaciones basadas en Web, es lograr que todos los usuarios estén desconectados de una base de datos para poder realizar el mantenimiento. No importa cuán cuidadosamente se planee, es inevitable que se creen conexiones inesperadas. Tan pronto como se cierran todas las conexiones a la base de datos, otra conexión se crea, lo que en un círculo sin fin. Un método para bloquear el acceso es deshabilitar todas las conexiones, sin embargo, está cambiando el estado de la sesión y volver a habilitar el inicio de sesión puede provocar comprobaciones de directivas a propensas a fallar, creando así una interrupción inesperada. Un mejor método es crear un disparador de inicio de sesión que rechaza los intentos de inicio de sesión en la ventana de mantenimiento. Entonces, todo lo que tiene que hacer es deshabilitar el desencadenador de inicio de sesión tras el mantenimiento y regresar el acceso a la normalidad.

Resumen de la lección

• Los desencadenantes son procedimientos almacenados especializados  que se ejecutan automáticamente en respuesta a un evento DDL o DML.
• Puede crear tres tipos de disparo: DML, DDL, y desencadenadores de inicio de sesión.
• Un desencadenador DML se ejecuta cuando un INSERT, UPDATE, o DELETE es ejecutado.
• Un desencadenador DDL se ejecuta cuando una instrucción DDL que produce el disparo.
• Un disparador de inicio de sesión se ejecuta cuando hay un intento de inicio de sesión.
• Usted puede acceder a las tablas inserted y deleted dentro de un desencadenador DML.
• Puede acceder al documento XML que proporciona la función EVENTDATA dentro de un desencadenador DDL o logon