Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following trigger in my database:

CREATE TRIGGER dbo.triggerGeocodedAddressUpdate ON dbo.Party AFTER UPDATE AS
    IF UPDATE(Latitude)
        UPDATE pt
        SET pt.GeocodedAddress = geography::Point(i.Latitude, i.Longitude, 4326)
        FROM dbo.Party AS pt INNER JOIN inserted AS i ON i.PartyId = pt.PartyId
        WHERE (i.Latitude IS NOT NULL AND i.Longitude IS NOT NULL);
        RETURN
    IF UPDATE(Longitude)
        UPDATE pt
        SET pt.GeocodedAddress = geography::Point(i.Latitude, i.Longitude, 4326)
        FROM dbo.Party AS pt INNER JOIN inserted AS i ON i.PartyId = pt.PartyId
        WHERE (i.Latitude IS NOT NULL AND i.Longitude IS NOT NULL);
        RETURN
GO

But every time I try to update a row in the Party table, I get an error, even if I don't change the Latitude or Longitude fields.

The error is:

geography::Point' failed because parameter 1 is not allowed to be null.

Any ideas? I'm pretty new to triggers, so I'm sorry if it's something simple and obvious.

share|improve this question
add comment

2 Answers 2

Try this one. I have moved the conditional update into a single statement because the action you were taking was the same for both conditions. Also I have altered the way that you join to the INSERTED table so that it performs the filter pre-join:

CREATE TRIGGER dbo.triggerGeocodedAddressUpdate ON dbo.Party AFTER UPDATE AS
IF UPDATE(Latitude) OR UPDATE(Longitude)
    UPDATE pt
    SET pt.GeocodedAddress = geography::Point(i.Latitude, i.Longitude, 4326)
    FROM dbo.Party AS pt 
    JOIN 
    (
        SELECT PartyId, Latitude, Longitude
        FROM inserted
        WHERE (Latitude IS NOT NULL AND Longitude IS NOT NULL)
    ) AS i ON i.PartyId = pt.PartyId;
    RETURN
GO

I hope this helps you.

share|improve this answer
    
Thanks... it didn't totally solve the problem, but it was helpful in simplifying the trigger. I posted an answer with the solution that worked for me. –  Alex Schimp Aug 23 '12 at 14:04
    
No problem - glad it gave you a nudge in the right direction. You should mark your own as the answer ;-) –  Mr.Brownstone Aug 23 '12 at 15:21
add comment
up vote 1 down vote accepted

It turned out to be that I needed to convert to the geography type differently. Building off of @Mr.Brownstone's answer (which was helpful since it simplified the trigger):

CREATE TRIGGER dbo.triggerGeocodedAddressUpdate ON dbo.Party AFTER UPDATE AS
IF UPDATE(Latitude) OR UPDATE(Longitude)
    UPDATE pt
    SET pt.GeocodedAddress = geography::STPointFromText('POINT(' + CAST(i.[Longitude] AS VARCHAR(10)) + ' ' + CAST(i.[Latitude] AS VARCHAR(10)) + ')', 4326)
    FROM dbo.Party AS pt 
    JOIN 
    (
        SELECT PartyId, Latitude, Longitude
        FROM inserted
        WHERE (Latitude IS NOT NULL AND Longitude IS NOT NULL)
    ) AS i ON i.PartyId = pt.PartyId;
    RETURN
GO

The main thing was that this line

SET pt.GeocodedAddress = geography::Point(i.Latitude, i.Longitude, 4326)

needed to be changed to this:

SET pt.GeocodedAddress = geography::STPointFromText('POINT(' + CAST(i.[Longitude] AS VARCHAR(10)) + ' ' + CAST(i.[Latitude] AS VARCHAR(10)) + ')', 4326)

For some reason, this works.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.