- Auditing in SQL Server (Server Audit)
- How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server
- How to create an Audit trigger to log object manipulation in SQL Server
- SQL Server - How to implement login auditing and control (Logon Trigger)
- Monitoring DDL and DCL operations using SQL Server's fn_trace_gettable
- Using the standard SQL Server trace to audit events (fn_trace_gettable)
- SQL Server – Permissions and privileges audit trigger at database and instance level (GRANT and REVOKE)
- SQL Server - How to monitor and audit data changes in tables using Change Data Capture (CDC)
- SQL Server 2016 - How to "time travel" using the Temporal Tables feature
- SQL Server - How to use auditing to map actual required permissions on a user
- SQL Server - Trigger to prevent and prevent changes in tables
- SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
- SQL Server - How to avoid brute force attacks on your database
- SQL Server – Security Checklist – An SP with over 70 security items to validate your database
- SQL Server - How to know the last login date of a user
- SQL Server - How to avoid and protect yourself from Ransomware attacks like WannaCry on your database server
- SQL Server - Watch out for the securityadmin server role! Using elevation of privileges to become sysadmin
- SQL Server – How to avoid SQL Injection? Stop using Dynamic Query like EXEC(@Query). Now.
- SQL Server - Understanding the risks of the TRUSTWORTHY property enabled on a database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change and Login Blocking after several Attempts
- SQL Server - How to create a login audit using instance logs
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!