TRigger no fire for Geography type

Question

Dear all,

I have an sql table define as below :

CREATE TABLE [dbo].[PropertyGeoLocalisation](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[PropertyId] [int] NOT NULL,
	[Longitude] [float] NULL,
	[Latitude] [float] NULL,
	[Easting] [int] NULL,
	[Northing] [int] NULL,
	[Bing_Longitude] [float] NULL,
	[Bing_Latitude] [float] NULL,
	[CenterPoint] [geography] NULL,
 CONSTRAINT [PK_PropertyGeoLocalisation] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[PropertyGeoLocalisation]  WITH CHECK ADD  CONSTRAINT [FK_PropertyGeoLocalisation_Vebra_PropertyDetails] FOREIGN KEY([PropertyId])
REFERENCES [dbo].[Vebra_PropertyDetails] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[PropertyGeoLocalisation] CHECK CONSTRAINT [FK_PropertyGeoLocalisation_Vebra_PropertyDetails]
GO

Then I have a trigger which should calculate the CenterPoint field based on the following :

ALTER TRIGGER [dbo].[SetGeographyCenterPoint]
   ON  [dbo].[PropertyGeoLocalisation] 
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
     UPDATE [RealEstateAgent].[dbo].[PropertyGeoLocalisation]
     SET [CenterPoint] = GEOGRAPHY::STGeomFromText('POINT('+Str([Longitude], 10, 5)+' ' +Str([Latitude],10,5)+')', 4326)

END

When I insert an item manually in my table, the trigger seems to not fire and update the column.

I am using SQL server 2012, any idea what could be wrong ?
Under SQL server 2008 it was working fine

Thanks for tips

regards

Answer 1

Works under 2014 and 2014 with 2012 compatibility level.

Is the trigger disabled?

Try this script:

USE RealEstateAgent
go

select Name, case when is_disabled = 0 then 'Enabled' else 'Disabled' end
from sys.triggers 
where name = 'SetGeographyCenterPoint'

---

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

Answer 2

I have try what you suggest with no luck.

When I edit one a the field directly in the table, the CenterPoint field remains null.

Any idea ?

What do you mean by "Works under 2014 and 2014 with 2012 compatibility level."

regards

Answer 3

What do you mean by "Works under 2014 and 2014 with 2012 compatibility level."

What I mean is that I have SQL Server 2014 installed. I attempted your code and it works fine in SQL Server 2014. I set the database compatibility level to SQL Server 2012 and that also worked. I did not try this in 2012.

One HUGE problem I see with your query is that you have no WHERE clause on your update and you're not joining with the INSERTED table. Do you want to update EVERY row in the table EVERY time you insert a single row? That seems like a performance issue...

I would rethink this trigger a bit and possibly remove it all together. If CenterPoint is always a calculation on Long and Lat then why not do a calculated field? https://msdn.microsoft.com/en-us/library/ms186241(v=sql.110).aspx

---

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

Answer 4

Hello,

Thnaks for your reply.

In fact the center point should be calculated only for the row which is updated.

Originally the database was from SQL server2008 which I guess did dot have calcualted field at that time ( not sure). I have never user such calculated field.

How it will be then in my scenario to change the trigger to calcuated field ?

regards

Answer 5

Here's an example (in the original question) of persisted computed spatial column. https://social.technet.microsoft.com/Forums/sqlserver/en-US/2e339d41-cbcd-4c8f-9613-ee50f1f859ae/sql-server-2012-not-using-spatial-index-on-computed-column?forum=sqlspatial The column does not necessarily have to be persisted. If you do want to index the column, ensure that the problem reported there has been resolved (it may have something to do with the column being nullable) on your SQL Server version.