Triggers Logon

Triggers Logon

Son los tipos de Triggers que se disparan en respuesta a un evento de INICIO DE SESIÓN.  Este evento se pproduce cuando se establece una sesión de usuario con una instancia de SQL Server.

El inicio de sesión dispara el Trigger después de la fase de autenticación de inicio de sesión, pero antes de que la sesión del usuario esté realmente establecida. (Ver Triggers). Por lo tanto, todos los mensajes que se originan dentro del desencadenador y que normalmente llegarían al usuario, como los mensajes de error y los mensajes de la instrucción PRINT  se desvían al registro de errores de SQL Server. 



Los Triggers Logon no se activan si la autenticación falla.

Puede utilizar los Triggers de inicio de sesión para auditar y controlar las sesiones del servidor, como el seguimiento de la actividad de inicio de sesión, la restricción de inicios  de sesión en SQL Server o la limitación del número de sesiones para un inicio de sesión específico.

Instrucción Create Trigger

Permite crear Triggers de tipo Logon

CREATE [ OR ALTER ] TRIGGER NombreTrigger
ON ALL SERVER
[ WITH <OpcionesTriggersLogon> [ ,…n ] ]
{ FOR| AFTER } LOGON
AS { InstruccionesSQL [ ; ] [ ,…n ] | EXTERNAL NAME < MètodoEspecífico > }

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

Orden de los Triggers Logon

Se pueden definir múltiples Triggers LOGON, cualquiera de estos Triggers puede designarse como el primer o último Trigger que se activará en un evento mediante el procedimiento almacenado del sistema sp_settriggerorder.
SQL Server no garantiza el orden de ejecución de los desencadenadores restantes.

Procedimiento sp_settriggerorder

Define el primero y el último de los Triggers Logon

sp_settriggerorder [ @triggername = ] ‘[ Esquema. ] NombreTrigger’
, [ @order = ] ‘value’
, [ @stmttype = ] ‘Tipo_Instrucción_SQL’
[ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ]

Valores posibles del orden
First Trigger se dispara primero
Last Trigger se diapara último.
None Trigger no se define su orden

Ejemplo

En el siguiente ejemplo se define como primero el Trigger trShippersInserta que realiza un Insert.

USE Northwind
go
sp_settriggerorder @triggername= ‘dbo.trShippersInserta’, @order=’First’, @stmttype = ‘INSERT’
GO

Ejercicio

Crear un Trigger Logon que deniegue intentos de inicio de sesión de SQL Server iniciados por login TrainerSQL si ya hay tres sesiones de usuario creadas por ese inicio de sesión.

USE master
go
CREATE LOGIN TrainerSQL WITH PASSWORD = ‘cl@v3F1n@1’
go

— Asignar el permiso para ver el estado del servidor

Grant VIEW SERVER STATE TO TrainerSQL
go

— El Trigger Logon para evitar más de tres sesiones.

Create Trigger trServerLimitarConexiones
ON ALL SERVER
WITH EXECUTE AS ‘TrainerSQL’
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= ‘TrainerSQL’ AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = ‘TrainerSQL’) > 3
ROLLBACK
END
go

Para probar el Trigger,  conectarse a la misma instancia más de tres veces con el mismo inicio de sesión, en la figura se muestra la conexión

Para visualizar las sesiones de un inicio de sesión

select session_id As ‘Código de Sesión’,
login_name As ‘Inicio de sesión’,
Format(login_time,’dd/MM/yyy’) As ‘Fecha’,
program_name As ‘Programa’
from sys.dm_exec_sessions where login_name = ‘TrainerSQL’
go

El resultado se muestra como sigue:

Después de la tercera conexión, al intentar conectarse se dispara el Trigger creado.



Consideraciones importantes

  • Cualquier modificación de datos realizada hasta el punto de ROLLBACK TRANSACTION se revierte. Estas modificaciones incluyen las realizadas por el desencadenador actual y las realizadas por desencadenantes anteriores que se ejecutaron en el mismo evento.
  • Cualquier desencadenante restante para el evento específico no se ejecuta.
  • El Trigger actual continúa ejecutando las sentencias restantes que aparecen después de la instrucción ROLLBACK.
  • Si alguna de estas declaraciones modifica los datos, las modificaciones no se revierten.

No se establece una sesión de usuario si se produce una de las siguientes condiciones durante la ejecución
de un desencadenante en un evento de INICIO de sesión:

  • La transacción implícita original se retrotrae o falla.
  • Se genera un error que tiene una gravedad superior a 20 dentro del cuerpo del Trigger. (Ver Errores)