CODEWITHSUNDEEP
mysqlsqltriggers
Alex
Alex

Reputation: 2299

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

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?

Upvotes: 1

Views: 207

Answers (2)

StuartLC
StuartLC

Reputation: 107267

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.

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

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 (//)

Upvotes: 1

Related Questions