SQL Server – Trigger para prevenir e impedir alterações em tabelas

Visualizações: 2.499
Esse post é a parte 12 de 15 da série Segurança e Auditoria
Tempo de Leitura: 3 minutos

Olá pessoal!

Uma necessidade corriqueira de DBA’s é evitar acessos e operações indevidas ou equivocadas, que até mesmo o próprio DBA pode acabar tendo um descuido e executar uma operação errada. A minha ideia nesse post, é demonstrar algumas soluções utilizando triggers.

Criação da tabela de testes

Transact-SQL
1
2
3
4
5
6
7
8
9
10
IF (OBJECT_ID('dbo.Teste_Trigger') IS NOT NULL) DROP TABLE dbo.Teste_Trigger
CREATE TABLE dbo.Teste_Trigger (
    Id INT IDENTITY(1, 1) NOT NULL,
    Nome VARCHAR(100) NOT NULL
)
 
INSERT INTO dbo.Teste_Trigger
VALUES ( 'Dirceu Resende' ), ( 'Teste' ), ( 'Teste 2')
 
SELECT * FROM dbo.Teste_Trigger

Evitar comandos DML na tabela (Tabela somente leitura)

Caso você não queira permitir operações de DML (INSERT, UPDATE e DELETE) na sua tabela, transformando-a assim, em uma tabela somente leitura (read-only table), basta criar a trigger abaixo:

Transact-SQL
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
31
32
IF (OBJECT_ID('[dbo].[trgBloqueia_Dml_Teste]') IS NOT NULL) DROP TRIGGER [dbo].[trgBloqueia_Dml_Teste]
GO
  
CREATE TRIGGER [dbo].[trgBloqueia_Dml_Teste] ON [dbo].[Teste_Trigger]
FOR INSERT, UPDATE, DELETE AS
BEGIN
  
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR ('Operações de INSERT não são permitidas na tabela "Teste_Trigger"', 15, 1);
        RETURN;
    END
  
  
    IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR ('Operações de DELETE não são permitidas na tabela "Teste_Trigger"', 15, 1);
        RETURN;
    END
  
  
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR ('Operações de UPDATE não são permitidas na tabela "Teste_Trigger"', 15, 1);
        RETURN;
    END
  
END;
GO

Resultado:

Evitar DELETE ou UPDATE sem where

Um erro muito comum no dia a dia do DBA, é quando algum analista ou desenvolvedor envia um script de UPDATE ou DELETE para o DBA executar e eles esquecem de colocar a cláusula WHERE. Imaginem o estrago que isso pode gerar no banco de dados.. Uma forma de evitar isso, é utilizando plugins e add-ons, como Redgate SQL Prompt e ApexSQL Complete, que alertam o DBA quando isso vai ocorrer.

Uma outra forma (e mais confiável), é criar uma trigger para isso:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
IF (OBJECT_ID('[dbo].[trgEvita_Acidentes]') IS NOT NULL) DROP TRIGGER [dbo].[trgEvita_Acidentes]
GO
 
CREATE TRIGGER [dbo].[trgEvita_Acidentes] ON [dbo].[Teste_Trigger]
FOR UPDATE, DELETE AS
BEGIN
  
    DECLARE
        @Linhas_Alteradas INT = @@ROWCOUNT,
        @Linhas_Tabela INT = (SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE [object_id] = OBJECT_ID('Teste_Trigger') AND (index_id <= 1))
 
    IF (@Linhas_Alteradas >= @Linhas_Tabela)
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR ('Operações de DELETE e/ou UPDATE sem cláusula WHERE não são permitidas na tabela "Teste_Trigger"', 15, 1);
        RETURN;
    END
  
  
END;
GO

Resultado:

Permitir apenas 1 registro alterado por vez na tabela

Uma outra necessidade que pode ocorrer em algumas tabelas, é exigir que apenas 1 registro da tabela seja alterado por vez através de uma operação de DELETE/UPDATE:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
IF (OBJECT_ID('[dbo].[trgEvita_Dml_Muitos_Registros]') IS NOT NULL) DROP TRIGGER [dbo].[trgEvita_Dml_Muitos_Registros]
GO
 
CREATE TRIGGER [dbo].[trgEvita_Dml_Muitos_Registros] ON [dbo].[Teste_Trigger]
FOR UPDATE, DELETE AS
BEGIN
  
    DECLARE
        @Linhas_Alteradas INT = @@ROWCOUNT,
        @MsgErro VARCHAR(MAX)
 
    IF (@Linhas_Alteradas > 1)
    BEGIN
        ROLLBACK TRANSACTION;
        SET @MsgErro = 'Operações de DELETE e/ou UPDATE só podem atualizar 1 registro por vez na tabela "Teste_Trigger", e você tentou atualizar ' + CAST(@Linhas_Alteradas AS VARCHAR(50))
        RAISERROR (@MsgErro, 15, 1);
        RETURN;
    END
  
  
END;
GO

Ao tentar atualizar mais de um registro, a trigger irá barrar a operação:

Apresentadas as soluções acima, espero ter tirado suas dúvidas sobre restrição de DML no SQL Server utilizando Triggers. Esse é um recurso bem antigo do SGBD, odiado por uns, amado por outros e que tem sim, sua utilidade quando bem aplicado e seu uso é justificado.

Espero que tenham gostado desse post e caso tenham dúvidas, deixem aqui nos comentários.
Um abraço e até o próximo post!

Tags:

You may also like...

2 Responses

  1. Gabriel Ahumada disse:
    22 de novembro de 2018 às 16:06

    Olá Dirceu:

    Eu acho que o seu artigo é muito bom, desculpe meu português, sou do Chile. Como você pode evitar a modificação de um ou mais registros em uma tabela, mas com a condição de que o campo “userid” seja igual a XXX. Você poderia me escrever para [email protected]? Muito obrigado

    Responder
  2. Leonardo Palmieri disse:
    5 de fevereiro de 2018 às 09:38

    Muito bom o Artigo.
    Parabens!

    Responder

Deixe uma resposta