In alcune applicazioni è spesso necessario creare database locali che si sincronizzano in qualche modo con il database presente su un server. Esempio tipico di applicazioni di questo tipo è Outlook, dove è necessario avere una copia dei dati locali in modo da poter lavorare in un ambiente parzialmente connesso. In scenari di questo tipo, specialmente nella fase di sviluppo e test dell'applicazione, ci può essere l'esigenza di modificare il database, e quindi di replicare tali modifiche a tutti i client insieme all'aggiornamento delle procedure di accesso ai dati. Tenere traccia delle modifiche effettuate e creare procedure di allineamento delle strutture, senza creare problemi ai dati presenti nei db remoti, è a volte una operazione lunga e che richiede parecchi test. I Trigger DDL ci vengono sicuramente in aiuto in scenari di questo tipo.
Un Trigger DDL è un particolare tipo di trigger di SQL Server 2005 in grado di intercettare tutte le operazioni DDL (Data Definition Language) e che quindi modificano la struttura della base dati.
Un buon esempio di un trigger di questo tipo è presente all'interno del database AdventureWorks. In pratica ogni operazione di Create, Alter, Drop, etc. di qualsiasi struttura del DB può essere intercettata e memorizzata in un'apposita tabella.
Durante il deployment delle nostre applicazioni, potremo integrare il setup con una routine che riesegue sul DB client tutte le query DDL effettuate sul server in modo da rendere le strutture dei due DB assolutamente uguali.
Ecco come avviene la creazione di un trigger DDL
[code language="sql"]
CREATE TRIGGER myDDLTrigger ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;
END
[/code]
La clausola FOR DDL_DATABASE_LEVEL_EVENTS indica che è richiesta l'intercettazione di TUTTI gli eventi DDL che si verificano all'interno del database in cui il trigger si trova.
Possiamo sostituire questa clausola con l'elenco degli eventi che intendiamo intercettare. Per avere una idea del tipo di eventi che possono essere intercettati con questo tipo di trigger è sufficiente eseguire questa query all'interno di un Database di SQL Server 2005
[code language="sql"]
select type_desc from sys.trigger_events WHERE type >20
[/code]
Una volta intercettato l'evento all'interno del trigger otteniamo una struttura XML che descrive esattamente il tipo di operazione che è stata effettuata, con tanto di query eseguita per effettuarla.
Possiamo salvare il dato in formato xml oppure navigare nel documento con XQuery ed esaminarne il contenuto.
[code language="sql"]
CREATE TRIGGER myDDLTrigger ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
SET @data = EVENTDATA(); -- La variabile @data contiene la struttura xml con il dettaglio delle modifiche effettuate.
END
[/code]
A questo punto possiamo creare una tabellina in cui memorizzare i valori ottenuti.
[code language="sql"]
CREATE TABLE myDbLog
(
Id uniqueidentifier,
operationdate datetime,
operation xml
) ON [Primary]
GO
CREATE TRIGGER myDDLTrigger ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
SET @data = EVENTDATA(); -- La variabile @data contiene la struttura xml con il dettaglio delle modifiche effettuate.
INSERT INTO myDbLog
VALUES (newid(), getdate(), @data)
END
GO
[/code]
Ovviamente possiamo rendere più ricca la nostra tabella esponendo già come campi, i dati presenti all'interno della struttura xml.