Suppose that I want to delete a user named 'abc cba' from the database. So I run the following query:

DELETE FROM table WHERE fname = 'abc' AND last name = 'cba'; commit;

Now, there can be more users with this name I do not know about. The obvious choice would be to run select statement beforehand and check if I am deleting what I really wanted to delete, but I am still human and I can forget that and end up searching for backups to retrieve lost data.

My question is: is there a way to impose certain rules on statements after which they will not execute the DML statement (for example, do not execute the delete if the number of rows deleted exceeds certain threshold)?

  • Which RDBMS are you working with? SQL Server? – Thomas Stringer May 7 '13 at 14:27
  • The question is not really DBMS-dependent, I would like to know if there is a way for each of the major DBMS. – kyooryu May 7 '13 at 14:28
  • IF you're lucky, your favourite SQL Editor may include a feature which automatically inserts a BEGIN WORK / START TRANSACTION and a ROLLBACK, then drops your cursor in the middle. If you're using transactions then it's much harder to be caught off-guard. – Nathan Jolly May 7 '13 at 14:40
  • 2
    @kyooryu the question is RDBMS dependant as there is nothing in the SQL standard that enforces this kind of functionality – Philᵀᴹ May 7 '13 at 14:46
  • I was aware that the solutions would probably vary depending on DBMS, but I did not want to restrict the question to only one DBMS, because I am interested in the functionality in general, not in the context of DBMS. – kyooryu May 7 '13 at 14:51

Imposing such rules on db level can be very expensive in terms of resources, so I don't think there is a standard way which will work everywhere.

I cannot suggest anything better than raising an error inside the body of BEFORE DELETE trigger (or AFTER DELETE, but BEFORE trigger seems to be more appropriate in this case). Unfortunately, it's quite RDMS dependent. For instance, in Oracle if you want to access the same table within the trigger, you need use statement level trigger; SQLServer calls such triggers INSTEAD OF , not BEFORE.

| improve this answer | |

I can only speak for SQL Server, but the only way this could be done is through a DML trigger (as far as I know). Take the below for example:

use TestDB;

create table dbo.TestTriggerTable
    id int identity(1, 1) not null,
    some_int int not null
        default 1

insert into dbo.TestTriggerTable
default values;
go 1000

create trigger DeleteMaxRows
on dbo.TestTriggerTable
after delete

    if (select count(*) from deleted) > 10
        raiserror('Cannot delete more than 10 rows', 16, 1);

select *
from dbo.TestTriggerTable;

-- this fails and rolls back
delete from dbo.TestTriggerTable
where id < 20;

-- this is successful
delete from dbo.TestTriggerTable
where id < 8;

There are a few problems with this approach though, and I don't believe this to be a good design. First off, you have hard-coded requirements within the trigger (whether you're doing a set row count, or a percentage of rows...it's still hard-coded). An application would have to be designed accordingly to ensure retry with different logic.

What this really comes down to is having a correctly designed database, and correctly constructed DML statements to ensure that the data that is being modified is the data you want, instead of having to check a row count of the deleted rows.

In my opinion, you should be spending design time in figuring out how to redesign so you won't need this check.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.