Solved

2 Inserts into Inserted table Why?

Posted on 2013-06-13
11
241 Views
Last Modified: 2013-06-14
I have the below Trigger. When it fires it sends 2 records identical to the PArcel_History Table. What is the reason why?

USE [CNTY_GIS]
GO
/****** Object:  Trigger [dbo].[Changes_To_Parcel_History]    Script Date: 06/13/2013 14:51:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Changes_To_Parcel_History] 
ON  [dbo].[Parcel]
After UPDATE

AS 

IF 
UPDATE (Geometry) 
OR UPDATE(OP_Designation) 
OR UPDATE(OP_Amendment_No)
OR UPDATE(Zone_Name) 
OR UPDATE(Parcel_ID) 
OR UPDATE(Roll_Number)
OR UPDATE(Zone_Bylaw_No)
OR UPDATE(Zone_Schedule_Name)


 INSERT INTO Parcel_History
 ([geometry], [OP_Designation], [OP_Amendment_No] ,[Zone_Name],[Parcel_ID],
 [Roll_Number], [Zone_Bylaw_No],[Zone_Schedule_Name])
   
  SELECT
  (geometry), (OP_Designation), (OP_Amendment_No) ,(Zone_Name),(Parcel_ID),
  (Roll_Number), (Zone_Bylaw_No),(Zone_Schedule_Name) 
   FROM inserted
   
   set identity_insert Parcel_History OFF

Open in new window

0
Comment
Question by:PtboGiser
11 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39245841
I think either you are firing your update twice
or the trigger is getting fired twice...

anyways to determine that put the below statement before the insert in the trigger and redo your update

select * from inserted

Open in new window


now post the results here and we will be able to help you out.
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 39245902
Are more than one of these fields being updated?

UPDATE (Geometry)
OR UPDATE(OP_Designation)
OR UPDATE(OP_Amendment_No)
OR UPDATE(Zone_Name)
OR UPDATE(Parcel_ID)
OR UPDATE(Roll_Number)
OR UPDATE(Zone_Bylaw_No)
OR UPDATE(Zone_Schedule_Name)
0
 
LVL 4

Assisted Solution

by:BAKADY
BAKADY earned 500 total points
ID: 39245959
i try to duplicate your case in sqlfiddle, but i need some data examples to get better results...

http://sqlfiddle.com/#!3/82fb4/4

it's look like your code work fine.

i think like Neo_jarvis your trigger is getting fire twice. how do your code at program level looks like???
0
 

Author Comment

by:PtboGiser
ID: 39247426
dkrollCTN
I was thinking the same thing but no, we are only updating\testing the Zone_Name Field
Changing its value from a1 to a2.
There is also only one peice of geometry that it is attached too. As during testing i am using a blank drawing and inserting on circle( a Table Record) using my GIS Software
0
 

Author Comment

by:PtboGiser
ID: 39247753
by: Neo_jarvis
I have added
select * from inserted
 to the code and the results are the same. I don't see anything different.
Still stumped!
ITs for sure firing the trigger twice but i'm not sure why!
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:PtboGiser
ID: 39247788
Blank Sample Data Attached for both tables. Table Structure

I working in a GIS Software Called Manifold. Also tested in AutoCAD 2013
Parcel-History.xlsx
Parcel-Blank.xlsx
0
 
LVL 4

Accepted Solution

by:
BAKADY earned 500 total points
ID: 39248034
Can you see the program's code?
0
 

Author Comment

by:PtboGiser
ID: 39248054
No, A similar trigger is run on the Roads dataset from the same GIS Program and it works fine. That why i figured it has to have something to do with this code or table set up.
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39248114
It mean you don't know what and when the program push data and the trigger gets fired.
Maybe you have another trigger in your table or some stored procedures making changes.
Try to find out first adding a time stamp field to your history table...
0
 

Author Comment

by:PtboGiser
ID: 39248755
I've requested that this question be closed as follows:

Accepted answer: 500 points for BAKADY's comment #a39245959
Assisted answer: 0 points for PtboGiser's comment #a39247426
Assisted answer: 0 points for PtboGiser's comment #a39247753

for the following reason:

Thx crew
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39248831
we cann't see the reason. can you post it again
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
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.

910 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

16 Experts available now in Live!

Get 1:1 Help Now