Procedure For Preventing Accidental Update or Delete In a SQL Server Table

Overview

The users of SQL Server database needs to make sure that their data is protected from any kind of silly mistakes that could lead to a problematic situation. One of the error is accidental delete or update commands of all rows in Table of SQL Server. Performing DML operations like modify, delete, insert etc. through tested application are considered safe and reliable. This process also prevents an accidental update or delete of all rows in a table of SQL Server database. However, many tables need update or modification of certain values or deletion of some rows from time to time. While performing this operation, many situation may arise when user accidentally issue an DML command without giving any condition like WHERE clause which may in turn affects the entire table. Hence, we need a solution to prevent the accidental DML operations to occur.

Scenario

Let us take an example of accidental deletion of rows in SQL Server table. Assume that Austin who works for an organization that provides Lab equipment to customers. Due to some reason, he needs to remove an entry from the Sales Report Table as some other employee performed an erroneous insertion to the table. Austin first identifies the wrong entry made and performs delete operation. Unfortunately, he did not notice that he also deleted a part of T-SQL Delete Statement which to lead to loss of critical entries and may cause financial ruin. Though it can be restored using the last backup, it may be time consuming and the organization may need to stop some processes while restoring the lost data. The question is what can be done to avoid this kind of problematic situation.

How to Prevent Accidental Update or Delete Commands

One of the effective ways that can prevent accidental DML operations on SQL Server Table is to create a trigger on important table that contain crucial data. These triggers are also called as Prevention triggers. We can use triggers to prevent both accidental UPDATE or DELETE operations. A new trigger will be created and the conditions will be defined inside the trigger body. It will check the rowcount value and determine if any rows are affected by DELETE statement and raise error message accordingly. In the example, we will be using AdventureWorks Database and it table Purchasing.VendorContact.

Some ways to prevent the accidental DML operations are as follows:

-Prevention Trigger for Accidental Update Operation

No trigger has been declared earlier on the Purchasing.VendorContact table. Now, we will create an UPDATE trigger to prevent any kind of accidental update of all rows in the table.

In the body of trigger, we have used the sys.dm_db_partition_stats dynamic management view in order to find out the number of rows in the table. This feature was first introduced in SQL Server 2005.

Case 1: we will create UPDATE trigger for SQL Server 2005 and 2008.

Case 2: we will create UPDATE trigger for SQL Server 2000.

Since sys.dm_db_partition_stats is not supported by SQL Server 2000, it uses sysindexes that contains one row for each index and table in the database.

Case 3: It is for those tables where an update trigger has already been defined in the table. We will use some part of code that needs to be inserted inside the body of trigger as defined below:

If the user needs to update on all the rows of the table, he/she needs to disable the trigger or ALTER the trigger to prevent the logic inside the trigger body from executing.

-Prevention Trigger for Accidental Delete Operation

Here, we will create a DELETE trigger for the Purchasing.VendorContact Table in order to prevent any form of accidental or careless delete operation on the rows of the table.

We just need to ALTER the trigger and prevention logic is same as used earlier in case of Update operation. Similarly, the error message will be ‘Cannot delete all rows’.

Similarly, for SQL Server 2000 instances, we will use sysindexes that contains only one row for each index and table in the database instead of sys.dm_db_partition_stats as it was only introduced from SQL Server 2005.

For already created Delete trigger, we will need the codes pasted inside the body of trigger defined as below:

If we need to delete all the rows present in the table and no logged operations are permitted, we can use truncate table command that will remove all the rows from a table but will maintain its columns, indexes etc. Truncate command should not invoke the logic in the body of trigger.

Verification of functionality of both prevention triggers

(a) Verify the UPDATE trigger Function

If our UPDATE Trigger works, then it should only work for update statement, with the WHERE clause and it should not work if WHERE clause is not defined.

Here, we can see that Update Trigger prevents any other update in the rows of table except the update of value on required row.

The update operation is not successfully completed as the statement has been executed without WHERE clause and generates error. User can verify the data in table by selecting rows.

(b) Verify the DELETE trigger Function

Similarly, the Delete Trigger should only allow deletion of required row in the table using statement with WHERE clause and it should not work for statement without WHERE clause.

The Delete operation is successful when WHERE clause is given. Due to Delete Trigger, the desired value from row is deleted preventing any other accidental deletion.

We can see that Delete Trigger works as it prevents the delete operation when WHERE clause is not defined. User can verify if any data is deleted using SELECT command.

Conclusion

In the above article, we have discussed the problematic situation that may generate due to accidental DML operations performed on the rows of the SQL Server Table. In order to avoid or prevent these kind of situations we have used SQL triggers that prevent the accidental Delete and Update Commands on rows of the Table. In addition, we have verified the functionalities of the Triggers. Therefore, we can conclude that usage of SQL Triggers is helpful in order to avoid the accidental DML commands.