Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trigger Looks good?

Posted on 2013-01-15
6
Medium Priority
?
210 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

636 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