- Audit on 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 audit and control of logins (Logon Trigger)
- Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable
- Using SQL Server Standard 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 to 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 more than 70 security items to validate your database.
- SQL Server - How to know the date of a user's last login
- SQL Server - How to Avoid and Protect Against Ransomware Attacks Like WannaCry on Your Database Server
- SQL Server – Beware of server role securityadmin! Using privilege elevation to become sysadmin
- SQL Server - How to Avoid SQL Injection? Stop using Dynamic Query as EXEC (@Query). Now.
- SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change, and Login Lockout after N attempts
Hello everybody!
A common necessity for DBA's is to avoid improper or mistaken access and operations, which even the DBA itself may end up being careless and perform a wrong operation. My idea in this post, is to demonstrate some solutions using triggers.
Test table creation
|
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 |
Avoid DML Commands in Table (Read-Only Table)
If you don't want to allow DML operations (INSERT, UPDATE, and DELETE) on your table, making it a read-only table, just create the trigger below:
|
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:
Avoid DELETE or UPDATE without where
A very common mistake in everyday DBA is when some analyst or developer submits an UPDATE or DELETE script for DBA to execute and they forget to put the WHERE clause. Just imagine the damage this can cause to the database. One way to avoid this is by using plugins and add-ons, such as Redgate SQL Prompt and ApexSQL Complete, which alert the DBA when this will occur.
Another (and more reliable) way is to trigger this:
|
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:
Only allow 1 record changed at a time in table
Another need that may occur in some tables is to require that only 1 table record be changed at a time through a DELETE / UPDATE operation:
|
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 |
When trying to update more than one record, the trigger will bar the operation:
Presenting the above solutions, I hope I have answered your questions about DML restriction in SQL Server using Triggers. This is a very old feature of the DBMS, hated by some, loved by others and that has its usefulness when well applied and its use is justified.
I hope you enjoyed this post and if you have questions, leave it here in the comments.
Regards and see you next post!
Very good….
Excellent article, helped me a lot!
Not only for teaching the content, but for teaching logic that can be applied in countless different scenarios.
Hello Dirceu:
I think your article is very good, sorry my Portuguese, I'm from Chile. How can you avoid modifying one or more records in a table, but with the proviso that the “userid” field is equal to XXX. Could you write me to [email protected]? Thank you very much
Very good article.
Parabens!