上次插入值的触发器

Question

谁能帮我创建一个触发器，将Sensordata表中的最后一条记录附加到lastssensordata中。我只希望每个ConnectionDeviceId有1个值，并且该值必须是最后插入的值。（它将用于在仪表中显示）。我将在下链接我的sql脚本。

CREATE TABLE Revpi (
revpiName varchar(100) NOT NULL,
revpiDateTimeCreated datetime NOT NULL,
revpiLocation varchar(150) NOT NULL,
CONSTRAINT PK_revpiNavn PRIMARY KEY (revpiName)
);

CREATE TABLE Sensor (
sensorName varchar(100) NOT NULL,
revpiName varchar(100) NOT NULL,
sensorDateTimeCreated datetime NOT NULL,
CONSTRAINT PK_sensorNavn PRIMARY KEY (sensorName),
CONSTRAINT FK_revpiName FOREIGN KEY (revpiName) REFERENCES Revpi (revpiName)
);

CREATE TABLE Sensordata (
SensordataID int NOT NULL AUTO_INCREMENT,
data varchar (500),
ConnectionDeviceId varchar(500) NOT NULL,
EventProcessedUtcTime varchar(50) NOT NULL,
CONSTRAINT PK_sensorDataNavn PRIMARY KEY (SensordataID),
CONSTRAINT FK_sensorNavn FOREIGN KEY (ConnectionDeviceId) REFERENCES Sensor (sensorName)
);

CREATE TABLE Lastsensordata (
lastSensorNavn varchar(255) NOT NULL,
lastData varchar (500) NOT NULL,
LastTime varchar(50) NOT NULL,
CONSTRAINT PK_Lastsensordata PRIMARY KEY (lastSensorNavn),
CONSTRAINT FK_LastensordataSensordata FOREIGN KEY (lastSensorNavn) REFERENCES Sensordata (ConnectionDeviceId)
);

Answer 1

您可以使用以下触发器执行此操作：

CREATE TRIGGER after_insert_sensordata 
AFTER INSERT ON Sensordata
FOR EACH ROW 
REPLACE INTO Lastsensordata VALUES (NEW.ConnectionDeviceId, NEW.`data`, NEW.EventProcessedUtcTime);

您需要使lastSensorNavn字段唯一。您还应该修复lastsensornavn和connectiondeviceid之间的varchar大小差异。固定表的外观如下所示：

CREATE TABLE Lastsensordata (
lastSensorNavn varchar(500) UNIQUE NOT NULL,
lastData varchar (500) NOT NULL,
LastTime varchar(50) NOT NULL,
CONSTRAINT PK_Lastsensordata PRIMARY KEY (lastSensorNavn),
CONSTRAINT FK_LastensordataSensordata FOREIGN KEY (lastSensorNavn) REFERENCES Sensordata (ConnectionDeviceId)
);