Создание триггера в SQL, который считывает данные из одной таблицы и вставляет новые данные в другую

Question

У меня возникли проблемы с введением в класс баз данных, понимая, что я могу и не могу делать в SQL в отношении триггеров. Один вопрос, в частности, сбивает с толку.

Мне дана эта схема (которую можно посмотреть здесь):

CREATE TABLE Sensor(
    sid int NOT NULL AUTO_INCREMENT,
    Name varchar(100),
    PRIMARY KEY(sid)
);

CREATE TABLE TemperatureSensor(
    sid int NOT NULL,
    metricSystem ENUM(‘Celsius’,’Kelvin’),
    PRIMARY KEY(sid),
    FOREIGN KEY(sid) REFERENCES Sensor(sid)
);

CREATE TABLE Observation(
    oid int NOT NULL,
    sid int NOT NULL,
    PRIMARY KEY(sid,oid),
    FOREIGN KEY(sid) REFERENCES Sensor(sid)
);

CREATE TABLE RawTemperature(
    oid int NOT NULL,
    sid int NOT NULL,
    temperature float,
    timestamp timestamp NOT NULL,
    PRIMARY KEY(sid,oid),
    FOREIGN KEY(sid,oid) REFERENCES Observation(sid,oid),
    FOREIGN KEY(sid) REFERENCES TemperatureSensor(sid)
);

CREATE TABLE Event(
    eid int NOT NULL AUTO_INCREMENT,
    activity ENUM(‘running’,’walking’,’entering’,’Too High Temperature’),
    confidence int unsigned,
    PRIMARY KEY(eid)
);

CREATE TABLE DerivedFrom(
    eid int NOT NULL,
    sid int NOT NULL,
    oid int NOT NULL,
    PRIMARY KEY(eid,oid,sid),
    FOREIGN KEY(eid) REFERENCES Event(eid),
    FOREIGN KEY(sid) REFERENCES Sensor(sid)
);  

И с этим, я должен создать "механизм обнаружения событий", который поднимает тревогу, когда температура, от RawTemperature слишком высоко Триггер проверяет, превышает ли температура более 65 градусов по Цельсию, и, если это так, обновит соответственно Event а также DerivedFrom столы. Это создаст Event типа 'Too High Temperature' с confidence из 1. В нем также говорится, что мы можем предположить, что нет проблем параллелизма.

Чтобы решить эту проблему, я попробовал эту идею (своего рода псевдокодиш):

CREATE TRIGGER TemperatureHigh
AFTER INSERT 
ON RawTemperature FOR EACH ROW
BEGIN
    UPDATE Event
    SET activity = ‘Too High Temperature’
INSERT INTO DerivedFrom(eid) VALUES (LAST_INSERT_ID())
    WHERE temperature > 65;
END;

Но это дает мне ошибки и не работает вообще. Итак, как я могу правильно использовать триггеры для достижения этой цели?

Answer 1

Вы не за горами - стоит отметить, что в триггере у вас есть доступ к псевдорядкам new а также old которые представляют каждую новую вставляемую строку или старую строку, которая обновляется / удаляется (в данном случае в TemperatureHigh Таблица).

Вы можете использовать столбцы на new получить наблюдения и сенсорид для вставки в Event + DerivedFrom столы.

Также обратите внимание, что требование состоит в том, что температура должна быть выше 65 Celsius, вам нужно присоединиться к TemperatureSensor определение для датчика, чтобы увидеть, что он измеряет.

CREATE TRIGGER TemperatureHigh
AFTER INSERT 
ON RawTemperature FOR EACH ROW
BEGIN
      IF (new.temperature > 65 AND EXISTS
         (SELECT 1 FROM TemperatureSensor WHERE sid = new.sid AND metricSystem = 'Celsius')) 
      THEN
        INSERT INTO `Event`(activity, confidence)
        VALUES('Too High Temperature', 1);

        INSERT INTO DerivedFrom(eid, sid, oid) 
        VALUES (LAST_INSERT_ID(), new.sid, new.oid);
      END IF;
END

SqlFiddle здесь с различными тестами

На основе макета таблицы событий (AUTO_INCREMENT и имя), мне кажется, что эта таблица требует добавления новых событий, а не старых событий для обновления.

Answer 2

Я считаю, что это делает то, что вы хотите:

delimiter //

create trigger temperaturehigh after insert on rawtemperature
    for each row begin
        insert into event (activity, confidence) values ('Too High Temperature',1);
        insert into derivedfrom select max(eid), new.oid, new.sid from event;
end
//

delimiter ;

Скрипка: http://sqlfiddle.com/

Вы заметите, что я также сделал несколько вставок образцов данных, в дополнение к вставке в rawtemperature потому что в противном случае ваш внешний ключ потерпит неудачу (учитывая, что ваша скрипка не содержит примеров данных):

insert into sensor values (1,'Test Sensor');
insert into temperaturesensor values (1,'Celsius');
insert into observation values (1,1);
insert into rawtemperature values (1,1,66,'2015-01-01 12:00:00');

Этот триггер с отправленным вами DDL и приведенными выше примерами данных, кажется, работает так, как вы и предполагали.

Например, с этой вставкой 66 градусов (что выше 65), когда вы запустите:

select * from event;

(как в скрипке), вы получите:

| eid |             activity | confidence |
|-----|----------------------|------------|
|   1 | Too High Temperature |          1 |

Триггер правильно вставил связанную строку в event Таблица. Если бы вставка была 65 градусов или меньше, это не было бы.

Напомним, что опубликованный вами DDL отличается от DDL, который вы положили в скрипку (в нескольких местах). Я предполагаю, что DDL, который вы разместили в теле вашего поста, является вашим настоящим DDL.

Кроме того, поскольку триггеры могут включать в себя более 2 операторов (как и ваши), вы должны временно изменить разделитель на точку, отличную от точки с запятой (чтобы ваш триггер можно было интерпретировать как единое целое. Я использовал двойную косую черту (//)