?
Solved

Triggers - Causing "Cannot Add Object" in GIS software

Posted on 2013-01-11
4
Medium Priority
?
391 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
[X]
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
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
LIONKING earned 1000 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 27

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

762 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