Solved

2 Inserts into Inserted table Why?

Posted on 2013-06-13
11
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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
 

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

636 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