• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Trigger Looks good?

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
PtboGiser
Asked:
PtboGiser
  • 2
  • 2
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
lcohanDatabase AnalystCommented:
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
 
PtboGiserAuthor Commented:
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
PtboGiserAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now