使用视图代替 INSERT 触发器和标识列

我正在构建一个数据库来存储公司的客户数据。数据库中的表已标准化，因此我有多个使用外键约束链接在一起的表。

Microsoft Access 将用于与数据库交互（作为前端）。为了使事情变得更简单，我创建了一个视图，将所有必需的表连接在一起，以便最终用户可以轻松查询信息。

我遇到的问题涉及将信息插入到该视图中。根据我的理解，由于我的视图中有多个表，因此我必须使用带有INSTEAD OF INSERT陈述。我已经创建了触发器；但是，我不确定如何与ID表中的列（这些充当键）。

我有一个MemberBasicInformation包含客户的出生日期、姓名、性别等及其会员 ID 的表。这ID is an IDENTITY表中的列，因此它是自动生成的。我遇到的问题是，因为身份是自动生成的，所以我无法获取插入后生成的身份值MemberBasicInformation表并将其插入到其他相关表中。当我尝试这样做时，我最终会违反外键约束。

我尝试过使用@@Identity and Scope_Identity()无济于事。我列出了我的观点和触发器，让您了解事情是如何设置的。如果有人能指出我正确的方向，我将不胜感激。我真的很茫然。

MEMBER view:

CREATE VIEW [dbo].[Member]
AS
    SELECT        
        dbo.MemberBasic.MemberId, dbo.MemberBasic.FirstName, 
        dbo.MemberBasic.MiddleInitial, dbo.MemberBasic.LastName, 
        dbo.MemberBasic.FullName, dbo.MemberBasic.DateOfBirth, 
        dbo.Gender.Name AS Gender, dbo.MemberBasic.Address, 
        dbo.MemberBasic.Address2, dbo.MemberBasic.City, 
        dbo.MemberBasic.State, dbo.MemberBasic.ZipCode, 
        dbo.MemberBasic.PhoneNumber, 
        dbo.MemberBasic.SocialSecurityNumber, 
        dbo.MemberBasic.DriversLicense, 
        dbo.MemberBasic.EmployerIdentificationNumber, 
        dbo.MemberBasic.Notes, 
        dbo.FieldRep.Name AS FieldRepName, 
        dbo.MemberDetail.DateAssigned AS FieldRepDateAssigned, 
        dbo.MemberDetail.CPReceivedOn, dbo.MemberDetail.CredentialedOn, 
        dbo.MemberEligibility.IsActive, dbo.ICO.Name AS ICO, 
        dbo.MemberEligibility.StartDate AS EligibilityStartDate, 
        dbo.MemberEligibility.EndDate AS EligibilityEndDate, 
        dbo.MemberWorkerCompDetail.ExpirationDate AS WorkerCompExpirationDate, 
        dbo.MemberWorkerCompDetail.AuditDate AS WorkerCompAuditDate, 
        dbo.WorkerCompTier.Name AS WorkerCompTier, 
        dbo.MemberAttachment.AttachmentId, 
        dbo.MemberAttachment.Data AS AttachmentData
    FROM            
        dbo.MemberAttachment 
    INNER JOIN
        dbo.MemberBasic ON dbo.MemberAttachment.MemberId = dbo.MemberBasic.MemberId 
    INNER JOIN
        dbo.MemberCaregiverAssignment ON dbo.MemberAttachment.MemberId = dbo.MemberCaregiverAssignment.MemberId 
    INNER JOIN
        dbo.MemberDetail ON dbo.MemberBasic.MemberId = dbo.MemberDetail.MemberId 
    INNER JOIN
        dbo.MemberEligibility ON dbo.MemberAttachment.MemberId = dbo.MemberEligibility.MemberId 
    INNER JOIN
        dbo.MemberWorkerCompDetail ON dbo.MemberAttachment.MemberId = dbo.MemberWorkerCompDetail.MemberId 
    INNER JOIN
        dbo.Gender ON dbo.MemberBasic.GenderId = dbo.Gender.GenderId 
    INNER JOIN
        dbo.FieldRep ON dbo.MemberDetail.FieldRepId = dbo.FieldRep.FieldRepId 
    INNER JOIN
        dbo.ICO ON dbo.MemberEligibility.ICOId = dbo.ICO.ICOId 
    INNER JOIN
        dbo.WorkerCompTier ON dbo.MemberWorkerCompDetail.TierId = dbo.WorkerCompTier.TierId
GO

MEMBER扳机：

ALTER TRIGGER [dbo].[InsertNewMember] 
ON [dbo].[Member] 
INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO MemberBasic (FirstName, MiddleInitial, LastName, GenderId, DateOfBirth, Address, Address2, City, State, ZipCode, PhoneNumber, SocialSecurityNumber, DriversLicense, EmployerIdentificationNumber, Notes)
        SELECT 
            FirstName, MiddleInitial, LastName, GenderId, DateOfBirth, 
            Address, Address2, City, State, ZipCode, PhoneNumber, 
            SocialSecurityNumber, DriversLicense, 
            EmployerIdentificationNumber, Notes 
        FROM 
            inserted
        INNER JOIN
            Gender ON Gender.Name = Gender; 

    INSERT INTO MemberDetail (MemberId, FieldRepId, DateAssigned, CPReceivedOn, CredentialedOn)
        SELECT 
            MemberId, FieldRep.FieldRepId, FieldRepDateAssigned, 
            CPReceivedOn, CredentialedOn
        FROM 
            inserted
        INNER JOIN
            FieldRep ON FieldRep.Name = FieldRepName;

    INSERT INTO MemberEligibility (MemberId, ICOId, StartDate, EndDate)
        SELECT 
            MemberId, ICOId, EligibilityStartDate, EligibilityEndDate 
        FROM 
            inserted
        INNER JOIN
            ICO ON ICO.Name = ICO;

    INSERT INTO MemberWorkerCompDetail (MemberId, AuditDate, ExpirationDate, TierId)
        SELECT 
            MemberId, WorkerCompAuditDate, WorkerCompExpirationDate, TierId
        FROM 
            inserted
        INNER JOIN
            WorkerCompTier ON WorkerCompTier.Name = WorkerCompTier;

    INSERT INTO MemberAttachment (MemberId, Data)
        SELECT MemberId, AttachmentData
        FROM Member
END

您可以通过使用表变量来保存新插入的 ID 来完成此操作and您要插入到其他表中的所有数据。你需要这样做，因为没有办法从第一个开始INSERTs 要连接回的数据inserted以这样的方式，你可以匹配IDENTITY值以及导致它们生成的行。

我们也得虐MERGE since INSERT不允许您在其表中包含目标表以外的任何内容OUTPUT clause.

我正在一个玩具示例中执行此操作，但希望您能够了解如何为完整的表结构编写它。

首先，一些表格：

create table dbo.Core (
    ID int IDENTITY(-71,3) not null,
    A varchar(10) not null,
    constraint PK_Core PRIMARY KEY (ID)
)
go
create table dbo.Child1 (
    ID int IDENTITY(-42,19) not null,
    ParentID int not null,
    B varchar(10) not null,
    constraint PK_Child1 PRIMARY KEY (ID),
    constraint FK_Child1_Core FOREIGN KEY (ParentID) references Core(ID)
)
go
create table dbo.Child2 (
    ID int IDENTITY(-42,19) not null,
    ParentID int not null,
    C varchar(10) not null,
    constraint PK_Child2 PRIMARY KEY (ID),
    constraint FK_Child2_Core FOREIGN KEY (ParentID) references Core(ID)
)
go

和观点：

create view dbo.Together
with schemabinding
as
    select
        c.ID,
        c.A,
        c1.B,
        c2.C
    from
        dbo.Core c
            inner join
        dbo.Child1 c1
            on
                c.ID = c1.ParentID
            inner join
        dbo.Child2 c2
            on
                c.ID = c2.ParentID
go

最后是触发器：

create trigger Together_T_I
on dbo.Together
instead of insert
as
    set nocount on
    declare @tmp table (ID int not null, B varchar(10) not null, C varchar(10) not null);

    merge into dbo.Core c
    using inserted i
    on
        c.ID = i.ID
    when not matched then insert (A) values (i.A)
    output
        inserted.ID /* NB - This is the output clauses inserted,
                    not the trigger's inserted so this is now populated */
        ,i.B,
        i.C
    into @tmp;

    insert into dbo.Child1(ParentID,B)
    select ID,B
    from @tmp

    insert into dbo.Child2(ParentID,C)
    select ID,C
    from @tmp

（我会在那里保留类似的评论，因为触发器内的语句包括OUTPUT条款往往很混乱，因为有two inserted比赛中的桌子）

（还值得注意的是，你在其中放入什么并不重要ON的条款MERGE，只要你确定它会fail进行匹配。你可能更喜欢，比如说，只是1=0如果你认为这更清楚了。我只是因为触发器的事实而变得可爱inserted.ID将NULL)

现在我们进行插入：

insert into dbo.Together(A,B,C) values ('aaa','bbb','ccc')
go
select * from dbo.Together

并得到结果：

ID          A          B          C
----------- ---------- ---------- ----------
-71         aaa        bbb        ccc