Solved

Triggers - Causing "Cannot Add Object" in GIS software

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to find duplicates in SQL Server 3 21
Scheduling Jobs for Execution: 4 14
Sql query for filter 12 21
SQL Maintenance Plan 3 14
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now