Error inserting multiple rows into View with INSTEAD OF trigger

Question

I have a Map table that stores a location using a GEOGRAPHY type. Since Entity Framework Core doesn't support spatial types, I have to map a MapView view instead with Lat and Long columns in my application. I then transform these Lat and Long values to a geography::Point when inserting or updating into the view using INSTEAD OF triggers.

Example:

CREATE TRIGGER [dbo].[MapViewInsertInstead] ON [dbo].[MapView]
INSTEAD OF INSERT
AS
IF ((SELECT Lat FROM inserted) IS NOT NULL AND (SELECT Long FROM inserted) IS NOT NULL)
BEGIN
INSERT INTO [dbo].[Map] (HouseId, HousePoint)
    SELECT HouseId
    geography::Point(Lat, Long, 4326)
FROM inserted
END

This has worked fine for a while, but I've always been inserting rows one at a time. I'm now doing a bulk-import task of data that includes Map data. When inserting multiple rows into the View at once, like this:

INSERT INTO MapView (HouseId, Lat, Long)
VALUES(0x1, 10, 10), (0x2, 10, 10);

I get this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So this trigger only works if inserting a single Map row at once. Is there something I can modify inside of the trigger to allow for multiple rows being inserted?

This is because your trigger has a MAJOR flaw. It assumes only a single row will ever be inserted. Triggers fire once per operation, not once per row. You could just add a where clause in your insert and get rid of the IF statement. — Sean Lange
– Sean Lange, Commented Sep 10, 2018 at 19:37
@SeanLange triggers do not fire once per operation as a rule of thumb. This is true for SQL Server, however not for every other database (eg: Postgresql). — Kamil Gosciminski
– Kamil Gosciminski, Commented Sep 10, 2018 at 19:39

Kamil Gosciminski · Accepted Answer · 2018-09-10 19:42:03Z

3

This is because this statement:

(SELECT Lat FROM inserted) IS NOT NULL

Expects one value to compare with the condition.

What you could do is rewrite the logic by getting rid of IF statement and instead incorporate the check within the INSERT statement itself:

CREATE TRIGGER [dbo].[MapViewInsertInstead] ON [dbo].[MapView]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO [dbo].[Map] (HouseId, HousePoint)
    SELECT HouseId
    geography::Point(Lat, Long, 4326)
FROM inserted
WHERE Lat IS NOT NULL
  AND Long IS NOT NULL
END

answered Sep 10, 2018 at 19:42

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Valuator Over a year ago

It was that simple. Previously we never even had a way to insert more than one at a time so this wasn't an issue.

David Browne - Microsoft · Accepted Answer · 2018-09-10 19:45:51Z

3

You probably don't want an invalid INSERT to silently fail. So just skip the null check and the geography::Point function will raise an error if someone attempts to insert a NULL. EG

CREATE OR ALTER TRIGGER [dbo].[MapViewInsertInstead] ON [dbo].[MapView]
INSTEAD OF INSERT
AS
BEGIN
    set nocount on;

    INSERT INTO [dbo].[Map] (HouseId, HousePoint)
        SELECT HouseId, geography::Point(Lat, Long, 4326)
    FROM inserted;
END

answered Sep 10, 2018 at 19:45

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Collectives™ on Stack Overflow

Error inserting multiple rows into View with INSTEAD OF trigger

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related