trigger in MySQL to disallow multiple row deletions at a time on a table

Question

How do we create a trigger in MySql to disallow deletion of more than 2 rows from (say) employee table by a single query?

delimiter \\
create trigger not_more_than_2_rows  
before delete on  employee
begin
declare count int default 0;   
if (count>1) then  
    signal sqlstate '12345'
    set message_text = 'cannot delete more than 2 rows at a time'
end if;
set count = count+1
end \\
delimiter ;

but this does not seem to work, the count variable is getting initialized for each row. I could see that one way to overcome this is to initialize variable count once per trigger execution instead of once for each row....

Any other solutions?

score 1 · Answer 1 · edited Jun 15 '20 at 09:05

This goes against all that righteous and good in triggers but here we go...

Create a table that keep a count of attempted deletes per connection

CREATE TABLE IF NOT EXISTS trigger_happy_employee_deletes
(
    delete_count INT DEFAULT 0,
    conn_id INT NOT NULL,
    PRIMARY KEY (connection_id)
) ENGINE=MyISAM;

or to reduce disk I/O make it a MEMORY table

CREATE TABLE IF NOT EXISTS trigger_happy_employee_deletes
(
    delete_count INT DEFAULT 0,
    conn_id INT NOT NULL,
    PRIMARY KEY (connection_id)
) ENGINE=MEMORY;

Now get the count from that table and check it

delimiter \\
create trigger not_more_than_2_rows  
before delete on  employee
begin
declare count int;
insert ignore into trigger_happy_employee_deletes (conn_id) values (connection_id());
select delete_count into count
    from trigger_happy_employee_deletes
    where conn_id = connection_id()
;
set count = count + 1;
update trigger_happy_employee_deletes
    set delete_count = count
    where conn_id = connection_id()
;
if (count>1) then  
    signal sqlstate '12345'
    set message_text = 'cannot delete more than 2 rows at a time'
end if;
end \\
delimiter ;

After your issue a DELETE command, you will have to remember to clear the count like this:

delete from employee where ... ;
update trigger_happy_employee_deletes
    set delete_count = 0
    where conn_id = connection_id()
;

or you count clear it before:

update trigger_happy_employee_deletes
    set delete_count = 0
    where conn_id = connection_id()
;
delete from employee where ... ;

Give it a Try !!!

POTENTIAL DRAWBACKS

Here are the pitfalls of doing this:

Pitfall #1

Using an arbitration method like this (via a MyISAM table) will cause a major bottleneck because a MyISAM performs a full table lock on each INSERT. Using a MEMORY table will not fare much better because although there is far less table access on disk, there is still nominal disk I/O hitting the MEMORY table's .frm file that can cause a slight bottleneck when given enough database traffic.

You cannot make trigger_happy_employee_deletes InnoDB because triggers do not work intelligently with InnoDB:

Jun 21, 2011 : Using MySQL triggers or transactions?
Mar 21, 2011 : Dynamic SQL in MySQL stored routines

Pitfall #2

From a coding perspective, you will have to remember to setup this mechanism across your code. If you forget to do this in all necessary places, you will have undesired deletions.

Pitfall #3

If you want this same mechanism for another table, you would either make another trigger_happy table for deletes or be tempted to merge all delete triggers into a common trigger_happy table, which would make maintenance a total nightmare, not to mention increased bottlenecks by an order of magnitude. Even worse, you may be tempted to merge update and insert triggers into the common trigger_happy table.

Pitfall #4

The trigger would have to communicate with another table outside the triggered table. That's more internal read traffic just to maintain a count.