Solved

Triggers - Causing "Cannot Add Object" in GIS software

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

688 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