CODEWITHSUNDEEP
sqlsql-servertriggers
m3k3r1
m3k3r1

Reputation: 15

Trigger subquery returns more than 1 value

I'm having a problem in this SQL Server trigger, I want to update info on Devices table after each insert on Mindframe table but when I have 2 entries separated by milliseconds, it gives an error saying it's returning more than 1 value.

Can someone explain me why ? Or point out my mistake ...

Trigger 

CREATE OR ALTER TRIGGER device_update 
ON MindFrame
FOR INSERT
AS
BEGIN
    IF @@ROWCOUNT = 0
        RETURN

    SET NOCOUNT ON;

    IF EXISTS(SELECT * FROM INSERTED)
    BEGIN
        DECLARE @dev NVARCHAR(6)
        DECLARE @lat NVARCHAR(45)
        DECLARE @lng NVARCHAR(45)
        DECLARE @bat FLOAT

        SET @dev = (SELECT  device from inserted)
        SET @lat = (SELECT latitude from inserted)
        SET @lng = (SELECT longitude from inserted)
        SET @bat = (SELECT battery from inserted)

        UPDATE Devices
        SET lat = @lat, lng = @lng, bat = @bat, last_seen = GETDATE()
        WHERE @dev = Devices.device
    END
END

Upvotes: 0

Views: 944

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You are using inserted wrong. It can contain multiple rows. It needs to be treated like a table (or technically a view). Never assume inserted or deleted has only one row.

The trigger is even easier to write this way:

BEGIN
    UPDATE d
        SET lat = i.latitude, 
            lng = i.longitude, 
            bat = i.battery,
            last_seen = GETDATE()
        FROM inserted i JOIN
             devices d
             on i.device = d.device
END;

You don't need to check if inserted has rows (if there are none, nothing gets updated). You don't need to define local variables. The code is shorter . . . and it works.

Upvotes: 2

Related Questions