如何为此插入过程创建更新 [英] How do I create update for this insert proc

问题描述

Createproc [dbo].[Insert_Apartment]
@cat_id int,
@Build_na nvarchar(50) ,
@floor smallint , 
@number_rooms smallint ,
@price decimal,
@location nvarchar(50),
@images image,
@owner_name nvarchar(50),
@owner_phone nvarchar(15),
@note nvarchar(max),
@person_name nvarchar(59),
@person_number nvarchar(50),
@con_image image
as
DECLARE @NewID INT

INSERT INTO tbPerson(PersonName,PresonPhone,ContractPhoto) VALUES(@person_name, @person_number, @con_image)

SELECT @NewID = SCOPE_IDENTITY()

INSERT INTO tbApartments(BuildingName,Location,Price,NumberOfRooms,Floor
,OwnerName,OwnerPhone,Note,IdCat,PersonID,Photo) VALUES(@Build_na, @location,@price,@number_rooms,@floor,@owner_name
,@owner_phone,@note,@cat_id,@NewID,@images)

我尝试了什么：



我试过但我的scope_Identity问题当我们需要时它会如何发生更新它

What I have tried:

I tried but my Problem with scope_Identity how it will happen when we need to update it

推荐答案

如果我理解你的情况，我相信你试图在一个程序中做太多。我的意思是这个基于名称的单个过程应该只在tbApartments -table中插入一行，也在tbPerson表中插入一行。我认为这两个操作应分成单独的程序。



例如，考虑如果一个人有两套公寓会发生什么。在这种情况下，你会为​​同一个人插入两个不同的行，两个公寓将链接到不同的人。



如果你分开操作，它也意味着插入或更新单元格行将接收person外键作为参数，在这种情况下您不需要担心scope_identity。换句话说



场景1，添加新人和新公寓

- 来电插入人员程序

- 插入人，从范围标识中获取键值

- 调用插入公寓程序，传递收到的人员密钥

- 在程序中插入公寓，从范围标识中获取密钥值



场景2，为现有人添加新公寓

- 获取现有人员钥匙

- 来电插入公寓程序，通过找到的人员钥匙

- 在程序中插入公寓，从范围标识中获取关键值



场景3，更新现有公寓

- 取现有的公寓行

- 调用更新公寓程序，将所有值传递给程序

- 更新公寓行基于公寓钥匙的程序



等......

If I understand your situation correctly, I believe that you're trying to do too much in a single procedure. What I mean is that this single procedure which based on the name should only insert a row in the tbApartments -table also inserts a row on in tbPerson table. I would believe that these two operations should be split into separate procedures.

For example, consider what happens if a single person has two apartments. In such case you would insert two different rows for the same person and the two apartments would be linked to different persons.

If you separate the operations, it also means that inserting or updating the apartment row would receive the person foreign key as a parameter and you wouldn't need to worry about scope_identity in this case. In other words

Scenario 1, add new person and new apartment
- call insert person procedure
- insert person, get the key value from scope identity
- call insert apartment procedure, pass the received person key
- insert apartment in procedure, get the key value from scope identity

Scenario 2, add new apartment to existing person
- fetch the existing person key
- call insert apartment procedure, pass the person key found
- insert apartment in procedure, get the key value from scope identity

Scenario 3, update an existing apartment
- fetch the existing apartment row
- call update apartment procedure, pass all values to the procedure
- update the apartment row in procedure based on the apartment key

and so on...

这篇关于如何为此插入过程创建更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！