Solved

Trigger Looks good?

Posted on 2013-01-15
6
204 Views
Last Modified: 2013-01-21
ALTER TRIGGER [dbo] [FName_AName_Num_Lastdate_Cont_Segment] ON [dbo].[Segment]
    AFTER INSERT, UPDATE
AS
    BEGIN
        SET NOCOUNT ON;

       UPDATE 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

When i have this Trigger enabled my table record will not update. I have tried 2 different front end GIS Softwares both with the same result. Nothing.

Once i disable the Trigger the update works fine.

Reasons?  Thoughts? as a novice i'm unsure what to do next.

I have one other enabled Trigger that is working. I do belive it needs tweaked as i want ot to only pull the needed records from the Inserted table not all them.
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

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
  • 2
  • 2
6 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 38779653
The trigger you created may fail on batch inserts/updates - did you tried it in SSMS query to see what error message you are getting?
0
 
LVL 40

Expert Comment

by:lcohan
ID: 38779669
Also in my opinion what you are trying to do is wrong just because triggers are hiden objects and is hard sometimes to control the impact. You should directly INSER or
UPDATE the dable with the name/address from the lookup tables based on the ID and NOT to do that in a (hidden SQL code object) trigger.
0
 

Author Comment

by:PtboGiser
ID: 38779763
If i am to use a normal Update Query it would not be instant though, I would have to run that query frequently. I'm thinking this is the perfect scenerio for a Trigger. Maybe i'm minunderstanding.
My thoughts are
If i change a street name in the Street Table the trigger would fire "After Update" and then my segment table would be updated. as well a different trigger would fire and update the streetname column in my Parcel Table as well.  
I agree it is hidden and can cause issues that why but once its set properly we should not have problems.
I'm not getting and Error in SSMS it runs properly but nothing updates.
Capture.PNG
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38782389
can you please post the .sql for the CREATE TABLE statements and the INSERT statements for sample data, and the UPDATE/INSERT that you are performing so we can try to reproduce this?
0
 

Author Comment

by:PtboGiser
ID: 38782462
There is no Create Table or insert statements for the this table that was all done with a Front end GIS software package.. I export the drawing from Manifold GIS to SQL server This creates a table with a geometry Field. I'm sure there is a sql statment that is completed but it is in the background programed into the GIS package i do not see it.
 Inserts are completed on a Linked GIS file in ManifoldGIS as well. Draw a line for a new road then i would populate the values in ManifoldGIS.
We do not insert most records in SQL server as we are based in the Manifold GIS Software. All edits, inserts, and deletions are compelted there.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38782469
>There is no Create Table or insert statements for the this table
yes, there is. you can generate the DDL for your tables, whoever created the tables initially.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 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