SQL Server - Trigger to prevent and prevent table changes

Views: 3.680
This post is the 12 part of 15 in the series. Security and Audit
Reading Time: 3 minutes

Hello everybody!

A common need for DBA's is to avoid improper or misleading access and operations, which even the DBA itself may end up with being careless and performing the wrong operation. My idea in this post is to demonstrate some solutions using triggers.

Test table creation

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:

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:

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:

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!