解决通过identity字段关联的多个数据表插入问题

在数据库设计中经常要用到自动增长字段(identity) ，在使用自动增长字段做多表关联时

往往会遇到这样一个问题，当向主表插入数据后同时需要向与之关联的表插入相关的数据

比如用户基本信息表记录用户基本信息，它通过自动增长字段UserId和用户扩展信息表关联。

当向用户基本信息表插入一条用户信息后，我们需要同时向用户扩展信息表插入于这个用户相关

的扩展信息，由于我们无法知道刚被插入的这条用户基本信息对应的UserId值具体是多少，这样

在插入扩展信息表时就无法填入该值。

本文提供了一种通过触发器来解决该问题的方法，供大家参考

if exists (select * from sysobjects where id = object_id('testfk') and type = 'u')
 drop table testfk
GO
if exists (select * from sysobjects where id = object_id('testpk') and type = 'u')
 drop table testpk
GO
create table testpk
(
 id int IDENTITY(1,1)  NOT NULL PRIMARY KEY,
 a  int
)
GO
create table testfk
(
 id int FOREIGN KEY REFERENCES  testpk(id),
 b  int
)

GO
if exists (select * from sysobjects where id = object_id('testview') and type = 'v')
 drop view testview
GO
create view testview as
select testpk.id, a, b from testpk,testfk where testpk.id = testfk.id

GO
if exists (select * from sysobjects where id = object_id('InsertTowTable') and type = 'p')
 drop procedure InsertTowTable
GO

create procedure InsertTowTable
@a  int, -- a
@b int -- b
as
declare
@fid int

begin try
drop table #TEMPTABLE
end try
begin catch
end  catch

create table #TEMPTABLE
(
   id int
)

insert testpk values(@a)
select @fid = max(id) from #TEMPTABLE
insert testfk values(@fid,@b)

GO
if exists (select * from sysobjects where id = object_id('TestInsert') and type = 'p')
 drop procedure TestInsert
GO

create procedure TestInsert
@a int, -- a
@b int, -- b
@cnt int -- count
as
declare
@i int
select @i = 0
while @i < @cnt
begin
EXEC InsertTowTable @a, @b
select @i = @i+1
end
GO

if exists (select * from sysobjects where id = object_id('Insert_testpk') and type = 't')
 drop trigger Insert_testpk
GO
Create Trigger Insert_testpk On testpk
For Insert
As
Insert #TEMPTABLE(ID) Select ID From Inserted
GO

 

eaglet

2007-4-20