Solved

Triggers - Causing "Cannot Add Object" in GIS software

Posted on 2013-01-11
4
349 Views
Last Modified: 2013-01-24
AS soon as i enable this Trigger i am unable to insert or edit an object in that dataset inside my GIs Software.
USE [CNTY_GIS]
GO
/****** Object:  Trigger [dbo].[FName_AName_Num_Lastdate_Cont_Segment]    Script Date: 01/11/2013 14:03:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[FName_AName_Num_Lastdate_Cont_Segment] ON [dbo].[Segment]
    AFTER INSERT, DELETE, UPDATE
AS
    BEGIN
        SET NOCOUNT ON;

        UPDATE  s
        SET     GIS_lastUpdate_Date = CURRENT_TIMESTAMP
        FROM    Segment s
                INNER JOIN INSERTED i ON s.OID = i.OID

        UPDATE  dbo.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

Open in new window

But this one will allow me to edit and insert records in the dataset
USE [CNTY_GIS]
GO
/****** Object:  Trigger [dbo].[Changes_Segment]    Script Date: 01/11/2013 14:03:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Changes_Segment] 
ON  [dbo].[Segment]
FOR UPDATE 
AS 


IF UPDATE(L_F_ADD) OR UPDATE(L_T_ADD) OR UPDATE(R_T_ADD)OR UPDATE(R_F_ADD) OR UPDATE(ROADJUR)
    INSERT INTO dbo.Segment_History
    SELECT * FROM inserted

Open in new window

Thoughts please
0
Comment
Question by:PtboGiser
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
LIONKING earned 500 total points
ID: 38768548
Why do you have "AFTER DELETE" in your first trigger when nothing in the code will execute on deletes?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38769272
Your software must not like the fact you are updating the same table the trigger is on or that you are doing it twice.  Without all the pieces to test on we cannot run scenarios to narrow it down.
What precise error message are you getting and is it from SQL Server or from your software?
0
 

Author Comment

by:PtboGiser
ID: 38775787
Valid point the software may not like updateing the current table with the use of triggers i will test in some other spots to confirm.
0
 

Author Comment

by:PtboGiser
ID: 38779199
The 2 triggers are written differently and i'm not sure what the difference is, can anyone help explain it to me?
the first one which is not allowing changes to the table when trying to edit the table with this trigger enabled i receive no error message but the values do not update. when i check on the record it is the same as i always way no changes.
first trigger write as follows
AFTER INSERT, DELETE, UPDATE
AS
    BEGIN
        SET NOCOUNT ON;

        UPDATE  s
        SET     GIS_lastUpdate_Date = CURRENT_TIMESTAMP

Open in new window


Second trigger is written. different and works when i change the Record values.
FOR UPDATE 
AS 


IF UPDATE(L_F_ADD) OR UPDATE(L_T_ADD) OR UPDATE(R_T_ADD)OR UPDATE(R_F_ADD) OR UPDATE(ROADJUR)
    INSERT INTO dbo.Segment_History
    SELECT * FROM inserted

Open in new window


The For Update is different.
Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question