Como usar Triggers DDL en SQL Server

Triggers DDL en SQL Server

Los Triggers DDL son aquellos que se disparan cuando se realizan eventos DDL, que son las siglas de Data Definition Languages, estos comando son Create, Alter, Drop, GRANT, DENY, REVOKE o UPDATE STATISTICS

Para más información sobre Triggers ver:
Triggers, definición y creación.
Triggers Logon.
Triggers, activar y desactivar.
Triggers, historial de registros eliminados.
Triggers, historial de cambios en una tabla

Eventos de un Trigger DDL

Los eventos que hacen que se dispare un Trigger DDL clasificados por el alcance de los mismos son de dos tipos:
1. Eventos que tienen alcance de base de datos
2. Eventos que tienen alcance de servidor

Eventos que tienen alcance de base de datos

CREATE_APPLICATION_ROLEALTER_APPLICATION_ROLEDROP_APPLICATION_ROLE
CREATE_ASSEMBLYALTER_ASSEMBLYDROP_ASSEMBLY
CREATE_ASYMMETRIC_KEYALTER_ASYMMETRIC_KEYDROP_ASYMMETRIC_KEY
ALTER_AUTHORIZATIONALTER_AUTHORIZATION_DATABASE
CREATE_BROKER_PRIORITYCREATE_BROKER_PRIORITYCREATE_BROKER_PRIORITY
CREATE_CERTIFICATEALTER_CERTIFICATEDROP_CERTIFICATE
CREATE_CONTRACTDROP_CONTRACT
CREATE_CREDENTIALALTER_CREDENTIALDROP_CREDENTIAL
GRANT_DATABASEDENY_DATABASEREVOKE_DATABASE
CREATE_DATABASE_AUDIT_SPEFICIATIONALTER_DATABASE_AUDIT_SPEFICIATIONDENY_DATABASE_AUDIT_SPEFICIATION
CREATE_DATABASE_ENCRYPTION_KEYALTER_DATABASE_ENCRYPTION_KEYDROP_DATABASE_ENCRYPTION_KEY
CREATE_DEFAULTDROP_DEFAULT
BIND_DEFAULTUNBIND_DEFAULT
CREATE_EVENT_NOTIFICATIONDROP_EVENT_NOTIFICATION
CREATE_EXTENDED_PROPERTYALTER_EXTENDED_PROPERTYDROP_EXTENDED_PROPERTY
CREATE_FULLTEXT_CATALOGALTER_FULLTEXT_CATALOGDROP_FULLTEXT_CATALOG
CREATE_FULLTEXT_INDEXALTER_FULLTEXT_INDEXDROP_FULLTEXT_INDEX
CREATE_FULLTEXT_STOPLISTALTER_FULLTEXT_STOPLISTDROP_FULLTEXT_STOPLIST
CREATE_FUNCTIONALTER_FUNCTIONDROP_FUNCTION
CREATE_INDEXALTER_INDEXDROP_INDEX
CREATE_MASTER_KEYALTER_MASTER_KEYDROP_MASTER_KEY
CREATE_MESSAGE_TYPEALTER_MESSAGE_TYPEDROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTIONALTER_PARTITION_FUNCTIONDROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEMEALTER_PARTITION_SCHEMEDROP_PARTITION_SCHEME
CREATE_PLAN_GUIDEALTER_PLAN_GUIDEDROP_PLAN_GUIDE
CREATE_PROCEDUREALTER_PROCEDUREDROP_PROCEDURE
CREATE_QUEUEALTER_QUEUEDROP_QUEUE
CREATE_REMOTE_SERVICE_BINDINGALTER_REMOTE_SERVICE_BINDINGDROP_REMOTE_SERVICE_BINDING
CREATE_SPATIAL_INDEX
RENAME
CREATE_ROLEALTER_ROLEDROP_ROLE
ADD_ROLE_MEMBERDROP_ROLE_MEMBER
CREATE_ROUTEALTER_ROUTEDROP_ROUTE
CREATE_RULEDROP_RULE
BIND_RULEUNBIND_RULE
CREATE_SCHEMAALTER_SCHEMADROP_SCHEMA
CREATE_SEARCH_PROPERTY_LISTALTER_SEARCH_PROPERTY_LISTDROP_SEARCH_PROPERTY_LIST
CREATE_SEQUENCE_EVENTSCREATE_SEQUENCE_EVENTSCREATE_SEQUENCE_EVENTS
CREATE_SERVER_ROLEALTER_SERVER_ROLEDROP_SERVER_ROLE
CREATE_SERVICEALTER_SERVICEDROP_SERVICE
ALTER_SERVICE_MASTER_KEYBACKUP_SERVICE_MASTER_KEYRESTORE_SERVICE_MASTER_KEY
ADD_SIGNATUREDROP_SIGNATURE
ADD_SIGNATURE_SCHEMA_OBJECTDROP_SIGNATURE_SCHEMA_OBJECT
CREATE_SPATIAL_INDEXALTER_INDEXDROP_INDEX
CREATE_STATISTICSDROP_STATISTICSUPDATE_STATISTICS
CREATE_SYMMETRIC_KEYALTER_SYMMETRIC_KEYDROP_SYMMETRIC_KEY
CREATE_SYNONYMDROP_SYNONYM
CREATE_TABLEALTER_TABLEDROP_TABLE
CREATE_TRIGGERALTER_TRIGGERDROP_TRIGGER
CREATE_TYPEDROP_TYPE
CREATE_USERALTER_USERDROP_USER
CREATE_VIEWALTER_VIEWDROP_VIEW
CREATE_XML_INDEXALTER_INDEXDROP_INDEX
CREATE_XML_SCHEMA_COLLECTIONALTER_XML_SCHEMA_COLLECTIONDROP_XML_SCHEMA_COLLECTION
Eventos DDL a nivel de base de datos.
Fuente: Documentación Microsoft

Eventos DDL que tienen alcance en el servidor

Se pueden crear en respuesta de cualquiera de los siguientes eventos, los que ocurren en la instancia de SQL Server.

ALTER_AUTHORIZATION_SERVERALTER_SERVER_CONFIGURATIONALTER_INSTANCE
CREATE_AVAILABILITY_GROUPALTER_AVAILABILITY_GROUPDROP_AVAILABILITY_GROUP
CREATE_CREDENTIALALTER_CREDENTIALDROP_CREDENTIAL
CREATE_CRYPTOGRAPHIC_PROVIDERALTER_CRYPTOGRAPHIC_PROVIDERDROP_CRYPTOGRAPHIC_PROVIDER
CREATE_DATABASEALTER_DATABASEDROP_DATABASE
CREATE_ENDPOINTALTER_ENDPOINTDROP_ENDPOINT
CREATE_EVENT_SESSIONALTER_EVENT_SESSIONDROP_EVENT_SESSION
CREATE_EXTENDED_PROCEDUREDROP_EXTENDED_PROCEDURE
CREATE_LINKED_SERVERALTER_LINKED_SERVERDROP_LINKED_SERVER
CREATE_LINKED_SERVER_LOGINDROP_LINKED_SERVER_LOGIN
CREATE_LOGINALTER_LOGINDROP_LOGIN
CREATE_MESSAGEALTER_MESSAGEDROP_MESSAGE
CREATE_REMOTE_SERVERALTER_REMOTE_SERVERDROP_REMOTE_SERVER
CREATE_RESOURCE_POOLALTER_RESOURCE_POOLDROP_RESOURCE_POOL
GRANT_SERVERDENY_SERVERREVOKE_SERVER
ADD_SERVER_ROLE_MEMBERDROP_SERVER_ROLE_MEMBER
CREATE_SERVER_AUDITALTER_SERVER_AUDITDROP_SERVER_AUDIT
CREATE_SERVER_AUDIT_SPECIFICATIONALTER_SERVER_AUDIT_SPECIFICATIONDROP_SERVER_AUDIT_SPECIFICATION
CREATE_WORKLOAD_GROUPALTER_WORKLOAD_GROUPDROP_WORKLOAD_GROUP
Eventos DDL a nivel de servidor.
Fuente: Documentación Microsoft

Ejemplos

Usando la base de datos Northwind
use Northwind
go

Crear un Trigger para evitar que se creen, modifiquen o eliminen tablas
Create Trigger trNoCrearModificarBorrarTablas
ON DataBase FOR Create_Table, DROP_TABLE, ALTER_TABLE
AS
BEGIN
RAISERROR (‘Transacción anulada, no se permite crear, editar o eliminar tablas’ , 16, 1)
Rollback transaction
END
go

Al intentar crear una tabla
Create table Prueba
( Codigo nchar(4), Descripcion nvarchar(100) )
go
Resultado
Mens 50000, Nivel 16, Estado 1, Procedimiento trNoCrearModificarBorrarTablas, Línea 131
Transacción anulada, no se permite crear, editar o eliminar tablas
Mens. 3609, Nivel 16, Estado 2, Línea 127
La transacción terminó en el desencadenador. Se anuló el lote.

Para poder crear tablas, se debe eliminar el Trigger o solamente desactivar (Ver Triggers Activar – Desactivar)
disable trigger trNoCrearModificarBorrarTablas on Database
go
Probar ahora si se puede crear una tabla
Create table Prueba
( Codigo nchar(4), Descripcion nvarchar(100) )
go
Resultado: Comandos completados correctamente.
Para activar nuevamente el Trigger se debe escribir la siguiente instrucción:
Enable trigger trNoCrearModificarBorrarTablas on Database
go

La función EventData()

Esta función devuelve información sobre eventos del servidor o de la base de datos.
Un Trigger Logon o DDL también admite el uso interno de EVENTDATA.
Sintaxis:
EventData()

Crear un Trigger que se dispare cuando se crea una vista y capture el evento creado.
Create trigger trCapturarCrearVista on Database
For Create_View
As
Begin
Select EVENTDATA()
End
go
Crear una vista para que el trigger creado se dispare
create view vistaCategorias
As
select C.CategoryID As ‘Código’, C.CategoryName As ‘Nombre’
from Categories As C
go


Puede notar que el resultado se presenta en un esquema XML.

Crear un trigger que se dispare al crear, modificar o eliminar un procedimiento almacenado, almacenar la instrucción ejecutada en una tabla SPHistorial

Primero crear la tabla, si se creo el trigger del ejercicio anterior debemos desactivarlo.
disable trigger trNoCrearModificarBorrarTablas on Database
go
Ahora si se podrá crear la tabla
Create table HistorialCambiosStoreProcedures
(TipoEvento nvarchar(200), Fecha DateTime,
Servidor nvarchar(100), InicioSesion nvarchar(100),
Equipo nvarchar(100), ComandoTSQL nvarchar(400)
)
go
Crear el Trigger para crear, editar o eliminar un Store Procedure
Create trigger trHistoriaSP on Database
for Create_Procedure, Alter_Procedure, Drop_Procedure
As
Begin
insert into HistorialCambiosStoreProcedures
(TipoEvento, Fecha, Servidor, InicioSesion, Equipo, ComandoTSQL)
Select EVENTDATA().value (‘(/EVENT_INSTANCE/EventType)[1]’,’nvarchar(200)’),
EVENTDATA().value (‘(/EVENT_INSTANCE/PostTime)[1]’,’Datetime’),
EVENTDATA().value (‘(/EVENT_INSTANCE/ServerName)[1]’,’nvarchar(100)’),
EVENTDATA().value (‘(/EVENT_INSTANCE/LoginName)[1]’,’nvarchar(100)’),
HOST_NAME(), EVENTDATA().value (‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(400)’)
End
go
Para que el Trigger se dispare, crear un procedimiento almacenado para listar los clientes
Create procedure spClientesListado
As
Select C.CustomerID As ‘Código’, C.CompanyName As ‘Cliente’
from Customers As C
order by Cliente
go
Modificar el procedimiento para incluir un campo adicional
Alter procedure spClientesListado
As
Select C.CustomerID As ‘Código’, C.CompanyName As ‘Cliente’,
C.Address As ‘Dirección’
from Customers As C
order by Cliente
go
Borrar el SP
Drop procedure spClientesListado
go

Para visualizar el contenido del historial
select * from HistorialCambiosStoreProcedures
go