ATPS: Procedures e Trigger

Atividade 2: Procedures e Trigger

 Etapa 2

Passo 1

Fazer uma pesquisa no livro texto ou nos livros complementares da disciplina sobre os fundamentos para criação e utilização de Triggers e Procedures. Em seguida, elaborar um tutorial mostrando os procedimentos para programar procedures e triggers. O tutorial deve conter pelo menos cinco páginas.

2.1 Criação Procedures e Triggers

Trigger (gatilho)

Uma Trigger (gatilho) é um tipo especial de procedimento armazenado que é executado automaticamente quando um evento ocorre no servidor de banco de dados. Os gatilhos DML são executados quando um usuário tenta modificar dados através de um evento DML (linguagem de manipulação de dados). Os eventos DML são instruções INSERT, UPDATE ou DELETE em uma tabela ou exibição. Esses gatilhos são disparados quando qualquer evento válido é acionado, independentemente de quaisquer linhas da tabela serem afetadas ou não.

O gatilho e a instrução que o dispara são tratados como uma transação simples, que pode ser revertida dentro do gatilho. Se um erro grave for detectado (espaço em disco insuficiente, por exemplo), toda a transação será revertida automaticamente.

Os gatilhos DML são semelhantes a restrições, pois podem impor integridade de entidade ou integridade de domínio. Em geral, a integridade da entidade sempre deve ser imposta no menor nível por índices que fazem parte das restrições PRIMARY KEY e UNIQUE ou que são criados independentemente de restrições. A integridade de domínio deve ser imposta por restrições CHECK e a RIN (integridade referencial) deve ser imposta por restrições FOREIGN KEY. Os gatilhos DML são muito úteis quando os recursos suportados por restrições não atendem às necessidades funcionais do aplicativo.

• Os gatilhos DML podem colocar as alterações em cascata através das tabelas relacionadas no banco de dados; no entanto, essas alterações podem ser executadas com mais eficiência utilizando restrições de integridade referencial em cascata. As restrições FOREIGN KEY só podem validar um valor de coluna com uma combinação exata de valor em outra coluna a menos que a cláusula REFERENCES defina uma ação referencial em cascata.

• Podem proteger contra operações mal-intencionadas ou incorretas do tipo INSERT, UPDATE, e DELETE, e fazer cumprir as outras restrições mais complexas do que aquelas definidas nas restrições CHECK.

Diferentemente das restrições CHECK, os gatilhos DML podem fazer referência a colunas em outras tabelas. Por exemplo, um gatilho pode usar um SELECT de outra tabela para comparar com os dados atualizados ou inseridos e para efetuar ações adicionais, como modificar os dados ou exibir uma mensagem de erro definida pelo usuário.

• Podem avaliar o estado de uma tabela antes e depois da modificação dos dados e efetuar ações com base nessa diferença.

• Vários gatilhos DML do mesmo tipo (INSERT, UPDATE, ou DELETE), em uma tabela, permitem que múltiplas ações diferentes ocorram em resposta à mesma instrução de modificação.

• Restrições só podem comunicar erros através de mensagens de erro padronizadas do sistema. Se o aplicativo exigir ou beneficiar-se de mensagens personalizadas e tratamento de erros mais complexo, é necessário usar um gatilho.

• Gatilhos DML podem desabilitar ou reverter alterações que violam a integridade referencial, cancelando assim a tentativa de modificação de dados. Pode ser que tais gatilhos só tenham efeito ao alterar uma chave estrangeira e se o novo valor não combinar com sua chave primária. Porém, as restrições FOREIGN KEY normalmente são usadas com este propósito.

• Se houver restrições na tabela de gatilhos, elas serão verificadas após a execução do gatilho INSTEAD OF, mas antes da execução do gatilho AFTER. Se as restrições forem violadas, as ações do gatilho INSTEAD OF serão revertidas e o gatilho AFTER não será executado.

Tipos de Gatilhos DML

Gatilho AFTER

Os gatilhos AFTER são executados depois que a ação das instruções INSERT, UPDATE, MERGE ou DELETE é executada. Os gatilhos AFTER jamais são executados em caso de uma violação de restrição; por isso, estes gatilhos não podem ser usados em processamentos que possam evitar as violações de restrição. Para cada ação INSERT, UPDATE ou DELETE especificada em uma instrução MERGE, o gatilho correspondente é disparado para cada operação DML.

Gatilho INSTEAD OF

Os gatilhos INSTEAD OF substituem as ações padrão da instrução de gatilho. Portanto, eles podem ser usados para executar uma verificação de erro ou valor em uma ou mais colunas, e executar ações adicionais antes de inserir, atualizar ou excluir uma ou mais linhas. Por exemplo, quando o valor que estiver sendo atualizado em uma coluna de salário calculado por hora, de uma tabela de folha de pagamento, exceder um valor especificado, um gatilho poderá ser definido para produzir uma mensagem de erro e reverter a transação, ou inserir um novo registro em uma trilha de auditoria, antes de inserir o registro na tabela de folha de pagamento.A principal vantagem dos gatilhos INSTEAD OF é que eles habilitam exibições que não seriam atualizáveis para oferecer suporte a atualizações. Por exemplo, uma exibição baseada em várias tabelas base deve usar um gatilho INSTEAD OF para oferecer suporte a inserções, atualizações e exclusões que referenciam dados em mais de uma tabela. Outra vantagem dos gatilhos INSTEAD OF é que eles o habilitam a codificar lógica que pode rejeitar partes de um lote e, ao mesmo tempo, permitir que outras partes do lote tenham êxito.

Funcionalidade dos gatilhos AFTER e INSTEAD OF:

Gatilhos CLR

Um gatilho CLR pode ser um gatilho AFTER ou INSTEAD OF. Um gatilho CLR também pode ser um gatilho DDL. Em vez de executar um procedimento armazenado Transact-SQL, um gatilho CLR executa um ou mais métodos gravados em código gerenciado que são membros de um assembly criado no .NET Framework e carregado para o SQL Server.

Os gatilhos DDL são executados em resposta a diversos eventos DDL (linguagem de definição de dados). Esses eventos correspondem, basicamente, a instruções Transact-SQL CREATE, ALTER e DROP e determinados procedimentos armazenados do sistema que executam operações do tipo DDL. Os gatilhos de logon são disparados em resposta