Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
islandantics
Starting Member
3 Posts |
 Posted - 2006-05-05 : 02:06:28
|
| Hi all,First time post here. I had a look to see if I could find a post to answer my question, but could not. Here is the situation:I have 2 tables, both tables have an identy field as the Primary Keys. Table_A primary key (identity) is also the foreign key for table_B. Table_A Table_BA_ID (identity) B_ID (identity)A_Name A_ID (Foreign key) B_Orders So, Ihave an INSERT trigger on Table_A that inserts a new record in Table_B containing Table_A primary Key (A_ID). This part works, but when I enter a new record (through Enterprise Manager) it creates a new record in Table_B and Table_A but Table_A does not show the A_ID value until after I refresh the table (rather than creating the new identity value on the spot). I have narrowed the problem down to the fact that Table_B has the identity value as its Primary Key (B_ID). If I remove the identity so that a primary ID has to be manually inserted, Table_A populated the A_ID value as it should. Trigger looks something like: CREATE TRIGGER [Insert_record] ON [dbo].[Table_A] FOR INSERT AS Begin declare @myID bigint select @myID = A_ID from inserted insert into Table_B(A_ID)values (@myID) EndI have SQL 2000 SP4 and I know that there are bugs related to triggers/identity fields. Can anyone confirm this, and/or let me know a way I can over come this glitch. I thought maybe creating a trigger in Table_B to replace the Identity field??Cheers, |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-05 : 02:36:00
|
| question, are you getting your desired result, if yes, no need to worry how EM handles the insert on table_balso you may want to have on query instead of twoinsert into Table_B(A_ID)select a_id from insertedHTH--------------------keeping it simple... |
 |
|
|
islandantics
Starting Member
3 Posts |
Posted - 2006-05-07 : 19:10:09
|
| Hi Jen,Well, I am getting the results, but only after I refresh (or close and reopen Table_A). If I dont do this, I get an error when trying to run an insert from an application outside SQL because the Identity field wasnt populated. I will try your suggestion and see what happens.Cheers, |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-07 : 20:56:45
|
| if you're in EM, you need to move the cursor (focus) on another row to commit the transactionHTH--------------------keeping it simple... |
|
|
|
islandantics
Starting Member
3 Posts |
Posted - 2006-05-07 : 21:24:12
|
| Well, I tried that, actually created 2 records where both dont show the ID data until I refresh or close/open the table again, so its not a focus issue. Ive spokent to a few of SQL developers, who createad tables to test this and they too cant explain why it doesnt display the A_ID data after Table_B has created the rows with A_ID. I use InfoPath to create a new record, and that gives me an error (hence why I found it trying to replicate it in EM). Very frustrating! |
|
|
|
|
|
|
|
|
Trigger, Identity issues