MySQL触发器结合两个事件

Hello All,

I am not very experienced with MySQL triggers, only done basic things such as inserting of timestamps when a new record is inserted. With that in mind, I am seeking for some help on how to solve the following problem. Any help will be much appreciated.

我对MySQL触发器不是很熟悉,只做基本的事情,比如在插入新记录时插入时间戳。考虑到这一点,我正在寻求一些如何解决以下问题的帮助。任何帮助都感激不尽。

I collect raw events into a table. These events are written when a tag changes state, i.e. on or off. Please see table below for an example;

我将原始事件收集到一个表中。当标签改变状态,即打开或关闭时,将写入这些事件。请参阅下表以获取示例;

events_raw

+----+----------+-----+-------+---------------------+
| id | location | tag | state | time                |
+----+----------+-----+-------+---------------------+
| 1  | 14       | 23  | ON    | 2013-06-07 07:59:52 |
| 2  | 14       | 24  | ON    | 2013-06-07 08:13:03 |
| 3  | 14       | 17  | ON    | 2013-06-07 08:21:21 |
| 4  | 14       | 23  | OFF   | 2013-06-07 08:25:15 |
| 5  | 14       | 6   | ON    | 2013-06-07 09:30:55 |
| 6  | 14       | 24  | OFF   | 2013-06-07 09:58:42 |
| 7  | 14       | 23  | ON    | 2013-06-07 09:58:53 |
+----+----------+-----+-------+---------------------+

What I would like to achieve is a second table like the example below. When records are inserted into events_raw, process them into another table that has one record containing the time that tag was on and when it changed to off. Once processed I would like to remove the records from alarm_data.

我想要实现的是第二个表,如下例所示。将记录插入events_raw时,将它们处理到另一个表中,该表中包含一个记录,其中包含标记所在的时间以及更改为关闭的时间。处理完毕后,我想从alarm_data中删除记录。

events_processed

+----+----------+-----+---------------------+---------------------+
| id | location | tag | time_start          | time_finish         |
+----+----------+-----+---------------------+---------------------+
| 1  | 14       | 23  | 2013-06-07 07:59:52 | 2013-06-07 08:25:15 |
| 2  | 14       | 24  | 2013-06-07 08:13:03 | 2013-06-07 09:58:42 |
+----+----------+-----+---------------------+---------------------+

I'm really sorry if my explanation is poor. I am really stuck on this, if you need more info to go on please say.

如果我的解释很差,我真的很抱歉。我真的很困惑,如果你需要更多的信息,请说。

Again, thank you for any help.

再次,谢谢你的帮助。

1 个解决方案

#1

If you have a unique key in Location + Tag columns .. then you can write a trigger in events_row as

如果Location + Tag列中有唯一键,那么您可以在events_row中编写一个触发器

CREATE TRIGGER TGR_ROW_AI AFTER INSERT ON event_Row FOR EACH ROW BEGIN

    INSERT INTO events_processed (location,tag,time_start) 
    VALUES (New.Location,New.tag,new.time) 
    ON DUPLICATE KEY UPDATE time_finish =new.time ;
END;

I Assume the ID column is an autoIncrement column.

我假设ID列是autoIncrement列。

#1