Return to Revisions

Edit: alternative approach

-- remove first- last- seen from device
CREATE TABLE device 
( deviceID varchar(12) NOT NULL primary key
, snmpSet varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

-- add p.k. and f.k. 
CREATE TABLE snmpData 
( deviceID varchar(12)  NOT NULL
, ts datetime NOT NULL
, oid varchar(45)  NOT NULL
, value varchar(30)  NOT NULL
,    constraint pk_snmpData primary key (deviceID, ts) -- assumed 
,    constraint fk_device foreign key (deviceID) 
                          references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

Now, what are first- and last- seen? To me, this seems as a kind of aggregation or summary of a device.

CREATE TABLE device_summary 
( deviceID varchar(12) NOT NULL primary key
, firstSeen datetime NOT NULL
, lastSeen datetime NOT NULL
,   constraint pk_device_summary primary key (deviceID, ts) -- assumed 
,   constraint fk_device foreign key (deviceID) 
                         references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;


DELIMITER $$
CREATE TRIGGER after_snmpData_insert 
AFTER INSERT ON snmpData
FOR EACH ROW 
BEGIN
    -- if device exists, insert will be ignored
    INSERT IGNORE INTO device_summary
        (deviceID, firstSeen, lastSeen)
    VALUES (NEW.deviceID, NEW.ts, NEW.ts);

    -- update lastSeen
    UPDATE device_summary 
        SET lastSeen = NEW.ts
    WHERE deviceID = NEW.deviceId;
END$$
DELIMITER ;

It's untested, so the might be a glitch or two in there, but you should get the idea.