asked on

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

Surendra Nath

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.

David Kroll

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)

SOLUTION

BAKADY

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PtboGiser

ASKER

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

PtboGiser

ASKER

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!

PtboGiser

ASKER

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

ASKER CERTIFIED SOLUTION

BAKADY

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PtboGiser

ASKER

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.

BAKADY

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

PtboGiser

ASKER

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

BAKADY

we cann't see the reason. can you post it again