Return to Answer

Edit: added a fiddle

Since a device most likely exists in device_summary, it is more efficient to update first and check for number of updated rows (if 0, insert)

CREATE TRIGGER after_snmpData_insert 
AFTER INSERT ON snmpData
FOR EACH ROW 
BEGIN
    -- update lastSeen
    UPDATE device_summary 
        SET lastSeen = NEW.ts
    WHERE deviceID = NEW.deviceId;

    IF ( ROW_COUNT() = 0 ) THEN
        -- No rows updated, i.e. the device does not exist
        INSERT INTO device_summary
            (deviceID, firstSeen, lastSeen)
        VALUES (NEW.deviceID, NEW.ts, NEW.ts);
    END IF;    
END

Edit: added a fiddle, extended extended fiddle

Edit: an 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_snmpData foreign key (deviceID) 
                            references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

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

CREATE TABLE device_summary 
( deviceID varchar(12) NOT NULL
, firstSeen datetime NOT NULL
, lastSeen datetime NOT NULL
,   constraint pk_device_summary primary key (deviceID) 
,   constraint fk_device_summary 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 ;

Edit: added a fiddle

Edit: an 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_snmpData foreign key (deviceID) 
                            references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

Now, what is first- and last- seen? To me, this seems like a kind of aggregation or summary of a device. This kind of construction is sometimes named Incremental Evaluation System

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

The logic for updating device_summary is easily expressed in a trigger:

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 ;

Edit: added a fiddle

To get the latest data for each device:

select x.oid, x.value 
from snmpData x 
join device_summary y 
    on x.deviceId = y.deviceId 
   and x.ts = y.lastSeen;

Edit: an 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_snmpData foreign key (deviceID) 
                            references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

Now, what is first- and last- seen? To me, this seems like 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) 
,   constraint fk_device_summary 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.

Edit: an 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_snmpData foreign key (deviceID) 
                            references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

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

CREATE TABLE device_summary 
( deviceID varchar(12) NOT NULL
, firstSeen datetime NOT NULL
, lastSeen datetime NOT NULL
,   constraint pk_device_summary primary key (deviceID) 
,   constraint fk_device_summary 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 ;

Edit: added a fiddle