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

MySQLtrigger

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?

Best Answer

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:

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.

Related Question