Salve, Salve Comunidade, bom dia.
Hoje vou falar um pouco sobre Trigger DML, não estou me referindo a Trigger de For ou After, na verdade quero compartilhar com vocês a minha experiência com o Trigger DML Instead O, recurso que eu particularmente utilizei muito pouco nestes longos anos de carreira profissional.
Mas antes de destacar o Trigger DML Instead Of, vou abordar um sobre o Trigger, recurso que eu gosto muito e bem implementado pode se tornar um mecanismo de automatização de procedimentos e processos muito útil.
Introdução
Considerado como um dos mecanismos primários para fazer cumprir as regras empresariais e a integridade dos dados. O Trigger(Gatilho) é um tipo especial de procedimento armazenado que automaticamente entra em funcionamento quando um evento de linguagem é executado. O SQL Server inclui três tipos gerais de gatilhos: gatilhos DML, disparadores DDL, e gatilhos de logon.
Conhecendo um pouco mais sobre Triggers
Todo e qualquer trigger possui alguns disparados, tanto para transações DDL, como também, para transações DML. Os disparadores DDL são invocados quando um evento de linguagem de definição de dados (DDL) ocorre no servidor ou no banco de dados. Os Triggers DML, são invocados quando ocorre um evento de manipulação de definição de dados (DML) no banco de dados. Eventos DML incluem instruções INSERT, UPDATE ou DELETE, que modificam os dados em uma tabela especificada ou exibição.
Um Trigger DML pode examinar outras tabelas e incluir instruções Transact-SQL complexas. O gatilho e a instrução acionados 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.
Triggers DML
Os Triggers DML apresentam diversas utilidades no que se relaciona a Integração de Dados e Automatização de Transações em um Banco de Dados, dentre elas, destaco:
Podemos colocar em cascata as alterações pelas tabelas relacionadas no banco de dados; no entanto, essas alterações podem ser executadas com mais eficiência com o uso das restrições de integridade referencial em cascata.
Podemos 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.
Tipos de Triggers DML
Implementados desde a versão 2000 o Microsoft SQL Server, apresenta basicamente três tipos de Triggers para Transações DML, conforme apresento abaixo:
After: Um Trigger AFTER pode executado depois que ação das instruções INSERT, UPDATE ou DELETE for executada. Especificar AFTER é o mesmo que especificar FOR, que é a única opção disponível em versões anteriores do Microsoft SQL Server. Os gatilhos AFTER podem ser especificados somente em tabelas.
Instead Of: Um Trigger INSTEAD OF pode ser executado no lugar da ação de gatilho usual. Os triggers INSTEAD OF também podem ser definidos em exibições com uma ou mais tabelas, na quais seja possível estender os tipos de atualizações que uma exibição pode suportar.
CLR: Um Trigger CLR pode ser um trigger AFTER ou INSTEAD OF. Um Trigger CLR também pode ser um disparador DDL. Em vez de executar um procedimento armazenado Transact-SQL, um Trigger 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.
Pois bem, após esta breve introdução sobre Trigger, vou destacar o Trigger DML Instead Of, ou chamado em Português Gatilho Em Vez, tradução que não posso dizer que é ruim pois não sou um profundo conhecer de Inglês, mas sei que fica muito complicado de se utilizar este tipo de recurso com esta tradução.
Estudo de Caso
Para o nosso Estudo de Caso, pensei em algum bastante simples e que possa facilitar ainda mais o entendimento e forma de uso do Trigger DML Instead Of, sendo assim, vamos criar um ambiente fictício de uma Escola. A Escola possui um pequeno sistema de CRUD que esta apresentando algumas falhas básicas, dentro elas, a falha para o cadastramento de um novo aluno, quando o Sistema retorna a Mensagem de Violation Primary Key(Violação de Chave Primária).
Preparando o Ambiente
Para começarmos a brincadeira, vamos montar um simples ambiente, criando um novo Banco de Dados, denominado: MySchool.
Após a criação do Banco de Dados, o próximo é criar a tabela que vamos utilizar como recurso para vincular nosso Trigger DML Instead Of, vamos chamar esta Table de TBStudents, conforme o Bloco de Código, denominado Código 1, apresentado abaixo:
— Código 1 – Criação da Table TBStudents –
Create Table TBStudents
(Id Int Primary Key,
Name VarChar(60),
Status Char(1) Default ‘A’)
Go
A TBStudents será a base para trabalharmos com a nossa massa de dados, bem como, para implementarmos o Trigger DML Instead OF, neste caso, vamos realizar a inserção da massa de dados, apresentada no Bloco de Código, denominado Código 2, apresentado a seguir:
— Código 2 – Inserindo a Massa de Dados na Table TBStudents –
Insert Into TBStudents (Id, Name, Status)
Values (1, ‘Pedro LeroLero Junior’,’A’),
(2, ‘Eduardo LeroLero’,’A’),
(3, ‘Fernanda LeroLero’,’A’),
(4, ‘João Pedro LeroLero’,’A’),
(5, ‘Maria Luíza LeroLero’,’I’),
(6, ‘Joaquim LeroLero’,’I’),
(7, ‘Pedro LeroLero’,’I’),
(8, ‘Dora LeroLero’,’I’),
(9, ‘Marcio LeroLero’,’A’)
Go
Insert Into TBStudents (Name, Status)
Values (10, ‘José ShortCut’,’I’),
(11, ‘Cjico ShortCut’,’A’),
(12, ‘Fernando ShortCut’,’A’),
(13, ‘Jorge ShortCut’,’F’),
(14, ‘Kurumbu YooLong’,’E’),
(15, ‘Kurumbu Kim YooLong’,’E’),
(16, ‘Kum Shi Hum’,’G’),
(17, ‘Long Long Long’,’G’),
(18, ‘Cut Chu Cjut’,’M’)
Go
A massa de dados foi criada e inserida na Tabela TBStudents, mas até o presente momento não fizemos uso do Trigger. Você pode notar que durante o processo de inserção dos dados, foram informados manualmente os Ids de cada estudante, como destacado anteriormente o Sistema utilizado pela Escola esta apresentando algumas falhas, dentre elas, no processo de cadastramento de novos alunos.
Dando continuidade vamos criar uma nova Table denominado TBStudentsLog, que será utilizada em conjunto com o nosso Trigger, como um recurso para Auditoria e Histórico de Transações, para isso vamos utilizar o Bloco de Código, denominado Código 3, apresentado a seguir:
— Código 3 – Criando a Table TBStudentsLog —
Create Table TBStudentsLog
(CodEvent Int Primary Key Identity(1,1),
Description Varchar(60),
CodeError Int,
Data Varchar(200),
IDCorreto Int)
Go
Observe que esta table será capaz de armazenar o Código do Evento, a Descrição, o Código do Erro e os dados que estavam sendo manipulados no momento que a transação DML estava sendo processada.
Utilizando o Trigger DML Instead OF
Vamos imaginar que a pessoa responsável em realizar o Cadastro do próximo aluno não anotou o número do último ID inserido na Ficha de Alunos, no nosso caso, corresponde ao número 18. Com isso, o próximo número obrigatoriamente será o número 19, mas como é de seu conhecimento este número não foi anotado, desta forma, ao executar o Código 4, o SQL Server vai nos retornar a mensagem de erro:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK__TBStuden__3214EC0760FC61CA’. Cannot insert duplicate key in object ‘dbo.TBStudents’.
The statement has been terminated.
Justamente este o próximo passo que vamos simular aqui e posteriormente com o nosso Trigger, capturar o código das informações sobre o erro que o Microsoft SQL Server esta apresentando, bem como, e quais os dados que estão sendo afetados. Para tal situação, vamos utilizar o Bloco de Código, denominado Código 4, apresentado abaixo:
— Código 4 – Simulando a Falha no Cadastro de um novo aluno – Violation Primary Key —
Insert Into TBStudents (Id, Name, Status)
Values (18, ‘Fuji Hum Shi’, ‘P’)
Go
Esta é a nossa situação neste momento, agora pense se todas as vezes que um novo aluno for ser cadastrado e ocorra uma violação de Chave Primária, não sabermos os dados que estão sendo manipulados!
É nesta situação que o Trigger DML Instead OF pode ser utilizado com um recurso chave, algo que poderá ajudar em muito as atividades de manutenção do sistema nas funcionalidades que ele esta apresentando falha. Sendo assim, o nosso próximo passo é realizarmos a criação do nosso Trigger denominado MyTriggerInsteadOF, com base, no Bloco de Código 5, apresentado abaixo:
— Código 5 – Criando a Trigger DML Instead Of – MyTriggerInsteadOF –
Create Trigger MyTriggerInsteadOf
ON TBStudents
INSTEAD OF INSERT
AS
Begin
Set NoCount On
IF EXISTS (SELECT a.id FROM inserted AS a INNER JOIN TBStudents AS b ON a.id = b.id)
BEGIN
RAISERROR(50010, 16, 1, ‘Registro já existe!’)
Declare @Data Varchar(200)
Set @Data = (Select CONVERT(Varchar(5), Id) + ‘ – ‘ + [Name] + ‘ – ‘ + [Status] From inserted)
Insert Into TBStudentsLog(Description, CodeError, Data, IDCorreto)
Values(‘Violation Primary Key’,2627,@Data, (Select MAX(ID)+1 from TBStudents))
End
End
Logicamente se fizermos uma análise poderíamos tornar o código mais automatizado e até mesmo inteligente, mas por questões de funcionalidades e por estar trabalhando com o Trigger DML Instead Of, onde o trigger é processado antes da confirmação da transação que o disparou, fica um pouco mais complicado obter alguns dados como mensagem de erro, código de erro, entre outros elementos que poderiam ser utilizados através de Funções de Tratamento de Erro.
Com este Trigger implementado, todas as ocorrências que ser realizem ao Código de Erro 2627 que representa a Violation Primary Key, serão armazenadas na Table TBStudentsLog, regristando os dados que estavam envolvidos na Inserção incluindo o ID que deveria ser utilizado para inserir os dados.
Outro detalhe importante, com este tipo de implementação o SQL Server não irá levantar nenhuma mensagem de erro o que poderá evitar qualquer tipo de situação desagradável para os usuário e sim uma mensagem de erro mais amigável.
Desta forma vou encerrar mais este post, acredito que de forma muito simples e prática consegui apresentar e ilustrar uma forma que podemos utilizar este tipo de Trigger.
Mais uma vez obrigado, por sua visita.
Nos encontramos em breve.
Até mais.
Junior Galvão - MVP - Data Platform 