-2

Can anyone help me create a trigger that appends the last record from table Sensordata into Lastsensordata. I only want 1 value per ConnectionDeviceId and the value needs to be the last inserted one. (its going to be used to show in a gauge). BTW, this is on azure sql database. So it is SQL Server and not SQL Workbench. will link my sql script under.

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(500) 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 IDENTITY,
data varchar (500),
ConnectionDeviceId varchar(500) UNIQUE,
EventProcessedUtcTime varchar(50) NOT NULL,
CONSTRAINT PK_sensorDataNavn PRIMARY KEY (SensordataID, ConnectionDeviceId),
CONSTRAINT FK_sensorNavn FOREIGN KEY (ConnectionDeviceId) REFERENCES Sensor (sensorName)
);

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

This is what I have now but it is for sql workbench so I get a lot of syntax errors.

CREATE TRIGGER after_insert_sensordata 
AFTER INSERT ON Sensordata
FOR EACH ROW 
REPLACE INTO Lastsensordata VALUES (NEW.ConnectionDeviceId, NEW.`data`, NEW.EventProcessedUtcTime);
  • 4
    You probably should have edited your original question, closed for lack of details, instead of posting it again verbatim. – mustaccio May 4 at 15:45
0

You have brought alot of issues into your question and your question is almost impossible to answer the way you ask it. People don't like to mess around with "could you rescue me out of my situation" - questions. You have to reduce complexity and experiment yourcels and extract specific questions you cannot answer yourself, otherwise you see that your quesion will be ignored, downvoted and removed.

However since I have some time I will give it a try and answer what I feel is the extracted question.

Can anyone help me create a trigger that appends the last record from table Sensordata into Lastsensordata.

A table does not have a "last record". This could be some different things, but it depends on your criteria. Last in terms of insert time? Last in terms of any sort order?

First of all, in general I don't think a TRIGGER is the solution for what looks as you aim for. I think you should try to create a query then delivers the answer based on the data you already have collected instead of create a copy of some of the data you already have.

However, whiping away a lot of questionmarks that come with your "question" and after removing the barrieres (several blocking Unique Keys, Foreign Keys...) from your code and reducing the situation into a more basic and more answerable question like "How does a trigger work?" - I would answer like this in order to help you to create this thing and maybe figure out that a trigger might be a bad choice here.

CREATE TABLE Sensordata (
SensordataID int NOT NULL IDENTITY,
data varchar (500),
dt_insert datetime2 null
);


CREATE TABLE Lastsensordata (
lastSensorNavn varchar(500) NULL,
dt_insert datetime2 null,
dt_trigger datetime2 null
);


CREATE TRIGGER myInsertTrigger
ON [dbo].[Sensordata]
 AFTER INSERT
 AS 
 BEGIN

    /*remove the previous last entries according to the SensorDataId (you can use whatever matches your criteria) */

    DELETE [dbo].[Lastsensordata] WHERE lastSensorNavn = (SELECT TOP 1 data FROM INSERTED ORDER BY [SensordataID] desc)

    /*insert the "last" inserted record - it looks useless to me, but decide yourself - please not, as this sample shows, that an insert is possibly coming with more than row to be inserted and the trigger will consequently process all of them */

    INSERT INTO [dbo].[Lastsensordata] (lastSensorNavn, dt_insert, dt_trigger)
    SELECT TOP 1 data, dt_insert, getdate() FROM INSERTED ORDER BY [SensordataID] desc
 END 

-- Generate random insert
INSERT INTO [dbo].[Sensordata]
           ([data], dt_insert)
     select 'a', getDate()
     UNION
     select 'b', getDate()

-- Generate random insert
INSERT INTO [dbo].[Sensordata]
           ([data], dt_insert)
     select 'c', getDate()
     UNION
     select 'd', getDate()

-- Generate random insert
INSERT INTO [dbo].[Sensordata]
           ([data], dt_insert)
     select 'a', getDate()
     UNION
     select 'd', getDate()

select * From [dbo].[Sensordata]
select * From [Lastsensordata]

In the end you will find the "last" inserted row of the first insert statement (a) and the last inserted row of the third statement (d) collected by your trigger.

I really don't see much of a use case here but however, if this helps you to understand how a triggger works, maybe you can make your own conclusions? Good luck :)

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.