Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Triggers - Causing "Cannot Add Object" in GIS software

Posted on 2013-01-11
4
358 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

829 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