一个省表Province, 两列 ProvinceName ProvinceID(pk)( 默认为newid())一个市表City 四列 CityName CityID(pk)(默认为newid()) UnderProvinceName UnderProvinceID怎样在插入ProvinceName,ProvinceID(自动生成)时,同时插入City表的和ProvinceID一样的UnderProvinceID
调试欢乐多
要一至, 用什么无所谓,能实现这个功能就可以了
after insert
as
declare @id int
set @id=(select ProvinceID from Province p,inserted i where p.ProvinceName=i.ProvinceName)
insert City (UnderProvinceID) values(@id)
试试吧
--这样?
create proc AddPro
@ProvinceName varchar(50),
@CityName varchar(50)
as
declare @ProvinceID [uniqueidentifier]
set @ProvinceID=newid()
insert into Province(ProvinceID,ProvinceName) select @ProvinceID,@ProvinceName
insert into city(CityName,UnderProvinceID,UnderProvinceName) select @cityname,@ProvinceID,@ProvinceName
go
SCOPE_IDENTITY()是numeric类型的,也不行对于guid类型的,一般都是在客户段先生成,再插入
go
create table Province
(ProvinceName varchar(20),ProvinceID uniqueidentifier default newid() not null)
alter table Province
add constraint primary_key primary key (ProvinceID)go
create table City
(CityName varchar(20),
CityID uniqueidentifier default newid(),
UnderProvinceName varchar(20),
UnderProvinceID uniqueidentifier)
go
declare @newid uniqueidentifier
set @newid=newid()
insert into Province (ProvinceName,ProvinceID)
select 'aaaaa',@newidselect * from Provinceinsert into City (UnderProvinceID)
select @newidselect * from City/*
ProvinceName ProvinceID
-------------------- ------------------------------------
aaaaa 6AC66C8F-0EDE-4CD3-A61E-F805B6195DDA
CityName CityID UnderProvinceName UnderProvinceID
-------------------- ------------------------------------ -------------------- ------------------------------------
NULL ABC845CA-A117-4D3D-BDE2-1A57CF63186A NULL 6AC66C8F-0EDE-4CD3-A61E-F805B6195DDA
*/