Can't Add Object - GIS Software
Any Reason when this Trigger is enabled i'm not able to update the Dataset using my GIS(Manifold GIS) Package?
ALTER TRIGGER [dbo].[FName_AName_Num_Las
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE Segment
SET Street_Full_Name = st.Street_Full_Name,
AnnoName = st.AnnoName
FROM dbo.Segment s
INNER JOIN INSERTED i ON s.OID = i.OID
INNER JOIN dbo.Street st ON s.Street_ID = st.Street_ID
END
ALTER TRIGGER [dbo].[FName_AName_Num_Las
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE Segment
SET Street_Full_Name = st.Street_Full_Name,
AnnoName = st.AnnoName
FROM dbo.Segment s
INNER JOIN INSERTED i ON s.OID = i.OID
INNER JOIN dbo.Street st ON s.Street_ID = st.Street_ID
END
Check if the trigger is disabled.
if it is disabled enable the trigger and try.
ALTER TABLE Segment ENABLE TRIGGER FName_AName_Num_Lastdate_C
SELECT name, is_disabled FROM sys.triggersif it is disabled enable the trigger and try.
ALTER TABLE Segment ENABLE TRIGGER FName_AName_Num_Lastdate_C
ASKER
Trigger is enable and once it is. We get an error thrown, "Cannot add Object" in our GIS Softwares (Tried in both Manifold GIS and Autocad 2013)
Hi,
Not sure if SQL exceptions work in triggers, but I suggest you try that, with the catch storing the necessary info in another table for further analysis.
HTH
David
Not sure if SQL exceptions work in triggers, but I suggest you try that, with the catch storing the necessary info in another table for further analysis.
HTH
David
Simillar problem posted, seems updating same table in trigger is causing the error in manifold GIS.
https://www.experts-exchange.com/questions/27992700/Triggers-Causing-Cannot-Add-Object-in-GIS-software.html
https://www.experts-exchange.com/questions/27992700/Triggers-Causing-Cannot-Add-Object-in-GIS-software.html
excuse me, i didn't observe, the last question also posted by you only.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Appari, New code does not make a difference still wont let me update.
ASKER
So i'ts something to do with nested triggers When i run the following then enable the trigger it works perfectly Street names update properly
The [Street_Full_Name] column is built using a Computed Column in the Street table. Looks like this could be the issue?
USE CNTY_GIS ;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'nested triggers', 0 ;
GO
RECONFIGURE;
GOThe [Street_Full_Name] column is built using a Computed Column in the Street table. Looks like this could be the issue?
ASKER
Is there a way to set the computed column to complete first then run a trigger off that completed computed column? or is this type of thing supported?
if [Street_Full_Name] is a computed column why are you updating the column?
what is the need to update that column? try removing [Street_Full_Name] from the update set list and see how it behaves.
what is the need to update that column? try removing [Street_Full_Name] from the update set list and see how it behaves.
Also if possible can you post table create script, both Street and Segment tables.
ASKER
[Street_Full_Name] is computed in the Street Table. I am running a Trigger to update [Street_Full_Name] in the Segment table from street.
[Street_Full_Name] the column is in both tables. Not the best DB design i know but its the way we want it.
[Street_Full_Name] the column is in both tables. Not the best DB design i know but its the way we want it.
ASKER
the hardest part i have understanding is this why will code written this way work on the
[Street_Full_Name] column work
ALTER TRIGGER [dbo].[FullName_Address]
ON [dbo].[Address]
AFTER INSERT,delete, update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
update dbo.Address
set dbo.Address.Street_Full_Na
from dbo.Address inner join dbo.Segment on dbo.Address.Segment_ID = dbo.Segment.Segment_ID
END
[Street_Full_Name] column work
ALTER TRIGGER [dbo].[FullName_Address]
ON [dbo].[Address]
AFTER INSERT,delete, update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
update dbo.Address
set dbo.Address.Street_Full_Na
from dbo.Address inner join dbo.Segment on dbo.Address.Segment_ID = dbo.Segment.Segment_ID
END
ASKER
But code written like this Won't let me have the trigger enabled. As this is supposed to be written as better code format.
UPDATE Segment
SET Street_Full_Name = st.Street_Full_Name,
AnnoName = st.AnnoName
FROM dbo.Segment s
INNER JOIN INSERTED i ON s.OID = i.OID
INNER JOIN dbo.Street st ON s.Street_ID = st.Street_ID
Can anyone answer this
UPDATE Segment
SET Street_Full_Name = st.Street_Full_Name,
AnnoName = st.AnnoName
FROM dbo.Segment s
INNER JOIN INSERTED i ON s.OID = i.OID
INNER JOIN dbo.Street st ON s.Street_ID = st.Street_ID
Can anyone answer this
Hi,
Can only insert/update one table at a time,
Try this:
UPDATE s -- dbo.segment is aliased as s below
SET Street_Full_Name = st.Street_Full_Name,
AnnoName = st.AnnoName
FROM dbo.Segment s
INNER JOIN INSERTED i ON s.OID = i.OID
INNER JOIN dbo.Street st ON s.Street_ID = st.Street_ID
HTH
David
Can only insert/update one table at a time,
Try this:
UPDATE s -- dbo.segment is aliased as s below
SET Street_Full_Name = st.Street_Full_Name,
AnnoName = st.AnnoName
FROM dbo.Segment s
INNER JOIN INSERTED i ON s.OID = i.OID
INNER JOIN dbo.Street st ON s.Street_ID = st.Street_ID
HTH
David
more information with sample data will help. Do you mean your update is throwing error or the updated data is not reflected?