Neste artigo, iremos rever gatilhos no SQL Server, diferentes tipos de eventos de gatilho, ordem de gatilho e NÃO PARA REPLICAÇÃO em gatilhos. Um disparo é um objecto de base de dados que corre automaticamente quando ocorre um evento. Existem três tipos diferentes de eventos.
- Eventos DML
- Eventos DDL
- Evento LOGON – O gatilho de logon é disparado quando ocorre um evento LOGON, ou seja quando uma sessão de utilizador está a ser estabelecida
Disparadores DML no SQL Server
Disparadores DML no SQL Server são disparados quando ocorre um evento DML. ou seja, quando os dados são inseridos/actualizados/apagados na tabela por um utilizador.
Accionadores de criação para um evento DML
Vamos criar algumas tabelas de amostra e accionadores no SQL Server.
|
1
2
3
|
CREATE TABLE Locations (LocationID int, LocName varchar(100))
CREATE TABLE LocationHist (LocationID int, ModifiedDate DATETIME)
|
Podemos criar um gatilho DML para um evento específico ou múltiplos eventos. Os gatilhos no SQL Server(DML) disparam sobre eventos independentemente do número de filas afectadas.
Abaixo está a sintaxe de amostra para criar um gatilho DML para um evento de actualização.
|
1
2
3
4
5
6
7
8
9
10
11
|
CREATE TRIGGER TR_UPD_Locations ON Locations
FOR UPDATE
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHist
SELECT LocationID
,getdate()
FROM inserido
END
|
Estes gatilhos são criados ao nível da tabela. Após a criação bem sucedida do trigger, podemos ver os gatilhos navegando para a pasta Triggers a nível da tabela. Consulte a imagem abaixo.
Em vez de triggers no SQL Server
Estes triggers são disparados antes do evento DML e os dados reais não são modificados na tabela.
Por exemplo, se especificarmos um disparo em vez de disparo para apagar numa tabela, quando a declaração de apagar é emitida contra a tabela, o disparo em vez de disparo é disparado e o bloco T-SQL dentro dos disparadores no SQL Server é executado, mas o apagamento real não acontece.
T-SQL Syntax for creating an instead of trigger
|
1
2
3
4
5
6
|
- Se houver múltiplos gatilhos juntamente com em vez de gatilho na tabela, em vez de gatilho é disparado primeiro na ordem
- INSTEAD of triggers can be created on views
- podemos definir apenas um em vez de gatilho por INSERT, UPDATE, ou declaração DELETE numa tabela ou visualização
Activar e desactivar gatilhos DML numa tabela
Navegar para a pasta de gatilhos ao nível da tabela, seleccionar o gatilho, clicar com o botão direito do rato no gatilho e clicar em Activar/Desactivar para Activar ou desactivar o gatilho usando SSMS.
Desactivar o gatilho específico do SQL Server numa tabela utilizando o T-SQL.
|
1
|
TRIGGER TR_UPD_Locations2 on Locations
|
Enabling specific trigger on the table using T-SQL.
|
1
|
ENABLE TRIGGER TR_UPD_Locations2 on Locations
|
Para activar todos os gatilhos sobre uma mesa, utilização abaixo da sintaxe.
|
1
|
ENABLE TRIGGER ALL ON Locations
|
Para desactivar todos os gatilhos sobre uma mesa, utilização abaixo da sintaxe. Esta afirmação não é suportada se a tabela fizer parte da replicação da fusão.
|
1
|
DISABLE TRIGGER ALL ON Locations
|
Droping a trigger on a table.
Para largar um gatilho DML sobre a mesa utilizando o estúdio de gestão do SQL Server, navegar para a pasta Triggers debaixo da mesa. Seleccione a tabela que pretende largar, clique com o botão direito do rato sobre o gatilho e clique em Apagar. Clique em Ok.
T-SQL para largar um trigger sobre a tabela.
|
1
|
DROP TRIGGER TRL_UPD_Locations2
|
Deixar cair uma tabela irá largar todos os gatilhos do SQL Server na mesa juntamente com a tabela.
DDL Triggers
Os gatilhos DDL no SQL Server são disparados em eventos DDL. ou seja, contra declarações de criar, alterar e largar, etc. Estes gatilhos são criados ao nível da base de dados ou ao nível do servidor com base no tipo de evento DDL.
Estes gatilhos são úteis nos casos abaixo.
- Prevenir alterações ao esquema da base de dados
- Auditoria de alterações ao esquema da base de dados
- Para responder a uma alteração no esquema da base de dados
Criar um gatilho DDL
Abaixo está a sintaxe de exemplo para criar um gatilho DDL para o evento ALTER TABLE numa base de dados que regista todas as declarações de alteração contra a tabela. Pode escrever o seu código personalizado para acompanhar ou auditar as alterações do esquema usando EVENTDATA().
|
1
2
3
4
5
6
7
8
9
10
11
/td> |
CREATE TABLE TableSchemaChanges (ChangeEvent xml, DataHora modificada)
CREATE TRIGGER TRIGGER TR_ALTERTABLE ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
INSERT INTO TableSchemaChanges
SELECT EVENTDATA(),GETDATE()
END
|
É possível especificar um grupo de eventos que consiste em diferentes eventos DDL. Se especificarmos um grupo de eventos ao criar um disparo DDL, o disparo é disparado quando ocorre um evento DDL no grupo.
Por exemplo, se quisermos criar um disparo para todos os eventos DDL ao nível da base de dados, podemos apenas especificar o grupo de eventos DDL_DATABASE_LEVEL_EVENTS como se mostra na imagem abaixo.
Para ver triggers de nível de base de dados, inicie sessão no servidor utilizando o SQL Server management studio e navegue até à base de dados. Expanda a base de dados e navegue para Programabilidade -> Disparadores da Base de Dados.
Para ver triggers ao nível do servidor, inicie sessão no servidor usando SSMS e navegue até à pasta Server Objects e depois Triggers.
Activar e desactivar gatilhos DDL
Usar abaixo a sintaxe T-SQL para desactivar ou activar o gatilho DDL ao nível da base de dados.
|
1
2
3
4
5
|
TRIGGER TRIGGER TR_DATABASEVENTES ENAVEIS SOBRE BASE DE DADOS
GO
TRIGGER TRIGGER TR_DATABASEVENTES DISPONÍVEIS SOBRE BASE DE DADOS
GO
|
Utilização abaixo de T-Sintaxe SQL para largar um gatilho DDL que é criado ao nível da base de dados.
|
1
|
DROP TRIGGER TR_DATABASEEVENTS NA BASE DE DADOS
|
Acionadores deLOGON no SQL Server
Estes acionadores no SQL Server disparam em resposta a um evento LOGON. LOGON dispara após autenticação bem sucedida e antes de estabelecer a sessão do utilizador.
Os disparadores de LOGON são criados ao nível do servidor e são úteis abaixo dos casos.
- Para auditar a actividade de login
- Para controlar a actividade de login
Criar disparadores de LOGON
Pode usar EVENTDATA() e escrever o seu código personalizado para seguir ou controlar as ligações. Aqui estou a criar gatilhos simples no SQL Server para o evento LOGON. Abaixo está o exemplo de sintaxe para criar um gatilho de LOGON.
|
1
2
3
4
5
6
7
8
9
10
>/td> |
CREATE TABLE LoginActivity (LOGONEvent XML ,Logintimetimetime)
CRIAR TRIGGER EM TODO O SERVIDOR
PARA LOGON AS
BEGIN
INSERIR PARA AACTIVIDADE DE Login
SELECT EVENTDATA()
,GETDATE()
END
|
Devemos ser cautelosos ao criar estes gatilhos, pois o login pode falhar se a execução do gatilho falhar ou se não tiver acesso aos objectos referenciados no gatilho LOGON. Nesses casos, o único membro da função de administrador do sistema pode ligar-se ao servidor utilizando uma ligação dedicada de administrador. Assim, é sempre melhor activar a ligação de administrador dedicado ao utilizar estes gatilhos.
Activar e desactivar gatilhos de LOGON
Utilizar abaixo a sintaxe T-SQL para desactivar ou activar o gatilho de LOGON.
|
1
2
3
4
5
|
ENABLE TRIGGER track_logins ON ALL SERVER
GO
DISABLE TRIGGER track_logins ON ALL SERVER
GO
|
Utilização abaixo de T-Sintaxe SQL para largar um gatilho de LOGON.
|
1
|
DROP TRIGGER track_logins ON ALL SERVER
|
Reversão directa
Reversão directa é um caso em que o gatilho do SQL Server na mesa é disparado e executa uma acção que, mais uma vez, dispara o mesmo gatilho.
Por exemplo, por favor consultar abaixo o gatilho de amostra para uma actualização que é directamente recursiva.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE .(
NULL,
(100) NULL,
DateUpdated datetime
) ON
GO
INSERIR VALORES DE LOCALIZAÇÃO(1,’Estrada de Richmond’, NULL)
CREATE TRIGGER TR_UPD_Locations ON Localizações
FOR UPDATE
AS
BEGIN
Actualização de Localizações DataActualizado =GETDATE()
END
|
A recorrência directa pode ser controlada por uma definição de base de dados RECURSIVE_TRIGGERS. Se a definição estiver ligada, então o gatilho acima dispara um erro.
Se a configuração da base de dados RECURSIVE_TRIGGERS estiver desligada, então o gatilho é disparado apenas uma vez e não faz loop.
Para alterar a configuração RECURSIVE_TRIGGERS usando SSMS, navegue até à base de dados, clique com o botão direito do rato sobre a base de dados e seleccione Properties. Clique em Options e altere a configuração para a opção que pretende.
Para definir o RECURSIVE_TRIGGERS OFF usando T-SQL, use a declaração abaixo e substitua o nome da base de dados pelo nome da sua base de dados.
|
1
2
|
ALTER DATABASE SET RECURSIVE_TRIGGERS OFF COM NO_WAIT
GO
|
Para colocar os RECURSIVE_TRIGGERS ON usando T-SQL, utilizar a declaração abaixo e substituir o nome da base de dados pelo nome da sua base de dados.
|
1
2
|
ALTER DATABASE SET RECURSIVE_TRIGGERS ON WITH NO_WAIT
GO
|
Recursion Indirect
Este é um caso em que um gatilho é disparado e invoca outro gatilho do mesmo tipo.
Abaixo está o gatilho de amostra para a recursividade indirecta.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
CREATE TABLE Temp1 (id int)
GO
INSERT INTO Valores Temp1 (1),(2)
GO
CREATE TABELA Temp2 (id int)
GO
INSERIR EM Temp2 valores (1),(2)
GO
CREATE TRIGGER TR_Temp1 em Temp1
FOR UPDATE
AS
BEGIN
UPDATE TEMP2 set ID =’5′ onde id in (seleccionar id de inserido)
END
GO
CREATE TRIGGER TR_Temp2 on Temp2
FOR UPDATE
AS
BEGIN
UPDATE Temp1 set ID =’5′ onde id in (seleccionar id de inserido)
END
|
Agora quando actualizamos um valor na tabela Temp1, o gatilho TR_Temp1 é disparado o que actualiza a tabela Temp2. TR_Temp2 é disparada e actualiza a tabela Temp1 que faz com que TR_Temp1 volte a disparar.
Este comportamento pode ser controlado através da definição de gatilhos aninhados desligados.
|
1
2
|
EXEC sp_configure ‘nested triggers’, 0 ;
GO
|
SQL Server trigger order
SQL Server permite múltiplos gatilhos na tabela para o mesmo evento e não existe uma ordem definida de execução destes gatilhos.
Podemos definir a ordem de disparo para primeiro ou último usando o procedimento sp_settriggerorder. Pode haver apenas um primeiro ou último gatilho para cada instrução numa tabela.
Abaixo está a sintaxe de amostra para definir a ordem de gatilho para primeiro para a instrução INSERT.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
>
CREATE TABLE TriggerOrderTest (id int)
GO
CREATE TRIGGER TR_1 ON TriggerOrderTest
FOR INSERT
as
BEGIN
PRINT ‘First Trigger’
END
GO
CREATE TRIGGER TR_2 ON TriggerOrderTest
FOR INSERT
as
BEGIN
PRINT ‘Second Trigger’
END
GO
CREATE TRIGGER TR_3 ON TriggerOrderTest
PARA INSERIR
as
BEGIN
PRINT ‘Third Trigger’
END>/div>
GO
sp_settriggerorder @triggerorder =’TR_3′
, @order = ‘FIRST’
, @stmttype = ‘INSERT’
|
Agora, quando os dados são inseridos na tabela “TriggerOrderTest” ocorre o evento INSERT e o gatilho TR_3 dispara primeiro.
No caso de gatilho DDL devemos especificar o parâmetro namespace que é o âmbito do gatilho do SQL Server no procedimento armazenado sp_settriggerorder.
Abaixo está a sintaxe de amostra para definir a ordem de gatilho DDL.
|
1
2
3
4
|
sp_settriggerorder @triggerorder =’DDL_3′
, @order = ‘FIRST’
, @stmttype = ‘ALTER_TABLE’
, @namespace = ‘DATABASE’
|
NÃO PARA REPLICAÇÃO
NÃO PARA REPLICAÇÃO indica que o gatilho não deve disparar quando o agente de replicação sincroniza as alterações de dados para o assinante.
Por exemplo, se estiver a replicar tanto Localizações como LocationHist. Agora, quando se actualiza um registo em Location, o gatilho é disparado, insere-se o registo na tabela de histórico. Quando estas alterações se sincronizam com outro extremo (subscritores), não há necessidade de disparar novamente o gatilho. Assim, se marcarmos o gatilho para “NÃO PARA REPLICAÇÃO”, o gatilho não dispara quando o agente de replicação sincroniza as alterações e dispara apenas para as alterações de dados feitas pelo utilizador.
Abaixo está a sintaxe de amostra para criar um gatilho no SQL Server com não para replicação.
|
1
2
3
4
5
6
7
8
9
10
11
|
CREATE TRIGGER TR_UPD_Locations ON Locations
FOR UPDATE
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHist
SELECT LocationID
,getdate()
FROM inserido
END
|
Se quiser que os gatilhos no SQL Server sejam disparados quando o agente de replicação sincronizar os dados para outro fim, basta criar o gatilho sem especificar “NÃO PARA REPLICAÇÃO”.
