Total novice at triggers... all the docs don't bother with beginner stuff.
I just want to update the row(s) that has/have been updated. My trigger below updates the entire table. The trigger below just tests two columns for changes.
How do I restrict this update trigger to the update only the updated rows and not the entire table?
ALTER TRIGGER [dbo].[geog_update] ON [dbo].[Site]
FOR UPDATE
AS
SET NOCOUNT ON
IF (UPDATE(Latitude) OR UPDATE(Longitude))
BEGIN
UPDATE Site
SET geog = geography::Point([Latitude], [Longitude], 4326)
WHERE Latitude is not null and Longitude is not null
END
Can I use the same trigger for inserted rows by just using FOR UPDATE, INSERT? Probably not since the IF checks for UPDATE() on a column, unless INSERT implies UPDATE.
Ok first, you never under any circumstances design a trigger to update only one row in SQL Server. You design it to update the rows that were inserted, deleted or updated. Triggers operate on batches and you cannot assume that you will never change more than one record in code that hits the database.
You do that by joining to one of two pseudotables that are available only in triggers, inserted or deleted. Inserted contains the new records or the values after an update, delted contains the values for the records deleted or the values before an update occurred.
You could try something like:
ALTER TRIGGER [dbo].[geog_update] ON [dbo].[Site]
FOR UPDATE
AS
SET NOCOUNT ON
UPDATE S
SET geog = geography::Point(i.[Latitude], i.[Longitude], 4326)
FROM Site s
JOIN Inserted I on s.id = i.id
WHERE Latitude is not null and Longitude is not null
Here is the final UPDATE trigger. I made an INSERT trigger that is almost identical.
CREATE TRIGGER [dbo].[geog_update] ON [dbo].[Site]
FOR UPDATE
AS
SET NOCOUNT ON
IF (UPDATE(Latitude) OR UPDATE(Longitude))
BEGIN
UPDATE t1
SET t1.geog = geography::Point(t1.Latitude, t1.Longitude, 4326)
FROM Site AS t1 INNER JOIN Inserted AS t2 ON t1.Site_ID = t2.Site_ID
WHERE (t1.Latitude IS NOT NULL)
AND (t1.Longitude IS NOT NULL)
AND (t1.Record_Archive_Date IS NULL)
END
