Insert into Table with Identity Column in Trigger

Question

Insert into Table with Identity Column in Trigger

lauriemurphy

Old Hand

Points: 398
More actions
May 18, 2006 at 10:54 am

#190597

I have been researching since September this problem with identities in triggers. I have devised a workaround but I still want to understand what is going on. I cannot stop researching until I find the answer. I am using SQLServer 2000 and VB 6. I have created triggers on certain tables to build audit tables that have (or did have) identity columns. The trigger will fire upon an insert to table A (from the application) which has its own identity column. The trigger will write an insert record into the audit table with an identity column. Back in the application, records that should be using as a foreign key the identity value from table A are all written with the identity value from the audit table. We are using SCOPE_IDENTITY, not @@Identity.

This is the sequence of events:

Insert a person
Person_sid (Identity)
1

Trigger inserts a row into the audit table
Audit_table_sid (Identity)
2

Insert a name for person 1
Person_name_sid (Identity)    Person_sid (Foreign key)
1                                                    2

Insert an address for person 1
Person_address_sid (Identity)    Person_sid (Foreign key)
1                                                    2

I'm baffled and would appreciate any advice you can give me.

Thank you very much,
Laurie

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I think we need to see the table definitions; it looks to me like you have separate tables for ID, name and address;

at the very least the ID and NAME should be in the same table, and maybe the address in a separate table for a 1 to many relationship.

With some more detail, we can help you write the trigger; anything we post now is just best guess/examples, most likely the same you would get from the BOL.

Also when you say "insert a name" or "insert an address", are you really saying update the exisitng record with a new name or address? isn't that the audit you want to track?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lauriemurphy Old Hand Points: 398 More actions · Answer 2

I have a person table with personal characteristics, and separate tables (because they can have many) for names , addresses, dates of birth, driver's licenses, and ssns. Thank you so much for responding to my post because this has been troubling me for months. I ended up removing the identity column from the audit tables because that identity value seemed to be getting passed to subsequent updates to tables with foreign keys to the trigger table.

Audit Table

Audit_Table (audit_table_sid [int] IDENTITY (1, 1) NOT NULL

[DatabaseAction] [char] (1) ,

[person_sid] [int] NULL ,

[New_IP_number] [int] NULL ,

[New_sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

...

[Old_IP_number] [int] NULL ,

[Old_sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

... [CreateDateTime] [datetime] NULL

Person Table

[person] (

[person_sid] [int] IDENTITY (1, 1) NOT NULL ,

[IP_number] [int] NULL ,

[CII_number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

...

Person Address Table (with foreign key to person table)

[person_address] (

[address_sid] [int] IDENTITY (1, 1) NOT NULL ,

[person_sid] [int] NOT NULL ,

[formatted_address] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[zip] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[street_number] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[direction] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

...

noeld SSC Guru Points: 96590 More actions · Answer 3

are you reading SCOPE_IDENTITY after EACH insert?

* Noel

lauriemurphy Old Hand Points: 398 More actions · Answer 4

The inserts to the person table and all of the inserts to the person related tables that occur after the trigger insert to the audit table happen in embedded SQL inside the application (VB 6).

Michael Tocik SSCertifiable Points: 7099 More actions · Answer 5

Hi

Have you taken a look at

IDENT_CURRENT('Datasets')

Which will return the Identity for a table?

Mike

lauriemurphy Old Hand Points: 398 More actions · Answer 6

I'm a little confused because I'm not looking for the scope identity or any other identity. Variables in my VB code are taking on the value of the identity column from the audit table. I don't think this is an issue of which Identity function to use.

Insert into Table with Identity Column in Trigger

Cookies on SQLServerCentral