Solved

Trigger Looks good?

Posted on 2013-01-15
6
196 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

758 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

17 Experts available now in Live!

Get 1:1 Help Now