Banco De Dados

Fundamentos para criação e utilização de Triggers e Procedures

PROCEDURE

Procedure é uma coleção de comandos em SQL para otimização de Banco de dados. Encapsula tarefas repetitivas, aceita parâmetros de entrada e retorna um valor de status (para indicar aceitação ou falha na execução). O procedimento armazenado pode reduzir o tráfego na rede, melhorar o desempenho, criar mecanismos de segurança.

A criação e utilização desta instrução são simples:

CREATE PROC[EDURE] nome_procedure

[

@parametro tipo_parametro [=valor_padrao]

]

AS

instrucao_SQL [...n]

Criando uma PROCEDURE que consulta todos os produtos da tabela ‘Produto’ ordenando pelo o valor do produto (Valor) em ordem decrescente:

CREATE PROCEDURE sp_produto_informar

AS SELECT

Nome, Descrição, Valor FROM Produto ORDER BY Valor DESC

A instrução ORDER BY ordena um determinado campo, porém prejudica fortemente o desempenho do servidor, portanto evite usar esta instrução (que muitas vezes é indispensável).

Uma Procedure pode reduzir o tráfego na rede, melhorar a performance de um banco de dados, criar tarefas agendadas, diminuir riscos, criar rotinas de processamento, etc. Por todas estas e outras funcionalidades é que as procedures são de extrema importância para os DBAs e desenvolvedores.

Há 5 Procedimentos (Procedures) básicos que podemos criar:

• Procedimentos Locais – São criados a partir de um banco de dados do próprio usuário;

• Procedimentos Temporários – Existem dois tipos de procedimentos temporários:

Locais, que devem começar com # e Globais, que devem começar com ##;

• Procedimentos de Sistema – Armazenados no banco de dados padrão do SQL Server (Master), podemos identificá-los com as siglas sp, que se origina de stored procedure. Tais proceduresexecutam as tarefas administrativas e podem ser executadas a partir de qualquer banco de dados.

• Procedimentos Remotos – Podemos usar Queries Distribuídas para tais procedures. São utilizadas apenas para compatibilidade.

• Procedimentos Estendidos – Diferente dos procedimentos já citados, este tipo de procedimento recebe a extensão .dll e são executadas fora do SGBD SQL Server. São identificadas com o prefixo xp.

Uma PROCEDURE pode também fazer qualquer outra operação na base, como por exemplo: inserir, alterar e deletar algum registro. A instrução abaixo permite inserir algum registro na tabela através de uma PROCEDURE, passando os parâmetros necessários.

CREATE PROCEDURE sp_produto_inserir

@Nome VARCHAR(64), @Descricao TEXT, @Valor MONEY

AS INSERT INTO Produto(Nome, Descricao, Valor)

VALUES( @Nome, @Descricao, @Valor)

PROCEDURE DE ALTERAÇÃO:

CREATE PROCEDURE sp_produto_alterar

@ID BIGINT, @Nome VARCHAR(64), @Descricao TEXT, @Valor MONEY AS UPDATE Produto SET Nome = @Nome, Descricao = @Descricao, Valor = @Valor WHERE ID = @ID

TRIGGER

Uma trigger é um tipo especial de procedimento armazenado, que é executado sempre que há uma tentativa de modificar os dados de uma tabela que é protegida por ele.

Quando há uma tentativa de inserir, atualizar ou excluir os dados em uma tabela, e um TRIGGER tiver sido definido na tabela para essa ação específica, ele será executado automaticamente, não podendo nunca ser ignorado.

Ao contrário dos procedimentos armazenados do sistema, os disparadores não podem ser chamados diretamente e não passam nem aceitam parâmetros.

Usos e aplicabilidade dos TRIGGERS

• Impor uma integridade de dados mais complexa do que uma restrição CHECK;

• Definir mensagens de erro personalizadas;

• Manter dados desnormalizados;

• Comparar a consistência dos dados – posterior e anterior – de uma instrução UPDATE;

As TRIGGERS são criadas utilizando a instrução CREATE TRIGGER que especifica a tabela onde ela atuará, para que tipo de ação ele irá disparar suas ações seguido pela instrução de conferência para disparo da ação.

Em SQL, um gatilho típico é composto de três componentes, que seguem o Modelo evento - condição – ação:

CREATE TRIGGER nome_do_gatilho ON dono.Nome_da_tabela

FOR INSERT (ou SELECT ou UPDATE ou DELETE)

AS

Código para execução

Outro exemplo: