Learn more about SQL Server tools

   
   















































Prevent accidental update or delete commands of all rows in a SQL Server table

MSSQLTips author Atif Shehzad By:   |   Read Comments (13)   |   Related Tips: More > Triggers

Problem
Performing DML operations through tested application are always safe and efficient.  This process also prevents an accidental update or delete of all rows in a table. In my environment, I have a couple of tables that often require ad-hoc updates of certain values or delete of certain rows.  On our development SQL Servers ad-hoc DML operations for various purposes are frequent.  Unfortunately, there have been a couple of incidents in when someone accidentally issued a DML command without a WHERE clause, hence affecting the entire table. Is there an efficient way to prevent accidental DML operations without a WHERE clause?

Solution
It is a good idea to prevent accidental DML operations which may affect an entire table. For both accidental UPDATE or DELETE prevention, we make use of triggers in this tip. A new trigger may be created and if a trigger is already defined on the table then it may be altered to embed prevention logic. As an example, let's use the Purchasing.VendorContact table in the AdventureWorks database for this hands on example.  Here are the basic steps we are going to follow:

  • First, we will create a prevention trigger for UPDATE operations
  • Second, we will create a prevention trigger for DELETE operations
  • Third, we will verify the prevention functionality for both UPDATE and DELETE triggers

One final note before we get started, it is assumed that a cluster index exists for the table on which these triggers would be implemented.


Create trigger to prevent accidental update of all rows in a table

Currently no trigger is defined on the Purchasing.VendorContact table. First we will create an UPDATE trigger to prevent any accidental update of all rows in table.

Script # 1: Create UPDATE trigger for SQL Server 2005 and SQL Server 2008
USE AdventureWorks
GO
CREATE TRIGGER [Purchasing].[uPreventWholeUpdate] 
ON [Purchasing].[VendorContact] 
FOR UPDATE AS 
BEGIN
     DECLARE @Count int
     SET @Count = @@ROWCOUNT;
         
     IF @Count >= (SELECT SUM(row_count)
         FROM sys.dm_db_partition_stats 
         WHERE OBJECT_ID = OBJECT_ID('Purchasing.VendorContact' ) 
         AND index_id = 1)
     BEGIN
         RAISERROR('Cannot update all rows',16,1) 
         ROLLBACK TRANSACTION
         RETURN;
     END
END
GO

We have used the sys.dm_db_partition_stats DMV to get find out the number of rows in the table. Since the DMVs where introduced with SQL Server 2005, the following script can be used:

Script # 2: Create UPDATE trigger for SQL Server 2000
USE AdventureWorks
GO
CREATE TRIGGER [Purchasing].[uPreventWholeUpdate] 
ON [Purchasing].[VendorContact] 
FOR UPDATE AS 
BEGIN
     DECLARE @Count int
     SET @Count = @@ROWCOUNT;
         
     IF @Count >= (SELECT rowcnt
         FROM sysindexes 
         WHERE ID = OBJECT_ID('Purchasing.VendorContact' ))
     BEGIN
         RAISERROR('Cannot update all rows',16,1) 
         ROLLBACK TRANSACTION
         RETURN;
     END
END
GO

If an update trigger is already defined on the table, then alter the code above can in inserted into the body of trigger as shown below:

If you have a need to update all of the rows of table, then disable the trigger or ALTER the trigger to prevent the logic from executing.


ALTER trigger to prevent accidental delete of all rows in a table

First we will create a simple delete trigger on the Purchasing.VendorContact, table to show how to modify existing delete trigger later in this tip.

Script # 3: Create DELETE trigger for SQL Server 2005
USE AdventureWorks
GO  
CREATE TRIGGER [Purchasing].[dPredefined] 
ON [Purchasing].[VendorContact] 
FOR DELETE AS 
BEGIN
     /*
     Logic of trigger coded here
     */
     Print 'Previous logic of Trigger is working '
END
GO  

 

Now that a DELETE trigger is defined on the Purchasing.VendorContact table, we can modify the delete trigger to prevent accidental delete of all rows in table. Just ALTER the trigger and paste code for the prevention logic at the beginning of the trigger. Also be sure to change the message generated in the RAISEERROR logic appropriately.  Keep in mind that the previous functionality of the user defined trigger will remain intact.

Now that the trigger Purchasing.dPredefined has been modified to prevent a delete operation without a WHERE clause, keep the remaining code intact and it should work without an issue. For SQL Server 2000 instances, make use of dbo.sysindexes table instead of the DMV as mentioned in the script above.  If it is required to delete all rows from a table and no logged operations are permitted, then issuing a truncate table command should be more efficient and it should not invoke the trigger logic.


Verify the functionality of UPDATE trigger

Now we have two triggers defined on Purchasing.VendorContact to prevent the a mass update or deletion of all rows in the table, let's verify the desired functionality.

First we will issue an update statement with a WHERE clause.

The update was successful when performed by providing WHERE clause.

Now let us issue the update statement without WHERE clause.

This statement generates an error.  You can also verify the data in the table by selecting the rows.


Verify the functionality of DELETE trigger

First we will execute a delete statement with a WHERE clause.

Now to verify the delete prevention of all rows without a WHERE clause, let's issue the delete statement without WHERE clause.

Once again the trigger logic is fired and the delete is prevented.  You can also verify the presence of the data subject issuing SELECT statements.

Next Steps



Last Update: 10/5/2009


About the author
MSSQLTips author Atif Shehzad
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, May 12, 2014 - 7:17:01 AM - Matthew Stafford Read The Tip

At the end of the IF statement, it would be better if you added the following condition:

AND @Count > 1

So that if there is only 1 row in the table the update/delete will still work.

Many thanks for your solution.

 


Saturday, July 21, 2012 - 4:20:15 AM - Deb Kumar Bhunya Read The Tip

Than you for giving solution .

Its help me . many many many Thanks


Thursday, March 08, 2012 - 9:18:13 AM - khurram cheema Read The Tip

Nice Tip Thanks


Thursday, October 15, 2009 - 9:25:46 AM - Beca Read The Tip

Case WAS ii) ...Now it's OK.

Was my mistake , i've not set VIEW DATABASE STATE permission to user (to run sys.dm_db_* views)

Thank you very much for the tips and the support.

Regards


Friday, October 09, 2009 - 9:11:49 PM - @tif Read The Tip

 @Beca: Please clearify that

Case i) problem occurs only when whole table is manupulated without where clause

Case ii) or it also occurs with selected records.

I did not test the case (i) with any application because i have in my mind that application would not manipulate whole table, as application code is always tested and reliable.I just performed testing for ad-hoc DML operations.

If your problem is like case (i) as mentioned above, then please check for RAISEERROR and ROLLBACK TRANSACTION permissions from within your application one by one.

Regards

 


Friday, October 09, 2009 - 4:19:09 AM - Beca Read The Tip

Thanks for the great topic, but i had problem.
When i use SQL Server Management Studio Console (2008) it works very well, using a Web application (IIS6 / ASP / VBScript) WITH SAME CREDENTIALS  i had error: [Microsoft][ODBC SQL Server Driver][SQL Server]The user does not have permission to perform this action.
Can anyone help me?
Best Regards

Beca


Tuesday, October 06, 2009 - 12:01:14 AM - ihar_ku Read The Tip

Good topic and the comments!!!

Thanks a lot.


Monday, October 05, 2009 - 8:58:22 PM - @tif Read The Tip

 @ riix. You have made good use of existing audit implemented. That would help any one having such scenario. Thanks for sharing it.

 Regards 


Monday, October 05, 2009 - 8:47:25 PM - @tif Read The Tip

 @ cranfield. Thanks for nice other options. Of course INSTEAD OF trigger and foreign key relations may be considered as good option. I was interested to embed the prevention logic in already existing triggers. Or to add any coming logic to prevention trigger so i used DML triggers.

Using scheme binding has several requirements associated with it. If those requirements are satisfied then it would be a good option for prevention.

Regards


Monday, October 05, 2009 - 8:38:05 PM - @tif Read The Tip

[quote user="Muhamamd Fahim"]Hi Atif Shehzad, If we want to secure our entire database for accidental update or delete commands then repeat this for each table?[/quote] 

As this article is related to scenario where specific tables with frequent ad-hoc updates and  deletes are required to be handled so implementing this logic for all tables, one by one would not be a good option. There should be some other approach to work with for whole database. I will try to cover this problem in any coming article.

Regards


Monday, October 05, 2009 - 7:16:35 AM - riix Read The Tip

Good article.  We use a simpler way to achieve same result, may not work for all cases but it suffices for our requirements:
a) all our data tables have a surrogate primary key called "rowuid" (row unique identifier) - not necessary but convenient and simplifies triggers even further
a) all data tables have matching audit tables
b) all data tables have UPDATE and DELETE triggers
c) all triggers just copy the "deleted" row to the respective audit table

This gives us a simple and effective audit tool where all "before" row images are logged to audit tables.
Now for tables where we allow multi-row updates or deletes, the triggers look like this:

insert <audittable> select * from deleted

But for tables where we don't wish to allow multi-row updates or deletes, the triggers look like this:

insert <audittable> select * from deleted where rowuid = (select rowuid from deleted)

In SSMS this is the nice error we get:

Msg 512, Level 16, State 1, Procedure xxx, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Of course we wrap this in TRY/CATCH and report suitable error back to caller.
Like I said, may not be for everyone but works well for us.

Cheers.

 


Monday, October 05, 2009 - 6:47:47 AM - --cranfield Read The Tip

Nice tip. thanks.

INSTEAD OF TRIGGERS is also a good way to handle accidental deletes.

Also, if you are paranoid (like most DBAs), to avoid accidentally dropping a table you can create VIEWS on the table WITH SCHEMABINDING so that any changes to table will be disallowed without dropping VIEW first.

Finally, to prevent users accidentally TRUNCATEing table you can create a foreigh key relationship to your table. TRUNCATE wont work on a table with a foreign key.

 

 


Monday, October 05, 2009 - 4:30:28 AM - Muhamamd Fahim Read The Tip
Hi Atif Shehzad, If we want to secure our entire database for accidental update or delete commands then repeat this for each table?



 
Sponsor Information