Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 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.

share|improve this answer
    
haha, I +1'd for "This goes against all that righteous and good in triggers", but then -1'd for showing how, so we're at a net no-gain currently! –  Derek Downey Dec 4 '12 at 16:04
    
@DTest haha That's why I said "but here it goes" –  RolandoMySQLDBA Dec 4 '12 at 16:05
    
I'll remove my virtual downvote for a +1 gain if you expand on what potential issues doing this could cause. –  Derek Downey Dec 4 '12 at 16:42
    
Yeh, I answered one like this once. I told the guy not to do it, showed him how, explained why not to do it, and gave some alternatives. I still lost about 10 reputation. I'm going to upvote this because you told him what he asked, AND told him not to do it. –  mdoyle Dec 4 '12 at 17:01
    
@mdoyle what that question in DBA.SE or another site ??? –  RolandoMySQLDBA Dec 4 '12 at 17:02
show 6 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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