Melhoramentos no T-SQL para SQL Server 2005

Este é o primeiro de uma série de matérias que vou escrever sobre as novidades no T-SQL da versão do SQL Server 2005.

por Eugênio Spolti

É com muita alegria que escrevo o meu primeiro artigo no site, este é o primeiro de uma série de matérias que vou escrever sobre as novidades no T-SQL da versão do SQL Server 2005 . Hoje iremos ver uma das várias solicitações dos DBAs que foram atendidas pelo pessoal de Redmond, as triggers em comandos DDL, ou seja, triggers para a criação, alteração, e exclusão de tabelas, views, procedures etc...

Um dos principais motivos desta solicitação , era a necessidade de podermos controlar a modificação no database por usuários mal intencionados, por scripts mal feitos ou para simplismente termos controle sobre os fontes.

Para exemplificar-mos esta nova funcionalidade iremos montar um mini sistema de versionamento de procedures. No script TriggersDDL.sql temos a criação das tabelas e procedures envolvidas no exemplo. Teremos somente uma tabela chamada Versoes e uma procedure chamada sp_Auditoria que não efetua nenhum processo, pois só existe para rodar-mos em cima dela as triggers.

Vamos dar uma olhada na criação da tabela Versoes e verificar o que cada coluna irá armazenar:

CREATE TABLE Versoes (Id INT PRIMARY KEY IDENTITY, Objeto char(254) ,
FonteDDL VARCHAR(MAX), Usuario char(254), Data char(30))

Abaixo temos a criação da procedure sobre a qual iremos auditar as alterações:

Create Procedure sp_Auditoria 
As 
      Print "Versão 1.0"

Agora que já temos os objetos para nossos exemplos vamos dar uma olhada na sintaxe para a criação das triggers

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH ENCRYPTION ]
{ FOR | AFTER } { event_type [ ,...n ] | DDL_DATABASE_LEVEL_EVENTS }
  [ WITH APPEND ]
  [ NOT FOR REPLICATION ]
{ AS
  { sql_statement [ ...n ] | EXTERNAL NAME < method specifier > }
}
< method_specifier > ::=
  assembly_name:class_name[::method_name]

Como podemos ver a sintaxe é muito parecida com as triggers para DML (Data Manipulation Language) , então vamos nos ater as diferenças entre elas.

As triggers para DDL tem o scopo agora em todo o servidor ( ON ALL SERVER) ou somente na base em que está sendo criada ( ON DATABASE ) . Outra diferença é para quais tipos de evento (event_type) a trigger irá disparar, podemos informar um ou mais eventos ( FOR AFTER DROP_TABLE,... ) ou utilizar a opção DDL_DATABASE_LEVEL_EVENTS para que ela responda a todos os eventos de DDL. Lembrando que nem todos os event_type estão disponíveis para o scopo ALL SERVER. Para o nosso exemplo vamos utilizar o DDL_PROCEDURE_EVENTS.

Outra possíbilidade é utilizar código em .Net , como todo o SQL Server 2005 é integrado com a CLR do .Net é possível também criar estas triggers em linguagens .Net ( VB.Net, C#.Net, etc...), a integração com o .Net iremos ver em um dos próximos artigos.

Os comandos que as triggers DDL poderão executar são iguais as triggers DML , claro que com algumas restrições que podem ser achadas no BOL.

A grande diferença neste sentido é que nas triggers DML temos as tabelas inserted e deleted, que dentro das triggers DDL não existem.

É claro que a pergunta que todos fizeram foi "Então como vamos pegar as informações dos dados antigos e novos?". A resposta a esta pergunta é uma function chamada eventdata(), que nos retorna um Xml com as informações do event_type.

Cada event_type possui seu próprio conjunto de informações que será retornado por eventdata(), informações estas que podem ser achadas no BOL, mas em todos os retornos da função teremos as seguintes informações em comum:

• Data e hora da execução.
• SPID da conexão que efetuou o comando.
• Login e usuário que efetuou o comando.
• Tipo do evento.

Na verdade a informação mais importante para o nosso exemplo é a tag que recebemos de eventdata(), nesta tag temos as opções da conexão e os comandos T-SQL , os quais iremos extrair do xml utilizando Xquery.

Abaixo temos um exemplo de xml retornado pela function eventdata():

<EVENT_INSTANCE>
 <PostTime>2004-01-30T11:58:47.217</PostTime>
 <SPID>57</SPID>
 <EventType>CREATE_PROCEDURE</EventType>
 <ServerName>DAVINCI</ServerName>
 <LoginName>DAVINCI\Eugenio</LoginName>
 <UserName>DAVINCI\Eugenio</UserName>
 <DatabaseName>TesteDDL</DatabaseName>
 <SchemaName>dbo</SchemaName>
 <ObjectName>sp_Auditoria</ObjectName>
 <ObjectType>PROCEDURE</ObjectType>
 <TSQLCommand>
  <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
   ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"
   ENCRYPTED="FALSE" />
  <CommandText>
           Create Procedure sp_Auditoria As   Print "Versão 1.0"
  </CommandText>
 </TSQLCommand>
</EVENT_INSTANCE>

Agora vamos a parte interressante do artigo, a codificação da nossa trigger de auditoria:

CREATE TRIGGER TR_AUDITORIA
ON DATABASE FOR DDL_PROCEDURE_EVENTS
As 

Declare @Dados as xml
Set @Dados = eventdata()

Insert into Versoes ( Objeto, FonteDDL,Usuario,Data ) 
SELECT
CONVERT(CHAR(254),@Dados.query("data(//ObjectName)")),
CONVERT(VARCHAR(max),@Dados.query("data(//TSQLCommand/CommandText)")),
CONVERT(CHAR(254),@Dados.query("data(//UserName)")),
CONVERT(char(30),@Dados.query("data(//PostTime)"))

Para testar-mos nossa trigger vamos disparar alguns comandos para a criação e alteração de procedures:

Create Procedure sp_Auditoria  As  Print "Versão 1.0"
Alter Procedure sp_Auditoria  As  Print "Versão 2.0"
Drop Procedure sp_Auditoria 

Depois verificamos na tabela Versoes o que foi logado de informações:

Select * from Versoes

Com estas novas funcionalidades podemos extender muito a segurança e recuperação de nossos fontes. Verifique outros event_type no BOL e como aplicá-las em suas futuras aplicações utilizando SQL Server 2005.

Este exemplo foi criado no SQL Server 2005 Beta 2, agora só nos falta o Bill liberar a versão final!

Um grande abraço a todos e até o próxima.

Eugênio Spolti