SQL 触发器从数据库中删除行
我有一个工业系统，可将警报记录到远程托管的 MySQL 数据库中。每当警报的属性发生变化（例如警报激活、确认或关闭的时间）时，工业系统都会在名为“警报”的表中插入一个新行。
我不希望每个警报有多个记录，因此我设置了两个数据库触发器。第一个触发器将每个新记录镜像到第二个表，根据需要创建/更新行。第二个表（“alarm_display”）将“Tag”列设置为主键。 “警报”表没有主键。该触发器的代码是：
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
第二个触发器应在第一个触发器之后执行，并（理想情况下）从警报表中删除所有行。 （我使用了警报的 Tag 属性，因为 Tag 属性永远不会改变，尽管我怀疑我可以使用“DELETE FROM Alarms WHERE 1”语句来达到相同的效果）。
CREATE TRIGGER `remove_alarms` AFTER INSERT ON `alarms`
FOR EACH ROW DELETE FROM alarms WHERE Tag=new.Tag
我的问题是第二个触发器似乎没有运行，或者如果运行了，第二个触发器不会从数据库中删除任何行。
所以问题是：为什么我的第二个触发器没有执行我期望的操作？
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. The second table ('alarm_display') has the 'Tag' column set as the primary key. The 'alarm' table has no primary key. 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 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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
可以在此处阅读说明：http://dev. mysql.com/doc/refman/5.0/en/stored-program-restrictions.html
这是你的问题，你的触发器以错误 #1442 结束。
表
alarms已被调用触发器（插入）的语句使用。这本质上意味着您无法使用删除触发器修改警报。干杯!
The explanation can be read here: http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html
This is your problem and your trigger ends with error #1442.
The table
alarmsis already being used by the statement that invoked your trigger (the insert). This essentially means you cannot modifyalarmswith a delete trigger.Cheers!