Triggers DDL


Triggers DDL

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

Puede interesar
Triggers, definición y creación
Triggers DML encriptados
Triggers Logon
Triggers, activar y desactivar.



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:

  • Eventos que tienen alcance de base de datos
  • Eventos que tienen alcance de servidor

Eventos que tienen alcance de base de datos

CREATE_APPLICATION_ROLE
CREATE_ASSEMBLY ALTER_ASSEMBLY DROP_ASSEMBLY
CREATE_ASYMMETRIC_KEY ALTER_ASYMMETRIC_KEY DROP_ASYMMETRIC_KEY
ALTER_AUTHORIZATION ALTER_AUTHORIZATION_DATABASE
CREATE_BROKER_PRIORITY CREATE_BROKER_PRIORITY CREATE_BROKER_PRIORITY
CREATE_CERTIFICATE ALTER_CERTIFICATE DROP_CERTIFICATE
CREATE_CONTRACT DROP_CONTRACT
CREATE_CREDENTIAL ALTER_CREDENTIAL DROP_CREDENTIAL
GRANT_DATABASE DENY_DATABASE REVOKE_DATABASE
CREATE_DATABASE_AUDIT_SPEFICIATION ALTER_DATABASE_AUDIT_SPEFICIATION DENY_DATABASE_AUDIT_SPEFICIATION
CREATE_DATABASE_ENCRYPTION_KEY ALTER_DATABASE_ENCRYPTION_KEY DROP_DATABASE_ENCRYPTION_KEY
CREATE_DEFAULT DROP_DEFAULT
BIND_DEFAULT (También sp_bindefault.) UNBIND_DEFAULT (También sp_unbindefault.)
CREATE_EVENT_NOTIFICATION DROP_EVENT_NOTIFICATION
CREATE_EXTENDED_PROPERTY (También sp_addextendedproperty.) ALTER_EXTENDED_PROPERTY (También sp_updateextendedproperty.)
DROP_EXTENDED_PROPERTY (También sp_dropextendedproperty.)
CREATE_FULLTEXT_CATALOG (También the CREATE FULLTEXT CATALOG y sp_fulltextcatalog)
ALTER_FULLTEXT_CATALOG (También ALTER FULLTEXT CATALOG , cuando sp_fulltextcatalog, start_full, Stop,  o Rebuild es especificado, y sp_fulltext_database )
DROP_FULLTEXT_CATALOG (También DROP FULLTEXT CATALOG y sp_fulltextcatalog)
CREATE_FULLTEXT_INDEX (También CREATE FULLTEXT INDEX y sp_fulltexttable)
ALTER_FULLTEXT_INDEX (También ALTER FULLTEXT INDEX , sp_fulltextcatalog, sp_fulltext_column,
y sp_fulltext_table)
DROP_FULLTEXT_INDEX (También DROP FULLTEXT INDEX y sp_fulltexttable)
CREATE_FULLTEXT_STOPLIST ALTER_FULLTEXT_STOPLIST DROP_FULLTEXT_STOPLIST
CREATE_FUNCTION ALTER_FUNCTION DROP_FUNCTION
CREATE_INDEX ALTER_INDEX (También the ALTER INDEX y sp_indexoption.) DROP_INDEX
CREATE_MASTER_KEY ALTER_MASTER_KEY DROP_MASTER_KEY
CREATE_MESSAGE_TYPE ALTER_MESSAGE_TYPE DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION ALTER_PARTITION_FUNCTION DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME ALTER_PARTITION_SCHEME DROP_PARTITION_SCHEME
CREATE_PLAN_GUIDE (También sp_create_plan_guide.)
ALTER_PLAN_GUIDE (También sp_control_plan_guide)
DROP_PLAN_GUIDE (También sp_control_plan_guide )
CREATE_PROCEDURE ALTER_PROCEDURE (También ALTER PROCEDURE y sp_procoption.) DROP_PROCEDURE
CREATE_QUEUE ALTER_QUEUE DROP_QUEUE
CREATE_REMOTE_SERVICE_BINDING ALTER_REMOTE_SERVICE_BINDING DROP_REMOTE_SERVICE_BINDING
CREATE_SPATIAL_INDEX
RENAME (También sp_rename)
CREATE_ROLE (También CREATE ROLE, sp_addrole, y sp_addgroup.)
ALTER_ROLE DROP_ROLE (También DROP ROLE, sp_droprole, y sp_dropgroup.)
ADD_ROLE_MEMBER DROP_ROLE_MEMBER
CREATE_ROUTE ALTER_ROUTE DROP_ROUTE
CREATE_RULE DROP_RULE
BIND_RULE (También sp_bindrule.) UNBIND_RULE (También sp_unbindrule.)
CREATE_SCHEMA (También CREATE SCHEMA, sp_addrole, sp_adduser, sp_addgroup, y sp_grantdbaccess.)
ALTER_SCHEMA (También ALTER SCHEMA y sp_changeobjectowner.) DROP_SCHEMA
CREATE_SEARCH_PROPERTY_LIST ALTER_SEARCH_PROPERTY_LIST DROP_SEARCH_PROPERTY_LIST
CREATE_SEQUENCE_EVENTS CREATE_SEQUENCE_EVENTS CREATE_SEQUENCE_EVENTS
CREATE_SERVER_ROLE ALTER_SERVER_ROLE DROP_SERVER_ROLE
CREATE_SERVICE ALTER_SERVICE DROP_SERVICE
ALTER_SERVICE_MASTER_KEY BACKUP_SERVICE_MASTER_KEY RESTORE_SERVICE_MASTER_KEY
ADD_SIGNATURE DROP_SIGNATURE
ADD_SIGNATURE_SCHEMA_OBJECT DROP_SIGNATURE_SCHEMA_OBJECT
CREATE_SPATIAL_INDEX ALTER_INDEX DROP_INDEX
CREATE_STATISTICS DROP_STATISTICS UPDATE_STATISTICS
CREATE_SYMMETRIC_KEY ALTER_SYMMETRIC_KEY DROP_SYMMETRIC_KEY
CREATE_SYNONYM DROP_SYNONYM
CREATE_TABLE ALTER_TABLE (También ALTER TABLE y sp_tableoption.) DROP_TABLE
CREATE_TRIGGER ALTER_TRIGGER (También ALTER TRIGGER y sp_settriggerorder.) DROP_TRIGGER
CREATE_TYPE (También CREATE TYPE y sp_addtype.) DROP_TYPE (También DROP TYPE statement y sp_droptype.)
CREATE_USER (También CREATE USER, sp_adduser, y sp_grantdbaccess.)
ALTER_USER (También ALTER USER y sp_change_users_login.)
DROP_USER (También DROP USER statement, sp_dropuser, y sp_revokedbaccess.)
CREATE_VIEW ALTER_VIEW DROP_VIEW
CREATE_XML_INDEX ALTER_INDEX DROP_INDEX
CREATE_XML_SCHEMA_COLLECTION ALTER_XML_SCHEMA_COLLECTION DROP_XML_SCHEMA_COLLECTION

Eventos DDL que tienen alcance en el servidor

ALTER_AUTHORIZATION_SERVER ALTER_SERVER_CONFIGURATION ALTER_INSTANCE
CREATE_AVAILABILITY_GROUP ALTER_AVAILABILITY_GROUP DROP_AVAILABILITY_GROUP
CREATE_CREDENTIAL ALTER_CREDENTIAL DROP_CREDENTIAL
CREATE_CRYPTOGRAPHIC_PROVIDER ALTER_CRYPTOGRAPHIC_PROVIDER DROP_CRYPTOGRAPHIC_PROVIDER
CREATE_DATABASE ALTER_DATABASE DROP_DATABASE
CREATE_ENDPOINT ALTER_ENDPOINT DROP_ENDPOINT
CREATE_EVENT_SESSION ALTER_EVENT_SESSION DROP_EVENT_SESSION
CREATE_EXTENDED_PROCEDURE (También sp_addextendedproc.) DROP_EXTENDED_PROCEDURE (También sp_dropextendedproc.)
CREATE_LINKED_SERVER (También sp_addlinkedserver.) ALTER_LINKED_SERVER (También sp_serveroption.)
DROP_LINKED_SERVER (También sp_dropserver)
CREATE_LINKED_SERVER_LOGIN (También sp_addlinkedsrvlogin.) DROP_LINKED_SERVER_LOGIN (También sp_droplinkedsrvlogin.)
CREATE_LOGIN (También CREATE LOGIN, sp_addlogin, sp_grantlogin, xp_grantlogin, y sp_denylogin
ALTER_LOGIN (También ALTER LOGIN, sp_defaultdb, sp_defaultlanguage, sp_password, y sp_change_users_login)
DROP_LOGIN (También DROP LOGIN, sp_droplogin, sp_revokelogin, y xp_revokelogin.)
CREATE_MESSAGE (También sp_addmessage.) ALTER_MESSAGE (También sp_altermessage.) DROP_MESSAGE (También sp_dropmessage.)
CREATE_REMOTE_SERVER (También sp_addserver.) ALTER_REMOTE_SERVER (También sp_setnetname.) DROP_REMOTE_SERVER (También sp_dropserver)
CREATE_RESOURCE_POOL ALTER_RESOURCE_POOL DROP_RESOURCE_POOL
GRANT_SERVER DENY_SERVER REVOKE_SERVER
ADD_SERVER_ROLE_MEMBER DROP_SERVER_ROLE_MEMBER
CREATE_SERVER_AUDIT ALTER_SERVER_AUDIT DROP_SERVER_AUDIT
CREATE_SERVER_AUDIT_SPECIFICATION ALTER_SERVER_AUDIT_SPECIFICATION DROP_SERVER_AUDIT_SPECIFICATION
CREATE_WORKLOAD_GROUP ALTER_WORKLOAD_GROUP DROP_WORKLOAD_GROUP

Ejemplos
Usando la base de datos Northwind
use Northwind
go

1. 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()

2. 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 que se muestra en la figura siguiente.

3. 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

La imagen siguiente muestra el resultado