Update source table on Insert - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update source table on Insert

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-04-24 : 07:13:07

I have an insert trigger that inserts records in another table from a table named TrackData. Consider this...
*****************************
CREATE TRIGGER [DB2_Insert] ON [dbo].[TrackData]
AFTER INSERT
AS
/* Update data records in DB2 from Enovision.TrackData values */
INSERT INTO DB2_Sample.dbo.DB2_TrackData
(DB2_SerialNumber,
DB2_Time_Stamp,
DB2_ClientCode,
DB2_TrackDataKey) /* Field to store TrackData primary key for cross reference during updates */
SELECT
i.SerialNumber,
i.Time_Stamp,
i.ClientCode,
i.TrackDataIDKey /* Cross Refrerence Primary Key for updates */
FROM
inserted i
***************************************

Now I can later update records in DB2_TrackData from TrackData becuase there is a one-to-one correlation of TrackData.TrackDataIDKey to DB2.DB2_TrackDataKey

But what if the DB2 table cannot store TrackDataIDKey? The secondary table schema may not be able to be changed to accomodate this feature.

The TrackData table contains a field DB2_Key for maintaining the secondary database table key within TrackData. Is there any way I can update this field during the insert without having to create a loop scenario?

Would it be possible to write an Insert Trigger on the secondary data table that would update the DB2_Key in the TrackData table?

TIA

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-25 : 01:54:58

Think this is a problem with definition.
Can you give the schema for the relevant fields and also say what you ae trying to do.

What is DB2_Key and what relationship does it have to TrackData?
You will probably answer your own question.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-04-25 : 08:07:00

Yeah, it's a tough one to explain. Let me retry.
I have a Trackdata table in a database Enovision with this schema...
************************************
CREATE TABLE [Enovision].[dbo].[TrackData] (
[TrackDataIDKey] [int] IDENTITY (1, 1) NOT NULL ,
[SerialNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Time_Stamp] [datetime] NOT NULL ,
[ClientCode] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InputCode] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ErrorCode] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Text] [char] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cleared_By] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cleared_On] [datetime] NULL ,
[ErrorLevel] [smallint] NULL ,
[ERMSDate] [datetime] NOT NULL ,
[DB2_Key] [int] NULL
) ON [PRIMARY]

And here is DB2...

CREATE TABLE [DB2_Sample].[dbo].[DB2_TrackData] (
[DB2_Key] [int] IDENTITY (1, 1) NOT NULL ,
[DB2_SerialNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DB2_Time_Stamp] [datetime] NOT NULL ,
[DB2_ClientCode] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

***********************************************

So, let's say I have this Insert Trigger on Enovision.TrackData...

***************************************************
INSERT INTO DB2_Sample.dbo.DB2_TrackData
(DB2_SerialNumber,
DB2_Time_Stamp,
DB2_ClientCode
SELECT
i.SerialNumber,
i.Time_Stamp,
i.ClientCode
FROM
inserted i
*********************************

The schema of DB2_Trackdata table cannot be modified to store the primary key TrackData.TrackDataIDKey. So, in order to setup a relation for updates I need to update the TrackData.DB2_Key field with the primary key from DB2_Sample.DB2_TrackData.DB2_Key field.

How can I update Enovision.TrackData.DB2_Key with the value in DB2_Sample.DB2_TrackData.DB2_Key? Is there some way I could use an Insert Trigger on DB2_Sample.DB2_TrackData? Since I am not inserting the primary key from TrackData into the DB2_Sample table how can I reference it?

Hope this clears it up a bit.

TIA

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-25 : 10:52:18

Oops.
You might have bigger problems.
What happens if you need to restore a database - this relies on the two databases being in step. Unless you only take backups of both databases when the system is down you will need scripts to resynchronise.
You would be better creating a new table in DB2_Sample to hold the link to make it easier.

As you say the easiest way of doing what you want is to insert row by row but if there are a lot of rows then you can do it by inserting unique batches - something like:

select
i.TrackDataIDKey ,
i.SerialNumber,
i.Time_Stamp,
i.ClientCode ,
status = 0
into #a
FROM
inserted i

declare @i int

while exists (select * from #a)
begin
update #a
set status = 1
where TrackDataIDKey = (select min(TrackDataIDKey) from #a a2 where a2.SerialNumber = #a.SerialNumber)

select @i = max(DB2_Key) from DB2_Sample.DB2_TrackData

insert DB2_Sample.dbo.DB2_TrackData
(DB2_SerialNumber,
DB2_Time_Stamp,
DB2_ClientCode
SELECT
SerialNumber,
Time_Stamp,
ClientCode
from #a
where status = 1

update TrackData
set DB2_Key = DB2_TrackData.DB2_Key
from DB2_Sample.dbo.DB2_TrackData DB2_TrackData
#a
where DB2_TrackData.SerialNumber = #a.DB2_SerialNumber
and #a.status = 1
and DB2_TrackData.DB2_Key > @i

delete #a where status = 1
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-04-26 : 10:01:30

Good point on the backups, & I will definitely let the client know of the possible problem.

As for the insertion of multiple rows I do not see that happening in this application, not to say that it couldn't. You could be right about using a link table & I was actually thinking about that. However I am not too sure what the advantage would be because how can you relate the DB2_Trackdata back to TrackData after it has been inserted? The only way I see that happening is if you store the cross-references in the DB2_Trackdata table.

Could you possibly add a few comments to the above code? I am finding it hard to comprehend. I still don't see how the final update statement will select the proper record from DB2_Sample because serialnumber is not unique in the DB2_TrackData table.n I guess it could work if you selected TOP 1 based on a Group By of ClientCode, SerialNumber, & Time_Stamp, as long as your server date never changes. :)

Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-28 : 04:04:47

The code above takes the min TrackDataIDKey for each distinct SerialNumber and sets status on that record.
It then adds all these recs to the destination table.
It then updates the source table with the ID from the destination.
In all this it is working with distinct SerialNumber's so there is no problem.
It then loops though all the duplicate SerialNumber's so if there is a SerialNumber that appears 3 times it will go round the loop 3 times.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-04-28 : 09:37:28

OK, I think I understand. Just had a meeting with the client yesterday & they decided to not perform updates, only inserts, so this is no longer an issue. Thanks much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources