SQL Server 2005 incorpora dos novedades orientadas a mejorar las posibilidades de auditoría y control sobre la ejecución de sentencias DDL: Los Triggers DDL y los Eventos de Notificación.
Existen varias diferencias y similitudes entre estos dos mecanismos que desglosaremos a continuación:
- Síncrono / Asíncrono. Los Triggers DDL tienen un funcionamiento similar a los Triggers DML de toda la vida. Se ejecutan en la misma transacción que la operación que los dispara, y de forma síncrono. Esto nos permite deshacer la operación desde dentro del Trigger, complementarla, realizar comprobaciones adicionales, etc. Sin embargo, los Eventos de Notificación, se ejecutan de forma asíncrono, enviando los datos del evento en formato xml a un servicio de Service Broker.
- Operaciones que podemos controlar. Los Triggers DDL, como su propio nombre indica, nos permiten controlar las acciones desencadenadas por la ejecución de sentencias DDL. Podríamos decir que los triggers DDL son un subconjunto de los eventos de notificación, desde el punto de vista de las operaciones que podemos manejar.
- Código que podemos ejecutar. Desde un trigger DDL, podemos ejecutar cualquier sentencia T-SQL, o incluso desarrollar un trigger CLR. Sin embargo desde un Evento de Notificación, tan solo enviamos los datos a un servicio de Service Broker.
Pero seguro que lo entendéis mejor con algunos ejemplos. Veamos.
En este primer ejemplo, creamos un trigger DDL que se dispara cada vez que se crea una tabla. LA función eventdata devuelve, en formato XML, los datos del trigger.
CREATE TRIGGER DDL_TEST
ON DATABASE
FOR CREATE_TABLE
AS
PRINT CONVERT(varchar(max),eventdata()))
Cómo podéis ver en la sintaxis, podemos gestionar triggers DDL a nivel de Base de Datos, pero también podríamos hacerlo a nivel de servidor
En cuanto a los Eventos de Notificación, su manejo es un poco más complejo, puesto que debemos de crear primero la infraestructura de Service Broker necesaria. En el siguiente ejemplo creamos una cola y un servicio al que enviar los datos del evento.
CREATE QUEUE ColaNotificacion
GO
CREATE SERVICE ServicioNotificacion
ON QUEUE ColaNotificacion
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
CREATE EVENT NOTIFICATION log_ddl
ON SERVER
FOR Object_Created
TO SERVICE 'ServicioNotificacion', 'current_database'
Además, podemos utilizar las siguientes vistas del catálogo para obtener más información
SELECT * FROM sys.event_notifications
SELECT * FROM sys.server_event_notifications
SELECT * FROM sys.server_events
SELECT * FROM sys.events
Auditemos!!!!
2 comments
Como recupero un trigger server? se guardan en la Master?
Efectivamente Juan, en la master. Puedes ver detalles de como obtener información aquí https://msdn.microsoft.com/en-us/library/ms184304.aspx