我在两个表Users
和Projects
之间有多对多关系。
将这两个表连接在一起的表称为ProjectsUsers
。
这是表及其关系的描述:
CREATE TABLE "Users"
(
Email VARCHAR(320) COLLATE SQL_Latin1_General_CP1_CI_AS PRIMARY KEY CHECK(LEN(Email) >= 3),--More....
);
CREATE TABLE "Projects"
(
ProjectID INT PRIMARY KEY IDENTITY,--More....
);
CREATE TABLE "ProjectsUsers"
(
UsersEmail VARCHAR(320) COLLATE SQL_Latin1_General_CP1_CI_AS CHECK(LEN(UsersEmail) >= 3) NOT NULL,ProjectsID INT NOT NULL,CONSTRAINT ProjectsUsers_PK PRIMARY KEY (UsersEmail,ProjectsID),CONSTRAINT ProjectsID_FK FOREIGN KEY (ProjectsID) REFERENCES Projects (ProjectID)
ON DELETE CASCADE ON UPDATE CASCADE,CONSTRAINT UsersEmail_FK FOREIGN KEY (UsersEmail) REFERENCES Users(Email)
ON DELETE CASCADE ON UPDATE CASCADE
);
我现在正在尝试创建一个存储过程,该存储过程会将一个新项目插入到Projects
表中。添加项目后,我想在ProjectsUsers
表中创建对此项目的引用。问题是,我无法知道我刚创建的项目的ID,因此无法知道应该在ProjectsUsers
中插入什么ID。
因此,如果我的存储过程是这样的:
INSERT INTO Projects (Project,CreationDate,ProjectName)
VALUES (@project,GETDATE(),@email);
INSERT INTO ProjectsUsers VALUES (@email,???)
如何获取ID?