SQL trigger to delete rows from database
- by wpearse
I have an industrial system that logs alarms to a remotely hosted MySQL database. The industrial system inserts a new row whenever a property of the alarm changes (such as the time the alarm was activated, acknowledged or switched off) into a table named 'alarms'.
I don't want multiple records for each alarm, so I have set up two database triggers. The first trigger mirrors each new record to a second table, creating/updating rows as required:
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 alarms table. (I used the Tag property of the alarm because the Tag property never changes, although I suspect I could just use a 'DELETE FROM alarms WHERE 1' statement to 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 doesn't appear to run, or if it does, the second trigger doesn't delete any rows from the database.
So here's the question: why does my second trigger not do what I expect it to do?
