Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

2 Inserts into Inserted table Why?

Posted on 2013-06-13
11
Medium Priority
?
253 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 1500 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 1500 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

722 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