SQL Server：INSTEAD OF INSERT触发器&使用视图的身份列 [英] SQL Server : INSTEAD OF INSERT trigger & Identity column using view

问题描述

一个星期的大部分时间里，我一直把头撞在墙上。我已经学习SQL（T-SQL）几周了，但是遇到了麻烦。

I've been banging my head against a wall for the better part of a week. I've been studying SQL (T-SQL) for a few weeks now, and I've hit a snag.

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

I'm building a database that will store client data for a company. The tables in the DB are normalized, so I have multiple tables that are linked together using Foreign Key Constraints.

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

Microsoft Access will be used to interface with the database (as a frontend). To make things simpler, I created a view that joins all the required tables together so that end-users can query information without hassle.

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

The problem I've run into involves INSERTING information into this view. From my understanding, since I have multiple tables in my view, I have to use a trigger with an INSTEAD OF INSERT statement. I've created the trigger; however, I'm unsure how to work with the ID columns in the tables (these act as keys).

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

I have a MemberBasicInformation table that contains the client's DOB, Name, Gender, etc AND their MemberID. This ID is an IDENTITY column in the table, so it is generated automatically. The problem that I'm running into is that because the identity is automatically generated, I'm unable to grab the identity value that is generated after the insert into the MemberBasicInformation table and insert it into the other related tables. When I attempt to do so, I end up with a Foreign Key constraint violations.

我尝试使用 @@ Identity 和 Scope_Identity（）无济于事。我列出了我的观点和触发因素，以使您了解如何进行设置。如果有人能指出我正确的方向，我将不胜感激。我真的很茫然。

I've tried using @@Identity and Scope_Identity() to no avail. I've listed my view and trigger to give you an idea of how things are set up. I would greatly appreciate if someone could point me in the right direction. I'm truly at a loss.

会员视图：

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

成员触发器：

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 和来完成此操作。您将要插入其他表的数据。您需要执行此操作，因为在这种情况下，仅从最初的 INSERT 数据无法联接回插入的您可以将 IDENTITY 值与导致它们生成的行进行匹配的方式。

You can do this by using a table variable to hold the newly inserted IDs and all of the data that you're going to insert into the other tables. You need to do this because there's no way from just the first INSERTs data to join back to inserted in such a manner that you can match up the IDENTITY values with the rows that caused them to be generated.

滥用 MERGE ，因为 INSERT 不允许您在目标表的 OUTPUT 子句。

We also have to abuse MERGE since INSERT doesn't let you include anything other than the target table in its OUTPUT clause.

我在一个玩具示例中执行此操作，但希望您能看到如何为整个表结构编写它。

I'm doing this on a toy example but hopefully you can see how to write it for your full table structures.

首先是一些表：

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 子句的触发器中的语句容易引起混淆，因为插入了两个 发挥作用的表）

(And I would keep something like that comment in there since statements inside triggers that include OUTPUT clauses tend to be quite confusing since there are two inserted tables in play)

（同样值得注意的是，您放入 ON < MERGE 的/ code>子句，只要您确定它将失败进行匹配即可。您可能更喜欢假设 1 = 0 更清楚一点，我很高兴知道触发器的 inserted.ID 将为 NULL ）

(It's also noteworthy that it doesn't really matter what you put in the ON clause of the MERGE, so long as you're sure that it will fail to make a match. You may prefer to have, say, just 1=0 if you think it makes it clearer. I just went cute on the fact that the trigger's inserted.ID will be NULL)

现在我们执行插入操作：

And now we do our insert:

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

并得到结果：

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

这篇关于SQL Server：INSTEAD OF INSERT触发器&使用视图的身份列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！