Example of using a trigger in SQL Server 2008 to test spatial relationship

spatial_trigger_sqlserver.sql

USE [MyDB]
GO
/****** Object:  Trigger [dbo].[Locations_Insert]    Script Date: 11/03/2012 08:50:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* This trigger makes sure that the data being inserted into
   the Locations falls within a specific polygon stored in another table.  
   If it doesn't, the transaction is rolled back. 

   The Locations table in this example does not contain a spatial data type, only Latitude and Longitude columns
*/

ALTER TRIGGER [dbo].[Locations_Insert] ON [dbo].[Locations]
INSTEAD OF INSERT
AS

DECLARE @lat float
DECLARE @lon float
--STContains only supported by Geometry, be sure to convert Geography if needed
DECLARE @pt geometry
DECLARE @count int

SET @lat = (SELECT Latitude FROM INSERTED)
SET @lon = (SELECT Longitude FROM INSERTED)
--This example assumes latitude, longitude and polygon table are all WGS84
SET @pt = geometry::Point(@lon, @lat, 4326)
--GID is PK of polygon table, geom is the geometry column
SELECT @count = COUNT(GID) FROM my_polygon_table WHERE geom.STContains(@pt) = 1
IF (@count < 1)
BEGIN
	ROLLBACK TRANSACTION
END
ELSE
BEGIN
     INSERT INTO Locations (Longitude, Latitude) SELECT Longitude,Latitude FROM Inserted
END