Creating a trigger in SQL that reads data from one table, and inserts new data to another

Question

I'm having some trouble in my intro to databases class understanding what I can and can't do in SQL regarding triggers. One question in particular is confusing.

I am given this schema (which can be viewed here):

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)
);

And with this, I am supposed to create an "event detection mechanism" that raises an alarm when the temperature, from RawTemperature is too high. The trigger checks if the temperature is larger than 65 degrees celsius, and if it is, will update appropriately the Event and DerivedFrom tables. It will create an Event of type 'Too High Temperature' with a confidence of 1. It also states that we can assume there are no concurrency issues.

To solve this I tried this idea (sort of pseudo-codish):

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;

But this gives me errors and doesn't really work at all. So how can I properly use triggers to accomplish this?

score 1 · Answer 1 · answered May 18 '15 at 04:12

I believe this does what you want:

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 ;

Fiddle: http://sqlfiddle.com/#!9/65a01/1/0

You'll note I also made a few inserts of sample data, in addition to the insert into rawtemperature because otherwise your foreign keys would fail (given that your fiddle contains no sample data):

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');

That trigger, with the DDL you posted, and the above sample data, seems to work as you've intended.

For instance, with that insert of 66 degress (which is above 65), when you run:

select * from event;

(as in the fiddle), you get:

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

The trigger correctly inserted the associated row into the event table. Had the insert been 65 degrees or less, it would not have.

On a side note, the DDL you posted is different from the DDL you put in your fiddle (in a few places). I'm assuming the DDL you posted in the body of your post is your actual DDL.

Also, because triggers can involve 2+ statements (and yours does) you have to temporarily change the delimiter to something other than a semicolon (so that your trigger can be interpreted as a whole. I used a double slash (//)

score 1 · Accepted Answer · answered May 18 '15 at 04:29

1

You aren't far off - one point to note is that in a trigger, you have access to pseudo rows new and old which represent each new row being inserted or old row being updated / deleted (in this case into the TemperatureHigh table).

You can make use of the columns on new to retrieve the observation and sensorid to insert into the Event + DerivedFrom tables.

Also, note that the requirement is that the temperature must be greater than 65 Celsius, you'll need to join back to the TemperatureSensor definition for the sensor to see what it is measuring.

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 here with various test cases

Based on the Event table's layout (AUTO_INCREMENT and name), it would seem to me that this table requires new events to be inserted rather than old events to be updated.

answered May 18 '15 at 04:29

StuartLC

104,537
17
209
285

This is amazing, thank you for your help and the explanation! I'm kind of confused by the `SELECT 1 FROM`.. statement though. Why do we use that instead of SELECT *? – Alex May 18 '15 at 04:45
It largely doesn't actually matter what you put in the select (we are checking for existence of a row rather than selecting anything). `select *` will also do [exactly the same thing](http://stackoverflow.com/a/26461901/314291). I use `1` as it conveys clearly that the columns aren't used which hopefully makes it easier to read. – StuartLC May 18 '15 at 04:48
(I mean of course that it doesn't matter what you put into the `select ` when wrapped in an `EXISTS`)! Also, the `LAST_INSERT_ID()` will give the ID from the preceding `insert into Event` row. – StuartLC May 18 '15 at 05:01
1

Ah that makes a lot of sense. Thank you for all of your help! You cleared up a lot of confusion for me, I appreciate it. – Alex May 18 '15 at 05:12
1

I guess one mandatory caveat needed is to note that although triggers are super powerful, they are generally regarded as bad practice as they happen "behind the scenes" and systems with many triggers can be difficult to debug - where possible, tend to put business logic into stored procedures or application code. – StuartLC May 18 '15 at 05:16

Creating a trigger in SQL that reads data from one table, and inserts new data to another

2 Answers2