插入经纬度后如何触发创建点? [英] How to do trigger which create a point after insert longitude and latitude?

问题描述

我想创建触发器，在将经度和纬度插入表 Vehicle 后，从经度纬度创建点.

I want to create trigger which after insert longitude and latitude to table Vehicle create point from longitude latitude.

我的触发器看起来是这样

My trigger looks that

create trigger [dbo].[t_points]
on [dbo].[Vehicle]
after insert
as 
begin
SET NOCOUNT ON;
    DECLARE @Point AS GEOGRAPHY
    DECLARE @Longitude AS FLOAT
    DECLARE @Latitude AS FLOAT
        set @latitude = (select v.latitude from Vehicle v)
        set @longitude =    (select v.longitude from Vehicle v)

    if @latitude is not null and @Longitude is not null 
    begin
    set @Point = geography::Point(@latitude,@longitude,4326)
    update Vehicle set locationVehicle = @Point
    end
end
GO

但是当我插入到我的表值时:

but when I insert to my table values:

insert into Vehicle
  (idGroupVehicle, brand, model, maxRange, weight, maxSpeed, pricePerSale,
   latitude, longitude, locationName)
  values ('1', 'Xiaomi', 'Mijia M365 PRO', '45', '14', '1900', '25',
   '19.905203', '50.071597', 'Piastowska 49 Kraków')

我有错误

子查询返回了 1 个以上的值.当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的.

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

有人能解释一下为什么我有这个错误而且我不能将这个值插入到我的表中，我的触发器只检查纬度和经度不为空创建一个点.

Can someone explain me why I have this error and I can't insert to my table this value my trigger only check is latitude and longitude is not null create a point.

推荐答案

您必须查询 Inserted 伪表以获取插入触发器内的插入行.该表可能返回多行.您可以像这样更新位置列，而不是遍历行

You must query the Inserted pseudo table to get the inserted rows inside an insert trigger. This table might return several rows. Instead of looping through the rows, you can update the location column like this

CREATE TRIGGER [dbo].[t_points]
    ON [dbo].[Vehicle]
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE v
    SET
        v.locationVehicle = geography::Point(i.latitude, i.longitude, 4326)
    FROM
        dbo.Vehicle v
        INNER JOIN inserted i
            ON v.idVehicle = i.idVehicle
    WHERE
        i.latitude IS NOT NULL AND
        i.longitude IS NOT NULL

END

假设 idGroupVehicle 是表的主键.如果不是，则用主键替换(每个表都应该有一个主键).

Assuming that idGroupVehicle is the primary key of the table. If it is not, replace it by the primary key (every table should have a primary key).

UPDATE 根据您的评论，我在连接中将 idGroupVehicle 替换为 idVehicle.

UPDATE According to your comment I replaced idGroupVehicle by idVehicle in the join.

这篇关于插入经纬度后如何触发创建点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！