尝试使用 PERSISTED COMPUTED 列:http://msdn.microsoft.com/en-us/library/ms191250.aspx http://msdn.microsoft.com/en-us/library/ms191250.aspx(这里可能需要一个外部演员)
Location_Geography AS (
CASE
WHEN Location_Latitude <> 0 AND Location_Longitude <> 0 THEN
geography::STGeomFromText('POINT(' + CONVERT(VARCHAR(100),Location_Longitude) + ' ' + CONVERT(VARCHAR(100),Location_Latitude) + ')',4326)
ELSE
NULL
END
)
这避免了必须制作具有几乎相同总体效果的触发器。
触发器:http://msdn.microsoft.com/en-us/library/ms191524.aspx http://msdn.microsoft.com/en-us/library/ms191524.aspx
您的触发器可能会修改为:
CREATE TRIGGER Update_Geography
ON [People]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE [People]
SET
Location_Geography = geography::STGeomFromText('POINT(' + CONVERT(VARCHAR(100),Location_Longitude) + ' ' + CONVERT(VARCHAR(100),Location_Latitude) + ')',4326)
WHERE (UPDATE(Location_Longitude) OR UPDATE(Location_Latitude))
AND Id IN (SELECT ins.Id FROM inserted ins)
END
END
以下是显示手动列和计算列的示例:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SO5572806]')
AND type IN (N'U') )
DROP TABLE [dbo].[SO5572806]
GO
CREATE TABLE SO5572806
(
lo DECIMAL(8, 5) NOT NULL
,la DECIMAL(8, 5) NOT NULL
,man GEOGRAPHY NULL
,calc AS (CONVERT(GEOGRAPHY, CASE WHEN la <> 0
AND lo <> 0
THEN GEOGRAPHY::STGeomFromText('POINT('
+ CONVERT(VARCHAR, lo)
+ ' '
+ CONVERT(VARCHAR, la)
+ ')', 4326)
ELSE NULL
END))
)
GO
INSERT INTO dbo.SO5572806
(lo, la)
VALUES (0, 0),
(-90, 30)
UPDATE dbo.SO5572806
SET man = GEOGRAPHY::STGeomFromText('POINT(' + CONVERT(VARCHAR, lo) + ' '
+ CONVERT(VARCHAR, la) + ')', 4326)
WHERE lo <> 0
AND la <> 0
SELECT *
FROM dbo.SO5572806