How To Do Trigger Which Create A Point After Insert Longitude And Latitude?

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

Solution 1:

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

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 According to your comment I replaced idGroupVehicle by idVehicle in the join.

Solution 2:

Your trigger has fundamental flaws:

• It does not take into account that there might be multiple (or no) rows in the statement
• It is not referencing the inserted pseudo-table, so the data it is pulling is from a random row
• It is writing back to the whole table, as there is no where filter

Instead a trigger would look like this:

create trigger [dbo].[t_points]
on [dbo].[Vehicle]
after insert
as 

SET NOCOUNT ON;

UPDATE v
SET locationVehicle = geography::Point(i.latitude, i.longitude,4326)
FROM inserted i
JOIN v ON v.id = i.id;  -- or whatever the primary key is

GO

---

However:

A much better solution is a simple computed column:

ALTER TABLE Vehicle
    ADD locationVehicle AS (geography::Point(latitude, longitude, 4326));

Solution 3:

Your issue is, I believe, the way you are expecting triggers to work.

The error you are receiving is nothing to to with the trigger itself, you are declaring a variable and then trying to assign that variable a value from a sub query.

select v.latitude from Vehicle v - this does exactly what you might expect it to, being inside a "trigger" makes no difference, it will return a set of values, all rows in fact from table vehicle - hence the error "returned more than one row" - assigning a value to a variable expects a single value.

To do this with a trigger there is no need to assign values to any variables. What makes a trigger special is it makes available two virtual tables called inserted and deleted that contain rows affected only by the data modification that happened. Your insert could have inserted a single row or many, or none, and only these row(s) will be in the inserted table.

To update your column only for rows affected you would do the following

update v set
    v.LocationVehicle=geography::Point(v.latitude,v.longitude,4326)
from inserted i join Vehicle v on v.idGroupVehicle=i.idGroupVehicle

Having said that, you could try using a computed column instead which would accomplish the same thing

Alter table Vehicle add LocationVehicle as geography::Point(latitude,longitude,4326)