Hey Guys!
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
|
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, Name 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:
|
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 |
Result:
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:
|
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 |
Result:
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:
|
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!
Portuguese 
English
Muito bom….
Excelente artigo, me ajudou muito!
Não só pelo ensinamento do conteúdo, mas pelo ensinamento da lógica que pode ser aplicada em incalculáveis de cenários diferentes.
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
Muito bom o Artigo.
Parabens!