Stored procedures e triggers em SQL Server

Desenvolver aplicativos para um banco de dados desktop implica em que toda a regra de negócio de nossa aplicação deverá residir ao lado do cliente, tornando o banco de dados um simples depósito de texto organizado lógica e fisicamente. SGDBs Cliente/Servidor permitem que estas regras “desçam”, se não totalmente, em grande parte, para o Servidor. Para isso existem blocos modulares de código, récompilados e armazenados no servidor como parte de um Banco de Dados, conhecidos como stored procedures e triggers. Cada um destes blocos pode conter instruções de controle de fluxo, comandos DML ou simples consultas. Entre as vantagens de sua utilização, que muitas vezes se confundem com as vantagens da própria arquitetura cliente servidor podemos citar:

pCentralização: Qualquer mudança na regra de negócio do aplicativo implicava na atualização de dezenas, às vezes centenas de binários espalhados pela empresa, pela cidade ou pelo mundo. Ao invés disso, basta rodar um script no servidor e os usuários sequer ficam sabendo que mudanças ocorreram;

pSegurança: A vida do DBA fica mais fácil, ao invés de ter que quebrar a cabeça com emaranhados de diretivas de segurança para diferentes níveis de privilégios sobre os diferentes objetos de um banco de dados, ele pode definir a segurança a partir de stored procedures;

pTrafico de rede: Uma stored procedure ou um trigger rodam no servidor, tudo o que o cliente faz é executá-los, implícita ou explicitamente, com eventuais parâmetros, e receber de volta alguma possível retorno.

pClientes magros: Como a regra de negócio esta no servidor, aplicativos clientes podem ser menores, exigindo menos recursos do sistema (disco, memória e CPU) e conseqüentemente hardware de menor custo;

Ainda consultas embutidas dentro de stored procedures podem ocasionar um aumento no desempenho de consultas repetitivas, já que possui seu plano se execução pré-compilado. A partir da próxima seção vamos estudar stored procedure e logo a seguir triggers, ambos possuem muitos aspectos em comum por isso evite de ir direto as triggers.

Stored procedures (procedimentos armazenados)

Assim como as triggers, stored procedures são módulos de código armazenados no servidor, porém enquanto as triggers são executadas implicitamente, pois estão associadas a um comando DML executado em uma tabela (Podemos dizer que as triggers estão associadas a eventos) as stored procedures, ao contrário, são executadas. Uma stored procedure podem ter o mais variado objetivo: Realizar uma simples consulta ou um complexo cálculo de regra de negócio do aplicativo.

Como já dito em parágrafos anteriores as stored procedures são um elemento importantíssimo na Arquitetura Cliente\Servidor. Vamos a partir de agora estudar diversos aspectos relacionados a estas.

Tipos de Sotored procedures

O SQL Server possui pelo menos 4 tipos de stored procedures: Locais, são as procedures de usuários, utilizadas pelos aplicativos; Temporais, existentes apenas durante uma seção; de Sistema, criadas pelo Servidor durante a instalação com funções administrativas e Estendidas, chamadas a partir de uma DLL. Vamos estudar cada um destes tipos.

procedures Temporárias

Stores procedures temporárias são armazenados no banco TempDB (Outros objetos com exceção de views ou functions, podem ser criados de forma temporária). Existem dois tipos de Stores procedures temporárias:

Locais: Visíveis apenas na seção corrente, ou seja, para o usuário que a criou. Este tipo de procedure é excluído automaticamente ao fim da seção. Para criar uma procedure temporária local o prefixo # deve ser adicionado ao nome.

Globais: Visíveis para todas as seções. Este tipo de procedure também é excluído automaticamente ao fim da seção em que foi criada, a não ser que esteja sendo utilizada por alguma outra seção no momento da exclusão, neste caso, a exclusão ocorrerá ao fim do comando T-SQL que a fez referência. Para criar uma procedure temporária global o prefixo ## deve ser adicionado ao nome.

System Sored Pocedures (Procedimentos armazenados de sistema)

Um aspecto importantíssimo do SQL Server são as System stored procedures (procedures armazenadas de sistema). Estas procedures são criadas durante a instalação do SQL Server e residem no banco de dados Master. Sua função é facilitar a realização de rotinas administrativas, bem como retornar informações sobre o sistema e\ou seus objetos. Uma System stored procedures tem por convensão SP_ como prefixo, e é executa como uma procedure de usuário normal. O SQL Server 2000 possui uma grande quantidade de System stored procedures (beirando os quatro dígitos, muitas sequer documentadas) e descrever todas elas seria assunto para uma obra completa, por isso vamos estudar algumas a titulo de exemplo:

sp_helpdb: Traz informações sobre todos os banco de dados do servidor (se executada sem parâmetros) ou de um banco de dados especifico

sp_help: Traz informações sobre um objeto de um banco de dados, como por exemplo, uma tabela

sp_columns: Traz informações sobre colunas de uma tabela

sp_who: Retorna informações sobre usuários e processos do servidor. Opcionalmente pode ser passado o nome do usuário como parâmetro. Sp_who2 (não documentada) traz um número mais de informações

sp_password: Adiciona ou altera a senha de um login do SQL Server. Os argumentos são: senha atual, nova senha e login. Para um novo usuário senha atual deve ser nulo

Extended Stored procedures (procedimentos armazenados estendidos)

Extended stored procedures são DLL’s criadas em alguma linguagem de programação, que podem ser executadas dinamicamente pelo SQL Server. Normalmente são desenvolvidas para executar tarefas que o SQL Server não pode fazer. Como convenção, possuem o prefixo XP_ no nome. O SQL Server instala diversas procedures Estendias, e novas podem ser registrada através da chamada da procedure de sistema sp_addextendedproc. Para o usuário, uma Extended stored procedures é tratada da mesma forma que outra stored procedure, tanto na execução quanto na passagem de parâmetros. Vamos ver um exemplo simples de utilizarão de uma procedure estendida. Xp_cmdshell permite executar um comando no comand.com do sistema operacional, a partir do SQL Server, obtendo a mesma saída. O código abaixo altera data do Sistema Operacional a partir de um comando executado no SQL Server:

A alteração ocorre no Sistema Operacional onde o servidor estiver rodando, independente de onde o mesmo seja executado.

Stored procedures de usuário

procedures de usuários são aquelas utilizadas como uma extensão do aplicativo no servidor. Normalmente este é o tipo de procedure que mais interessa no desenvolvimento de software. A sintaxe de criação de uma stored procedure é:

CREATE PROC [ EDURE ] procedure_name [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ]

[ WITH { RECOMPILE | ENCRYPTION [ FOR REPLICATION ] AS sql_statement [ ...n ]

[ ; number ]

| RECOMPILE , ENCRYPTION } ]

Nosso primeiro exemplo denominado TodoNome simplismente junta o Titulo de cortesia, Nome e sobrenome dos funcionários da tabela Employees do Banco Northwind em uma única saída. A criação da procedure:

É um bom costume, a fim de evitar a geração de erros, verificar antes da criação da procedure, se a mesma já não existe no Banco de dados, para isso procuramos uma entrada na tabela sysobjects com nome TodoNome e tipo P (Stored procedure), em caso afirmativo a procedure é excluída antes da criação:

Executando uma Stored procedure

A execução pode ser feita pela simples chamada da procedure, quando esta for o primeiro comando de um bloco, ou através do método execute (ou simplesmente exec):

Em nosso segundo exemplo, TodoNome2 possui uma pequena alteração de modo que possamos condicionar a cidade e o país do empregado através da passagem de parâmetros. Observe:

Podemos executar a procedure de diversas maneiras:

Passando os argumentos, na mesma ordem, separados por vírgula:

Ou citando o nome do parâmetro seguido do valor:

Em ambos os casos o resultado é o mesmo.

Parâmetros Default

Se tentarmos executar a stored procedure anterior sem informar os parâmetros, teremos um erro, pois estes não podem ser omitidos, a não ser que possuam um valor Default. Um valor default pode ser indicado logo após a declaração do parâmetro. Vamos alterar a procedure TodoNome para ter UK como pais Default:

Se chamarmos a procedure apenas com o parametro @cidade, o pais default será utilizado pela procedure:

Argumento With Encryption

A tabela de sistema syscomments possui o texto de todas as stored procedures e Trigger (e de diversos outros objetos). Cada banco de dados possui sua própria syscomments. Através desta tabela podemos recuperar o texto usado na criação do objeto (Como veremos mais adiante). Caso haja a necessidade de ocultar o texto da procedure ou trigger, pode-se usar o argumento With Recompile no momento da criação do objeto. Isto criptografa o texto do objeto, tornando-o ilegível.

Nem mesmo o adminstrador do banco de dados consegue recuperar texto de um objeto criptografado. With Encryption é uma excelente alternativa de oculatar a regra de negócio do aplicativo.

Com a descida da regra de negócio do aplicativo no cliente, onde normalmente residia em um arquivo binário compilado, para o servidor onde toda a lógica do sistema pode ser exposta em texto, o argumento With ENCRYPTION esta pode ser uma opção para ocultar a regra de negócio do seu sistema até mesmo do administrador do banco de dados.

Para criptografar a procedure basta usar o argumento with encryption após o nome da procedure:

Argumentos For Replication e NOT FOR REPLICATION

O argumento For Replication especifica que uma procedure não pode ser executada por um assinante durante a replicação. Já Not For Replication indica que uma trigger não deverá alterar a tabela a qual esta associado.

Argumento With Recompile

O SQL Server cria um plano de execução para qualquer sentença T-SQL antes de sua execução. Um plano de execução fica armazenado em um local especial de memória denominado procedure Cache. A pré-existência de um plano de execução causa um ganho de performance significativo. Em uma stored procedure, o plano de execução é compilado no momento de sua criação. Este plano será re-compilado, para fins de otimização, sempre a procedure sofrer algum tipo de alteração (com ALTER PROCEDURE) ou mesmo que um objeto utilizado por ela, como uma tabela, for alterado.

Podemos ainda forçar a criação de um novo plano de execução de uma procedure se a mesma for criada ou executada com a opção With Recompile. Criar a procedure com a opção With Recompile força a criação de um novo plano de execução a cada nova execução. Já a procedure se sistema sp_recompile, provoca a re-compilação do plano de execução a próxima vez que a procedure for executada. Uma sugestão é utilizar sp_recompile no plano de manutenção do banco de dados.

Recursividade e Aninhamento

Aninhamento ocorre quando um stored procedures ou triggers executa outra stored procedures ou triggers em seu escopo. O número máximo de aninhamentos permitidos é de 32.

Recursividade é um tipo de aninhamento, porém ocorre quando um objeto chamar a si mesmo. Existem dois tipos de recursividade, a Direta e a Indireta. Na direta o objeto chama a si mesmo no mesmo escopo. Na indireta o objeto é chamado por outro objeto que não ele próprio, porém executado direta ou indiretamente por ele. Para permitir recursividade Direta de triggers, a configuração RECURSIVE_TRIGGERS do banco de dados deve estar ativa. Esta opção não influi na recursividade Indireta.

Execução Automática

Uma stored procedure pode ser designada para execução automática sempre que o SQL Server for iniciado. Tal procedure não pode ter parâmetros de entrada, deve residir no banco Master e ser criada por um membro do papel sysadmin.

Para inicializar ou cancelar a inicialização automática, deve-se usar a procedure de sistema sp_procoption, que recebe três argumentos: Nome da procedure, opção que é sempre startup e valor. O código abaixo configura uma suposta procedure de nome Proc_teste para inicilização automática:

EXEC sp_procoption 'teste',startup,true

Para desabilitar a execução automática, basta alterar o último argumento para False:

EXEC sp_procoption 'teste',startup, False

Embora uma procedure para execução automática não possa receber parâmetros, o usuário pode criar uma procedure que execute uma ou mais procedures passando os parâmetros necessários.

Agrupamento

procedures criadas com o mesmo nome são chamadas procedures Agrupadas. Agrupar procedures pode ser útil para fins de organização lógica do código. Uma procedure agrupada recebe um número, a única regra é que a primeira deve ter o número 1:

CREATE PROCEDURE TodoNome;1

CREATE PROCEDURE TodoNome;10

A exclusão de qualquer procedure do grupo causa a exclusão de todas as demais.

Alterando uma Stored Procedure

A sintaxe para alterar uma stored procedure é basicamente a mesma utilizada para a criação, substituindo-se a palavra chave Create por Alter.

Excluindo uma ou mais Stored Procedure

Para remover uma ou mais procedure ou um grupo de procedures utilizamos o comando Drop procedure, na listagem abaixo excluímos a procedure TodoNome:

Podemos excluir diversas procedures simultaneamente, para isto basta listar seus nomes separadas por virgula.

Podemos verificar a existência da procedure antes da exclusão, isto vai evitar uma mensagem de erro caso o objeto já não faça mais parte do Banco de Dados:

Lembrando que ao excluir uma procedure agrupada, todo o conjunto será excluído.

Renomeando uma Stored Procedure

Para renomear uma stored procedure utilizamos a System procedure sp_rename, que recebe dois argumentos: O nome do objeto, seu novo nome e seu tipo.

Verificando dependências

Antes de excluir um objeto de nosso banco de dados, é uma pratica recomendada, e que poderá lhe poupar muita dor de cabeça, verificar quais dependências este objeto possui. O SQL Serve possui uma procedure de sistema com este fim, sp_depends, que recebe como parâmetro o nome do objeto. Por exemplo, para verificarmos as dependências de nossa procedure TodoNome anteriormente criada, podemos executar:

Obtendo o código fonte de uma stored procedure

Você pode obter o código fonte (definição) de uma procedure, desde que a mesma não esteja criptografada, pelo EM (o que não será descrito aqui) ou através da System procedure Sp_helptext. Esta procedure recebe como argumento o nome da procedure a qual desejamos obter o código fonte, pro exemplo:

Sp_helptext pode retornar o código não apenas uma procedure, mas de qualquer objeto armazenado em SYSCOMMENTS, como rules, defaults, UDFs, views e triggers.

Retorno de dados, parâmetros OUTPUT e RETURN

No SQL Server 2000 uma stored procedure pode retornar valores ao cliente de 3 maneiras diferentes:

•    Um ou mais conjuntos de resultados provenientes de consultas realizada no escopo da procedure;

•    Parâmetros OUTPUT: Um parâmetro como o que utilizamos nos exemplos anteriores é um parâmetro de entrada, é utilizado no escopo da procedure e que não retorna nenhum valor ao usuário. Já um parâmetro de saída (OUTPUT) pode retornar um valor ao aplicativo, além do que pode ainda ser utilizado para passar um valor para a procedure, como um parâmetro de entrada. O funcionamento é análogo à passagem de parâmetros por referencia a uma procedure no Delphi. Definimos um parâmetro com de saída utilizando a palavra chave OUTPUT.

• Return: Return é um parâmetro especial, utilizado para retornar um número inteiro ao usuário. Normalmente é utilizado para tratamento de erros ou para verificar determinadas condições na execução da procedure. A procedure criada como exemplo não realiza nada internamente além de uma consulta simples e de atribuir alguns valores a parâmetro, seu único objetivo é facilitar a compreensão das diversas maneiras em que podemos passar parâmetros a uma stored procedure e obter resultados.

A procedure Teste_OUTPUT recebe dois parâmetros, um de entrada o outro de saída. Em eu escopo é atribuído a variável do parâmetro de entrada o seu valor somado ao valor da variável do parâmetro de entra. Isto significa que, ao lermos o parâmetro de sainda (@Valor2) após a execução da procedure, ele deve ter a soma dos valores dos dois parâmetros. Observe no exemplo a execução da procedure e a leitura do parâmetro de saída:

Note que Return deve ter a metade do valor atribuído ao parâmetro @Valor. Para verificarmos o valor de retorno, devemos declarar uma variável e atribuí-la ao nome da procedure no momento da execução, acompanhe:

Depurando uma stored procedure

Caso você enfrente problemas com stored procedures e necessite depurá-las, a melhor ferramenta para tal é o QA, que possui um debuger poderoso e intuitivo. Para abrir o depurador do QA siga os seguintes passos:

•    Abra o QA;

•    Clique em Object Browser (ou tecle F8);

•    No Object Browser expanda o banco de dados;

•    Expanda o nó stored procedures;

•    Na procedure que deseja depurar, clique com o botão direito do mouse e selecione

• Debug;

Na caixa de dialogo Debug procedure informe o valor para cada parâmetro. Clique em execute. Será aberta a IDE de depuração do T-SQL, onde o usuário terá diversos recursos para de depuração, como criar pontos de parada, alterar parâmetros etc. Para maiores informação consulte o BOL.

Triggers (Gatilhos)

Um trigger (gatilho) é muito semelhante a uma stored procedure, exceto pelo fato de ao invés de ser executado explicitamente como o primeiro, o gatilho esta associada a um evento disparado através de um comando DML. Muitas vezes restrições não são suficientes para manter a integridade dos dados, esta é talvez uma das principais utilidades de um Trigger, porém ele pode ser usado com os mais diversos fins, certa vez criei um trigger para uma bibliotecária preguiçosa, que achava inconveniente ter que cadastrar um exemplar sempre que uma nova publicação fosse adquirida. Um Trigger pode ser usado ainda para fins de segurança, você pode desenvolver um método próprio de auditoria. Vamos estudar os principais conceitos relacionados a triggers e a seguir vamos criar um exemplo onde procuramos aplicar todos estes conceitos.

Eliminando mensagens de Retorno

SET NOCOUNT, setado como ON, elimina mensagem na seção informando o número de linhas afetadas por uma transação. É uma pratica recomendada eliminar estas mensagens em triggers, pois caso contrário, o usuário poderá ter diversas mensagens informando que “n” linhas foram afetadas quando na verdade executou uma único comando DML. Para retomar o retorno de mensagens, basta setar SET NOCOUNT para OFF.

Tipos de Triggers

O SQL Server 2000 permite a criação de dois tipos de triggers:

•    AFTER (Insert, Delete, Update): O Trigger After é o mesmo For (Podemos ainda continuar usando For ao invés de After),o único tipo disponível até a versão 7 do SQL Server. Este tipo de trigger é executado após o evento.

•    INSTEAD OF (Insert, Delete, Update): triggers Instead Of são executados ao inves da ação original.

Tabelas inserted e deleted

As tableas inserted e deleted são criados pelo SQL Server durante a execução de um Trigger, e contem, respectivamente, os dados inseridos ou excluídos da tabela. São residentes apenas em memória, temporárias, e seus dados não podem ser alterados.

Verificando qual campo foi alterado

Em um trigger disparado durante uma atualização, é interessante saber se um determinado campo foi alterado, ou ainda, quais campos foram alterados. O SQL Server nos fornece duas funções com este fim: IF UPDATE (nome_da_coluna) para saber se determinada coluna sofreu alteração, e COLUMNS_UPDATED() para saber quais colunas foram alteradas

Ordem de Execução

Podemos ter mais de um Trigger After associado a um mesmo evento de uma mesma tabela. É possível determinar qual trigger será disparado por primeiro (first) ou em último (last). Os demais (ordem none) serão todos executados entre o primeiro e o último sem uma ordem específica. Para determinar a ordem de um trigger usamos a System procedure sp_settriggerorder, que recebe três argumentos: Nome do trigger, ondem e evento. Trigger Instead Of não podem ter uma ordem de execução definida.

Número de Registros Afetados

Podemos determinar o número de registros afetados pro um trigger através da variável global @@ROWCOUNT.

Exemplo de Triggers

Embora o SQL Server 2000 ofereça ótimos mecanismos de auditoria, podemos utilizar triggers para rastrear qualquer tipo de alteração em nosso banco de dados e criar nosso próprio sistema de auditoria. O Trigger que apresento como exemplo é simples, mas requer algumas explicações. Suponha que o Diretor de uma empresa o qual utiliza um sistema desenvolvido por você, quer saber quais usuários tem feito alterações em preços de mercadorias. É preciso ainda saber quando e de onde (estação) esta alteração foi feita, e de onde (estação). Vamos utilizar como cenário o Banco de Dados Northwind, tabela Products. Esta tabela possui um campo UnitPrice, onde esta armazenado o preço atual do produto. Temos que então criar um trigger After update, que será chamada de Audita. Vamos precisar ainda de uma tabela para armazenar a auditoria, o trigger deve verificar se esta tabela existe, e em caso negativo, criá-la. A definição da tabela é a seguinte:

Note que os campos Usuário, Estacao e Data tem como default, respectivamente, as funções Current_user, Host_name() e GetDate(). Current_User retorna o usuário corrente da seção, Host_name() a estação e GetDate() a data e hora corrente. Estabelecendo estas funções como Default, seus valores podem ser omitidos no momento da inserção do registro na tabela.

Em seguida temos que obter das tabelas Inserted e Deleted o novo e antigo preço do produto, respectivamente, e ainda seu código, para sabermos qual produto foi alterado, para isso é necessário declarar três variáveis locais: @id, @preconovo e @precoantigo:

DECLARE @id INT, @preconovo MONEY,@precoantigo MONEY

Em seguida atribuímos a estas variáveis os valores das tabelas Inserted e Deleted:

SELECT @id = (SELECT ProductID FROM deleted), @preconovo (SELECT UnitPrice FROM inserted),@precoantigo =(SELECT

UnitPrice FROM deleted)

Finalmente inserimos o registro na tabela de auditoria

INSERT INTO Audit_1

(Codigo_Produto,Preco_Unitario_Novo,Preco_Unitario_Antigo) VALUES (@id,@preconovo,@precoantigo)

Um último detalhe: O trigger vai ser disparado sempre que a tabela products for alterada, mas só nos interessa auditar registros que tenham o campo UnitPrice alterado, para isso, utilizamos a função if update para realizar esta verificação, e só então executar a auditoria:

IF UPDATE(UnitPrice) BEGIN

O trigger completo ficou assim:

USE Northwind

CREATE TRIGGER Audita

ON Products

AFTER UPDATE AS

IF UPDATE(UnitPrice)

BEGIN

SET NOCOUNT ON

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name =

'Audit_1' AND xtype = 'u')

CREATE TABLE Audit_1(

Codigo INT IDENTITY(0, 1) NOT NULL Primary Key,

Usuario CHAR(30) NOT NULL default Current_user,

Data DATETIME NOT NULL default GetDate(),

Codigo_produto INT NOT NULL,

Preco_Unitario_Novo MONEY NOT NULL,

Preco_Unitario_Antigo MONEY NOT NULL)

DECLARE @id INT, @preconovo MONEY,@precoantigo MONEY

SELECT @id = (SELECt ProductID FROM deleted),

@preconovo = (SELECT UnitPrice FROM inserted),@precoantigo

=(Select UnitPrice FROM deleted)

INSERT INTO Audit_1 (Codigo_Produto,

Preco_Unitario_Novo, Preco_Unitario_Antigo)

VALUES (@id,@preconovo,@precoantigo)

END

Para testar altere alguns preços de produtos e depois leia o conteúdo da tabela Audit_1.