SQL trigger to delete rows from a database

Question

I have an industrial system that logs alarms in a remotely hosted MySQL database. The industrial system inserts a new row whenever the alarm property changes (for example, when the alarm was activated, acknowledged, or disabled) in a table named "alarms".

I do not need several entries for each alarm, so I configured two database triggers. The first trigger reflects each new record in the second table, creating / updating rows as needed. The second table ("alarm_display") has a "Tag" column defined as the primary key. There is no primary key in the alarm table. The code for this trigger is:

CREATE TRIGGER `mirror_alarms` BEFORE INSERT ON `alarms` FOR EACH ROW INSERT INTO `alarm_display` (Tag,...,OffTime) VALUES (new.Tag,...,new.OffTime) ON DUPLICATE KEY UPDATE OnDate=new.OnDate,...,OffTime=new.OffTime 

The second trigger should execute after the first and (ideally) delete all rows from the alarm table. (I used the Tag property for alarm because the Tag property never changes, although I suspect I can just use the DELETE FROM alarms WHERE 1 statement with the same effect).

 CREATE TRIGGER `remove_alarms` AFTER INSERT ON `alarms` FOR EACH ROW DELETE FROM alarms WHERE Tag=new.Tag 

My problem is that the second trigger does not start, or if it does, the second trigger does not delete rows from the database.

So, the question is: why is my second trigger not doing what I expect from it?

Answer 1

The explanation can be read here: http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html

Inside a stored function or trigger, it is not allowed to modify the table that is already in use (for reading or writing) in the statement that called the function or trigger.

This is your problem, and your trigger ends in error # 1442.

The alarms table is already in use by the statement that called your trigger (insert). This means that you cannot change alarms with the delete trigger.

Hooray!