SQL Trigger updating after

Question

I have a question regarding triggers and i'm not sure if this is the proper way to do this. But I have a table called 'tblWell' with id, welllonglat (geography) and welllong and welllat which are decimal value types. I'm using lightswitch to enter data which doesn't support the geography value type. So they enter long and lat and insert. Now I need to update the welllonglat (geography) value type field after the newly record has been inserted so I thought a trigger would work. I'm not that great with triggers so wondering how I get the value of welllong and welllat if the newly inserted record and then update that record welllonglat using this function. tblWell.WellLongLat=geography::Point(tblWell.WellLat, tblWell.WellLong, 4326)

right now says parameter 1 is null.. how do I get those values.. or how else can I do this? code is below thanks

Alter TRIGGER trgAfterInsert ON  [tblWell]
After INSERT
AS  
begin
set nocount on
DECLARE @long decimal
DECLARE @lat decimal

    update
        [tblWell]
           set tblWell.WellLongLat=geography::Point(tblWell.WellLat, tblWell.WellLong, 4326)
      FROM 
        tblWell
end

go

according to answer below. the following code should work but I still get a null error

Alter TRIGGER trgAfterInsert ON  [tblWell]
After INSERT
AS  

Begin
set nocount on
UPDATE A
SET WellLongLat = geography::Point(B.WellLat, B.WellLong, 4326)
FROM [tblWell] A
INNER JOIN INSERTED B
    ON A.WellUID = B.WellUID
    where B.WellLat IS NOT NULL and B.WellLong IS NOT NULL

END

go

Here is the table structure

CREATE TABLE [dbo].[tblWell](
[WellUID] [int] IDENTITY(1,1) NOT NULL,
[WellLocation] [varchar](500) NULL,
[WellLongLat] [geography] NULL,
[WarehouseID] [int] NULL,
[ProgrammedCost] [money] NULL,
[ProductionTypeID] [int] NULL,
[WellTypeID] [int] NULL,
[OperatorID] [int] NULL,
[WellLong] [decimal](9, 6) NULL,
[WellLat] [decimal](9, 6) NULL,
CONSTRAINT [PK_tblWell] PRIMARY KEY CLUSTERED 
(
[WellUID] ASC
)

Answer 1

You need to use the INSERTED pseudo table:

ALTER TRIGGER trgAfterInsert ON [tblWell] AFTER INSERT 
AS
BEGIN 
SET NOCOUNT ON 

UPDATE A
SET WellLongLat = geography::Point(B.WellLat, B.WellLong, 4326)
FROM [tblWell] A
INNER JOIN INSERTED B
    ON A.id = B.id

END

If you want to check the existence of WellLat and WellLong, you can use a WHERE:

ALTER TRIGGER trgAfterInsert ON [tblWell] AFTER INSERT 
AS
BEGIN 

SET NOCOUNT ON 

IF EXISTS(SELECT 1 FROM INSERTED WHERE WellLat IS NOT NULL 
                                 AND WellLong IS NOT NULL)
BEGIN
    UPDATE A
    SET WellLongLat = geography::Point(B.WellLat, B.WellLong, 4326)
    FROM [tblWell] A
    INNER JOIN INSERTED B
        ON A.id = B.id
    WHERE B.WellLat IS NOT NULL AND B.WellLong IS NOT NULL
END

END

Answer 2

Lamak's answer is good. here's why yours does not work. You are trying to set values from tbWell. so what your query is doing is going through every row and setting WellLongLat according to that row's WellLat and WellLong values. I imagine you have a null value for WellLat or WellLong somewhere thus the null error.

Doing as Lamak demonstrates you get the values of the just inserted row out of the INSERTED table and then match them to the row just inserted based on the id column (which should be unique). thus you end up updating one row (if id is unique) and only row and using the new values. now technically I suppose you don't HAVE to use inserted in the Set part of the update, but its better form to do so.

small note: your declares do nothing in the trigger because you never use them so no point to them