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

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:

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:

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:

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!