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.
There are two possible solutions:
set @z
to null in the very beginning of your loop (prior to SET @sql_text2 = concat('
....)
or instead of
SELECT id INTO @z FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1;
use this:
SET @z = (SELECT id FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1);
Explanation:
When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO
that won't reset the value if nothing is found.
see: https://dba.stackexchange.com/a/35207/12923
Best Answer
There are two ways to set a variable from a query, and they behave differently, especially when no record is matched.
You can
SELECT ... INTO
the variable...Or you can
SET
it to the value returned by a scalar subquery.Illustrating the difference with the sample database...
Our variable starts out
NULL
.We matched a row and our variable is set.
We try to select a record that doesn't exist:
So what's in our variable now?
I think this explains the confusion. When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like
SELECT ... INTO
that won't reset the value if nothing is found.On the other hand, if we use the other construct, the variable is reset when no record is matched.