November 13, 2007 at 6:51 am
Got a problem with getting a trigger to work properly. I have a table (Task) with coordinates in UTM and want to make a trigger that converts UTM to lat/lon. I got the UTM2LL table-valued function working but can't make it work in the trigger.
Table:
CREATE TABLE [dbo].[Task](
[TaskID] [int] IDENTITY(1,1) NOT NULL,
[UTMZone] [int] NULL,
[CoordinateEast] [int] NULL,
[CoordinateNorth] [int] NULL,
[Latitude] [float] NULL,
[Longitude] [float] NULL,
CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED
(
[TaskID] ASC
)
UTM2LL function:
CREATE FUNCTION [dbo].[UTM2LL](
@iZoneNumber int,
@UTMNorthing int,
@UTMEasting int)
-- table variable that will contain values
RETURNS @tablevalues TABLE (
lat float,
lon float)
AS
BEGIN
...
END
Trigger (working on only 1 row):
CREATE trigger [dbo].[task_t]
ON [dbo].[Task]
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @id INT;
DECLARE @Zone INT;
DECLARE @east INT;
DECLARE @north INT;
DECLARE @lat float;
DECLARE @lon float;
IF (@@ROWCOUNT > 0)
BEGIN
SELECT @id=c.taskId,@Zone=c.zone,@east=c.CoordinateEast*100,@north=c.CoordinateNorth*100
FROM Task c JOIN inserted i ON c.TaskId=i.TaskId;
SELECT @lat=lat, @lon=lon FROM UTM2LL(@Zone,@north,@east);
UPDATE Task set latitude=@lat, longitude=@lon WHERE TaskId=@id;
END
END
Since this trigger only works on single-row I guess the UPDATE-part has to be joined with 'inserted'. My problem is how to get lat and lon from the function combined in the join.
Something like this?
UPDATE t set t.latitude=u.lat, t.longitude=u.lon
FROM Task t JOIN inserted i ON c.taskid=i.taskid
JOIN UTM2LL(t.zone,t.CoordinateNorth*100,t.CoordinateEast*100) u;
Anyone?
November 13, 2007 at 7:30 am
You need to use the CROSS APPLY predicate on the function call - that should get you what you want.
CROSS APPLY allows you to create an "implicit join" between a TVF and the table it's being called against.
Simply replace the last JOIN with CROSS APPLY and you should be there:
UPDATE t set t.latitude=u.lat, t.longitude=u.lon
FROM Task t JOIN inserted i ON c.taskid=i.taskid
CROSS APPLY
UTM2LL(t.zone,t.CoordinateNorth*100,t.CoordinateEast*100) u;
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 13, 2007 at 7:40 am
Thank you Matt. It works fine and I've learned something new.
November 14, 2007 at 9:21 am
Very cool. I've never seen the CROSS APPLY before. Is this new to 2005? Also I have to say the BOL help on it is one of the better ones also.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply