MySQL Triggers

I came across an interesting problem: I needed to create priorities for records in a database so that they would be able to be displayed in a particular order. Moreover, I needed to find a way to reorder the priorities on the fly. My first instinct was for each record to have its own priority (PRI) column. But what would happen on an insert, if, say, the newly inserted item’s priority is somewhere in the middle? I would have to reorder the list. My table looks like this:

Table TEST
ID VARCHAR(10)
PRI INT(5)

I have never used triggers before, but from what I understood, they would have been the perfect solution.


delimiter $$
create trigger update_pri before insert on test
for each row begin
if NEW.pri is NOT NULL THEN
if exists(SELECT 'TRUE' from test where pri=NEW.pri) THEN
update test set pri=pri+1 where pri>=NEW.pri;
end if;
end if;
end$$
delimiter ;

But the trigger resulted in the following error:

ERROR 1442 (HY000): Can’t update table ‘test’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

That’s when I realized that MySQL doesn’t allow a write operation on a table that is being written to for the fear of corruption and recursive triggers (think about having a trigger on INSERT that also INSERTS some data. It would never end).

The one workaround is to have a separate table with ID and PRI colums that reference the original table:

Table TEST
ID VARCHAR(10)
ORIG_PRI INT(5)

Table PRIORITIES
ID VARCHAR(10)
PRI INT(5)

Why do I need the ORIG_PRI column in the TEST table? In order to get the new value somehow. If the ORIG_PRI didn’t exist, where would NEW.PRI (from the previous trigger) come from? So, the new trigger would look like this:


delimiter $$
create trigger update_pri before insert on test
for each row begin
if NEW.orig_pri is NOT NULL THEN
if exists(SELECT 'TRUE' from priorities where pri=NEW.orig_pri) THEN
update priorities set pri=pri+1 where pri>=NEW.orig_pri;
insert into priorities(id, pri) values(NEW.id, NEW.orig_pri);
end if;
end if;
end$$
delimiter ;

So, every time an INSERT (or an UPDATE) is performed, we actually perform the operation just on the TEST table (i.e. INSERT INTO TEST (ID, ORIG_PRI) VALUES(‘a’, 1)) and the trigger automatically updates all the priorities in the PRIORITIES table. Yes, the ORIG_PRI column will eventually have outdated data, but who cares? We don’t use the ORIG_PRI for the priorities, that’s what the table PRIORITIES is for. The trigger automatically adds and updates the original priorities.

We could also go a step further and create a trigger for a DELETE operation, but why? If the delete operation removes an entry in the middle, all the priorities will still be in the same order, just incremented by one.

Even though this is kind of a dirty method of doing this, I can’t think of anything better. I’m open to suggestions, though.

Leave a Reply