在同一表上触发INSERT和UPDATE [英] Trigger for INSERT and UPDATE on same table
问题描述
众所周知,实体框架无法保存地理位置数据。所以我的想法是,在模型中将经度和纬度指定为十进制。在执行了用于创建表的SQL脚本之后,我将启动另一个用于添加地理位置列的脚本。然后,我想通过触发器在每个INSERT或UPDATE(经度和纬度)上更新此列。以下触发器可以吗,还是不好?我问是因为我对触发器不是很熟悉,但是它现在可以使用。
as all of us know, entity framework can't hold geography data. So my idea was, to specify the longitude and latitude as decimal in my model. After executing the SQL script for creating the tables I would start another script for adding a geography column. Then I would like to update this column on every INSERT or UPDATE (on longitude and latitude) by a trigger. Is the following trigger okay, or is it something bad? I'm asking because I'm not very familiar with trigger, but it works for now.
CREATE TRIGGER Update_Geography
ON [People]
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @longitude DECIMAL(8, 5), @latitude DECIMAL(8, 5)
SET @longitude = (SELECT ins.Location_Longitude FROM inserted ins)
SET @latitude = (SELECT ins.Location_Latitude FROM inserted ins)
IF (@longitude != 0 AND @latitude != 0)
BEGIN
UPDATE [People]
SET
Location_Geography = geography::STGeomFromText('POINT(' + CONVERT(VARCHAR(100),@longitude) + ' ' + CONVERT(VARCHAR(100),@latitude) + ')',4326)
WHERE
Id = (SELECT ins.Id FROM inserted ins)
END
END
如果有人可以帮助我,我将非常高兴。
Would be glad if someone could help me.
致谢
编辑:
脚本如下所示:
ALTER TABLE [People] ADD Location_Geography AS (
CONVERT(GEOGRAPHY, CASE
WHEN Location_Latitude 0 AND Location_Longitude 0 THEN
geography::STGeomFromText('POINT(' + CONVERT(VARCHAR, Location_Longitude) + ' ' + CONVERT(VARCHAR, Location_Latitude) + ')',4326)
ELSE
NULL
END
)
)
可以,但无法查询该列:/
Thx
works but can't query that column :/ Thx
推荐答案
尝试使用持久性计算列: http://msdn.microsoft.com/en-us/library/ms191250.aspx (可能需要在此处进行外部转换)
Try a PERSISTED COMPUTED column: http://msdn.microsoft.com/en-us/library/ms191250.aspx (might need an outer cast here)
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
)
这避免了触发具有几乎相同的总体效果。
This avoids having to make a trigger with pretty much the same overall effect.
触发器: http://msdn.microsoft.com/en-us/library/ms191524.aspx
您的触发器可能是修改为:
Your trigger could probably be modified as:
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
以下示例显示了手动列和计算列:
Here's an example showing both manual and calc'ed columns:
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
这篇关于在同一表上触发INSERT和UPDATE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!