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

2 Inserts into Inserted table Why?

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
PtboGiser
Asked:
PtboGiser
2 Solutions
 
Surendra NathTechnology LeadCommented:
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
 
David KrollCommented:
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
 
BAKADYCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PtboGiserAuthor Commented:
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
 
PtboGiserAuthor Commented:
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
 
PtboGiserAuthor Commented:
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
 
BAKADYCommented:
Can you see the program's code?
0
 
PtboGiserAuthor Commented:
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
 
BAKADYCommented:
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
 
PtboGiserAuthor Commented:
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
 
BAKADYCommented:
we cann't see the reason. can you post it again
0

Featured Post

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.

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