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