Is it possible to validate DML statements? 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)?
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;
go

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

insert into dbo.TestTriggerTable
default values;
go 1000

create trigger DeleteMaxRows
on dbo.TestTriggerTable
after delete
as

    if (select count(*) from deleted) &gt; 10
    begin
        raiserror('Cannot delete more than 10 rows', 16, 1);
        rollback;
    end
go

select *
from dbo.TestTriggerTable;

-- this fails and rolls back
delete from dbo.TestTriggerTable
where id &lt; 20;
go

-- this is successful
delete from dbo.TestTriggerTable
where id &lt; 8;
go


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.