Trigger traduz aproximadamente para vietnamita como gatilho, o que significa que é como um gatilho e será ativado quando houver uma ação de puxar o gatilho.

Em SQL, um Trigger é um tipo de procedimento armazenado que é executado ao tentar modificar dados na tabela em que o trigger foi criado. Ao contrário dos procedimentos armazenados do sistema regular, os gatilhos não podem ser executados diretamente, nem podem passar ou receber parâmetros.

Cada tabela normalmente terá 3 operações que alteram os dados: UPDATE, INSERT, DELETE. E, às vezes, para cada ação, teremos restrições na tabela para ajudar a preservar os dados, agora usar gatilhos é uma boa solução.

Tipos de gatilhos

Os gatilhos serão disparados automaticamente quando um evento for encontrado em uma tabela ou exibição. As linguagens de eventos são divididas em dois tipos, eventos DML e DDL. Os acionadores associados a eventos DML são chamados de acionadores DML, também chamados acionadores DDL.

Acionadores DML

Os gatilhos DML são executados na conclusão de um evento DML ou em nome de um evento DML. Esses gatilhos garantem a integridade referencial por meio de mudanças em cascata nas tabelas relacionadas quando uma linha é modificada.

Os gatilhos DML vêm em 3 tipos principais:

• INSERIR gatilho
• ATUALIZAR gatilho
• EXCLUIR gatilho

Introdução às tabelas inseridas e excluídas

A instrução SQL nos gatilhos DML usa dois tipos especiais de tabelas para modificar o banco de dados. Quando os dados são adicionados, atualizados ou excluídos, o SQL Server cria e gerencia essas tabelas automaticamente, a área de transferência armazena os dados originais e os dados modificados da seguinte maneira:

Tabela inserida

A tabela inserida armazena cópias de linhas aplicadas por meio de instruções INSERT e UPDATE. Durante a inserção ou atualização, novas linhas serão adicionadas a ambas as tabelas, tabela inserida e tabela de gatilho. A linha na tabela inserida é uma cópia da nova linha na tabela de gatilhos.

Tabela excluída

A tabela Deleted armazena uma cópia das linhas aplicadas por meio das instruções DELETE e UPDATE. Durante a execução de DELETE ou UPDATE, a linha será excluída da tabela de gatilhos e movida para a tabela excluída.

Nota: A tabela inserida e a tabela excluída são temporárias, para armazenar memória e verificar o efeito de determinadas modificações de dados e definir condições para ações de disparo DML. O SQL Server 2019 não permite referências de coluna de texto, nxtext ou imagem em tabelas inseridas e excluídas para gatilhos AFTER .

Inserir gatilhos

Um gatilho INSERT é acionado quando um novo registro é inserido na tabela. O gatilho INSERT garante que a entrada seja satisfeita, mas a restrição é definida na tabela.

Quando o usuário insere um registro na tabela, o gatilho INSERT salva uma cópia desses registros na tabela inserida. Em seguida, verifica se o valor moiws satisfaz as restrições da tabela.

Se o registro for válido, o gatilho INSERT adicionará a linha à tabela, caso contrário, exibirá uma mensagem de erro. Um gatilho INSERT é criado por meio da palavra-chave INSERT nas instruções CREATE TRIGGER e ALTER TRIGGER.

Sintaxe INSERT|UPDATE|DELETE trigger:

 CREATE TRIGGER [schema_name.]trigger_name ON [schema_name.]table_name [WITH ENCRYPTION] [FOR INSERT] AS [IF UPDATE (column_name)] [{AND|OR} UPDATE (colum_name)] AS BEGIN <sql_statement> END;

Por exemplo:

Passo 1 – crie 2 tabelas de acordo com as seguintes informações:

 CREATE TABLE Locations (LocationID int, LocName varchar(100)); CREATE TABLE LocationHistory (LocationID int, ModifedDate DATETIME);

Etapa 2 – crie um gatilho INSERT chamado TRIGGER_INSERT_Locations para a tabela Locations.

 CREATE TRIGGER TRIGGER_INSERT_Locations ON Locations FOR INSERT NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHistory SELECT LocationID , getdate() FROM inserted END;

A próxima etapa teste a instrução do gatilho:

 insert into dbo.Locations values(1,'Ha Noi');

No gatilho acima, ao inserir dados de locais, os dados LocationHistory são adicionados com base nos dados de Locations.

Neste há uma nova palavra-chave Replication é uma tecnologia de replicação e distribuição de dados e é uma das soluções de recuperação de desastres disponíveis no SSMS que é muito útil para manter uma segunda ou cópia de backup. room of objects (Table, View, Stored Procedure) e base de dados. O significado NOT FOR REPLICATION não é usado para situações de replicação.

Atualizar acionadores

A instrução de gatilho UPDATE copia o registro original na tabela Deleted e copia o novo registro na tabela inserida quando o registro é atualizado. Em seguida, ele avalia o novo registro para determinar se o valor atende à restrição na tabela.

Se o novo valor for válido, o registro da tabela inserida será copiado para a tabela de trigger. No entanto, se o novo valor não for válido, uma mensagem de erro será impressa para o usuário. Assim, os registros originais são copiados da tabela Deleted de volta para a tabela de trigger.

Um gatilho UPDATE pode ser criado usando a palavra-chave UPDATE no comando CREATE TRIGGER ou ALTER TRIGGER.

Por exemplo:

 CREATE TRIGGER TRIGGER_UPDATE_Locations ON Locations FOR UPDATE NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHistory SELECT LocationID, getdate() FROM inserted END; GO update Locations SET LocName = 'Ho Chi Minh' where [LocName] = 'Ha Noi';

Depois de executar o comando de criação de gatilho acima e atualizar os dados para a tabela Locations, a tabela LocationHistory é adicionada com novos dados quando os dados da tabela Locations são atualizados.

Excluir gatilho

O gatilho de exclusão é criado para impedir que os usuários excluam registros específicos da tabela

A seguinte sequência de execução ocorre quando um usuário tenta excluir um registro:

• O registro é excluído da tabela de gatilhos e adicionado à tabela Excluída .
• Ele verificará a correspondência de restrição ao excluir dados
• Se houver uma restrição no registro que impeça a exclusão, o gatilho DELETE exibirá uma mensagem de erro
• Os registros excluídos armazenados na tabela Deleted serão copiados de volta para a tabela de trigger.

Por exemplo:

 CREATE TRIGGER TRIGGER_DELETE_Locations ON Locations FOR DELETE NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHistory SELECT LocationID, getdate() FROM deleted END; GO DELETE FROM dbo.Locations WHERE LocName = 'Ho Chi minh';

Após executar a exclusão, o gatilho adicionará novos registros à tabela LocationsHistory.

DEPOIS dos gatilhos

Um gatilho AFTER é executado após a conclusão da operação INSERT,UPDATE,DELETE. Uma tabela pode ter vários gatilhos AFTER definidos para cada operação INSERT, UPDATE, DELETE. Se vários gatilhos forem criados na mesma tabela, o próprio usuário deverá definir o método de execução do gatilho. Um gatilho AFTER é executado quando as restrições de verificação são concluídas, portanto, o gatilho será executado após a criação das tabelas Inserted e Deleted .

Sintaxe:

 CREATE TRIGGER <trigger_name> ON <table_name> [WITH ENCRYPTION] [FOR | AFTER] { [INSERT] [,] [UPDATE] [,] [DELETE] } AS BEGIN <sql_statement> END;

Por exemplo:

 CREATE TRIGGER AFTER_INSERT_Locations ON Locations AFTER INSERT AS BEGIN INSERT INTO LocationHistory SELECT LocationID ,getdate() FROM inserted END;

Após a execução, ele irá inserir na tabela LocationHistory após inserir com sucesso Locations, a diferença no trigger after e trigger on é apenas no tempo de execução do trigger.

EM VEZ DE Gatilhos

O gatilho INSTEAD OF será executado em vez da operação INSERT,UPDATE,DELETE quando usado. Uma tabela ou visualização só pode ter um gatilho INSTEAD OF definido para cada operação INSERT , UPDATE , DELETE .

O gatilho INSTEAD OF será executado antes que a verificação de restrição seja executada na tabela, este gatilho é executado após a criação das tabelas inseridas e excluídas .

Por exemplo:

 CREATE TRIGGER INSTEAD_OF_DELETE_Locations ON Locations INSTEAD OF DELETE AS BEGIN SELECT 'example instead trigger' as Message END; GO DELETE FROM dbo.Locations WHERE LocName='Ha Noi'

Quando a instrução delete é executada, o gatilho INSTEAD OF é acionado e o bloco dentro do gatilho é executado, e a ação delete não ocorre.

Ordem de execução dos gatilhos DML

O SQL Server permite que o usuário defina quais triggers AFTER devem ser executados primeiro e quais devem ser executados posteriormente. Todos os gatilhos AFTER quando chamados entre o primeiro e o último gatilho não têm uma ordem definida de execução.

Use o procedimento armazenado sp_settriggerorder para definir a ordem da porta do acionador DML,

A sintaxe para determinar a ordem de execução do gatilho AFTER DML:

 sp_settriggerorder [@triggername=] '[triggerschema.]triggername', [@order=]'value', [@stmmttype=]'statement_type'

Por exemplo:

 EXEC sp_settriggerorder @triggername = 'TRIGGER_DELETE_Locations', @order='FIRST', @stmttype='DELETE'

Veja a definição de DML Trigger

Para exibir a definição do gatilho por meio do procedimento armazenado sp_hepltext .
Sintaxe:

 sp_helptext '<trigger_name>';

Por exemplo:

 sp_helptext TRIGGER_DELETE_Locations

Acionador ALTER DML

Existem 2 maneiras de editar o gatilho:

• DROP e RECREATE disparam novamente com novo parâmetro
• Altere o gatilho com a sintaxe ALTER TRIGGER .

Sintaxe:

 ALTER TRIGGER <trigger_name> ON {<table_name>|<view_name>} [WITH ENCRYPTION] {FOR|ALTER|INSTEAD OF} { [INSERT] [,] [UPDATE] [,] [DELETE] } AS <sql_statement>

Por exemplo:

 ALTER TRIGGER TRIGGER_UPDATE_Locations ON Locations WITH ENCRYPTION FOR INSERT AS IF 'Ho Chi Minh' IN (SELECT LocName FROM inserted) BEGIN PRINT 'Location can not be updated' ROLLBACK TRANSACTION END;

SOLTAR Acionador DML

Sintaxe:

 DROP TRIGGER <DML_trigger_name> [,...n]

Por exemplo:

 DROP TRIGGER TRIGGER_UPDATE_Locations

Gatilhos DDL

Um gatilho DDL (Data Definition Language) executa um procedimento armazenado quando um evento DDL como CREATE,ALTER,DROP ocorre no banco de dados ou no servidor. Os gatilhos DDL só podem operar quando o evento DDL for concluído.
Os gatilhos DDL podem ser usados para evitar modificações no esquema do banco de dados.
O gatilho DDL pode chamar um evento ou exibir uma mensagem com base na mensagem do esquema. Os gatilhos DDL são definidos na camada do banco de dados ou na camada do servidor.

Sintaxe:

 CREATE TRIGGER <trigger_name> ON {ALL SERVER | DATABASE} [WITH ENCRYPTON] {FOR | AFTER} <event_type> } AS <sql_statement>

Por exemplo:

 CREATE TRIGGER Secure ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable trigger scure to drop or alter table' ROLLBACK;

Com esse código, um gatilho DDL é gerado para a instrução DROP TABLE ou ALTER TABLE.

Faixa de gatilho DDL

O gatilho DDDL é invocado pela instrução sql executada no banco de dados ou servidor atual. Por exemplo, um gatilho DDL é criado para a instrução CREATE TABLE que é executada no evento CREATE TABLE no banco de dados.
O gatilho DDL faz com que a instrução CREATE LOGIN seja executada no evento LOGIN no servidor.

O escopo de um gatilho DDL depende se o gatilho é executado para um evento de banco de dados ou um evento de servidor, os gatilhos são divididos em dois tipos, conforme a seguir:

Gatilhos DDL com escopo de banco de dados

O gatilho DDL no escopo do banco de dados é chamado pelo evento de edição do esquema do banco de dados. Esses gatilhos são armazenados no banco de dados e executados no evento DDL, exceto aqueles associados à área de transferência.

Gatilhos DDL com escopo de servidor

O gatilho DDL no escopo do servidor é chamado pelo evento DDL da camada do servidor. Esses gatilhos são armazenados no banco de dados mestre.

Acionador aninhado

Os gatilhos DDL e DML têm esse conceito quando um gatilho implementa uma ação que inicia outro gatilho. Os gatilhos DDL e DML podem ser aninhados em até 32 camadas. Suponha que se o gatilho edita uma tabela na qual existe outro gatilho, o segundo gatilho é inicializado, então chama o terceiro gatilho e assim por diante…

Se os gatilhos aninhados puderem ser executados, os gatilhos iniciarão sequencialmente como um loop infinito, se atingir a última camada de difusão, o gatilho terminará.

Gatilhos aninhados podem ser usados para lidar com funções como arquivamento ou backup de registros afetados por ações anteriores.

O usuário pode desabilitar gatilhos aninhados, configurando a opção de gatilhos aninhados por meio do procedimento armazenado sp_configure para 0 ou OFF. A configuração padrão permitirá gatilhos aninhados.

Por exemplo:

 GO CREATE TRIGGER Employee_Deletion ON HumanResources.Employee AFTER DELETE AS BEGIN PRINT 'Deletion will affect EmployeePayHistory table' DELETE FROM EmployeePayHistory WHERE BusinessEntityID IN (SELECT BusinessEntityID FROM deleted) END; GO CREATE TRIGGER Deletion_Comnfirmation ON HumanResources.EmployeePayHistory AFTER DELETE AS BEGIN PRINT 'Employe details successfully deleted from EmployeePayHistory table' END; DELETE FROM HumanResources.EmployeePayHistory WHERE BusinessEntityID=1

função ATUALIZAR()

A função UPDATE() retorna um booleano que determina se um UPDATE ou INSERT foi executado na exibição ou coluna da tabela.

A função UPDATE() pode ser usada em qualquer lugar dentro do corpo de um gatilho UPDATE ou INSERT para verificar se o gatilho deve realizar alguma ação.

Sintaxe:

 UPDATE (column);

Por exemplo:

 CREATE TRIGGER Accounting ON Production.TransactionHistory AFTER UPDATE AS IF (UPDATE(TransactionID) OR UPDATE(ProductID)) BEGIN RAISEERROR(5009,16,10) END; GO

Processe várias linhas (registros) em uma sessão

Quando um usuário escreve código para um gatilho DML, as instruções dependem do gatilho para acionar instruções únicas. Uma única instrução atualizará vários registros nos dados. Esse é um comportamento comum em gatilhos DELETE e UPDATE e, muitas vezes, as instruções afetarão vários registros. O comportamento do gatilho INSERT será menos comum, pois a instrução insert normalmente adiciona apenas 1 registro.

Quando um recurso de gatilho DML chama e recalcula automaticamente o resumo de valores de uma tabela e armazena os resultados em outra tabela, as considerações de várias linhas são importantes.

Por exemplo, considere a situação de armazenar um resultado de soma de inserção de uma única linha:

 USE AdventureWorks2019; GO CREATE TRIGGER PODetails ON Purchasing.PurchaseOrderDetail AFTER INSERT AS UPDATE PurchaseOrderHeader SET SubTotal = Subtotal + LineTotal FROM INSERTED WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID;

No código abaixo, o subtotal é calculado e armazenado para cada operação de comando de inserção de linha única.
O código a seguir será executado para situações de várias linhas ou de uma única linha:

 USE AdventureWorks2019; GO CREATE TRIGGER PODetailMultiple ON Purchasing.PurchaseOrderDetail AFTER INSERT AS UPDATE Purchasing.PurchaseOrderHeader SET Subtotal = SubTotal + (SELECT SUM(LineTotal) FROM inserted WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID) WHERE PurchaseOrderHeader.PurchaseOrderID IN (SELECT PurchaseOrderID FROM inserted);

Acionadores de LOGON

Esses gatilhos são acionados em resposta ao evento LOGON no SQL Server. Os gatilhos de logon acionam o procedimento armazenado no evento LOGON. Este evento é detectado quando uma sessão de usuário é estabelecida com uma instância do SQL Server. O acionador de logon ocorre após a conclusão da autenticação de logon, mas antes que uma sessão de usuário tenha sido realmente estabelecida. O gatilho de logon não é executado quando a validação falha.

Os gatilhos de logon são criados na camada do servidor e são úteis nos seguintes casos:

• Verifique a atividade de login
• Controlar ações de login

Por exemplo, crie uma tabela LoginActivity com as colunas LOGONEvent e Logintime criadas no adventureworks da seguinte forma:

 CREATE TABLE LoginActivity (LOGONEvent XML, Logintime datetime)

Crie um trigger para que quando o usuário efetuar login, ele insira o histórico de login na tabela LoginActivity:

 CREATE TRIGGER [track_login] ON ALL SERVER FOR LOGON AS BEGIN INSERT INTO LoginActivity SELECT EVENTDATA(), GETDATE() END;

Obs: muito cuidado ao utilizar o LOGON Trigger, pode causar uma situação onde o erro lógico TRIGGER não pode ser logado, deve-se abrir o DAC para conectar, portanto teste-o cuidadosamente antes de executar.

Desempenho do gatilho

Na prática, os gatilhos não têm custo, mas funcionam muito bem. No entanto, muitos problemas de desempenho podem ser causados pela lógica presente dentro do gatilho. Assumindo que um gatilho cria um ponteiro e itera em várias linhas, eles terão uma lentidão na execução.

Da mesma forma, considere a situação em que o gatilho executa várias instruções SQL impedindo tabelas separadas nas tabelas inseridas e excluídas. Ele retornará o resultado novamente e desacelerará a consulta mais do que não usar o gatilho.

Portanto, é melhor manter a lógica do gatilho simples e evitar o uso de ponteiros ao executar instruções em tabelas e várias tarefas que degradam o desempenho do sistema.