Solved

Trigger Looks good?

Posted on 2013-01-15
6
198 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 39

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 39

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

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 142

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

776 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