如何根据SQL Server 2008中另一个表中的值编写更新触发器来更新当前表？

I have two tables, Table_A and Table_B. Table_A has a one-to-many relationship with Table_B.

我有两个表,Table_A和Table_B。 Table_A与Table_B具有一对多的关系。

After inserting a record in the child table (Table_B) I want to use a trigger to grab a value from a field in the parent table (Table_A) and update the currently inserted record in Table_B.

在子表(Table_B)中插入记录后,我想使用触发器从父表(Table_A)中的字段中获取值,并更新Table_B中当前插入的记录。

Table_A

PK|EmpID|MemID|Firstname|Lastname
----------------------------------
1 |1234 |AB123|John     | Doe
2 |5678 |CD456|Jane     | Smith

Table_B

PK|EmpID|MemID|Description
---------------------
1 |1234 |NULL |Finance
2 |1234 |NULL |IT
3 |5678 |NULL |Finance 
4 |5678 |NULL |Management

The trigger should grab the MemID value from Table_A and update it's corresponding value in Table_B where [Table_A].[EmpID] = [Table_B].[EmpID]. Of course after 4 inserts in Table_B the results should be as follows:

触发器应该从Table_A中获取MemID值,并在Table_B中更新它的相应值,其中[Table_A]。[EmpID] = [Table_B]。[EmpID]。当然,在表_B中的4个插入后,结果应如下所示:

PK|EmpID|MemID|Description
---------------------
1 |1234 |AB123|Finance
2 |1234 |AB123|IT
3 |5678 |CD456|Finance 
4 |5678 |CD456|Management

I extensively searched other technical sites but can't seem to find one with this particular scenario. As a desperate move I have registered into this site and this would be my 1st post for help.

我广泛搜索了其他技术网站,但似乎无法找到这个特定场景。作为一个绝望的举动,我已经注册到这个网站,这将是我的第一篇求助信。

The following is my futile attempt at trying to create my 1st trigger in MS SQL Server 2008 R2.

以下是我尝试在MS SQL Server 2008 R2中创建第一个触发器的徒劳尝试。

CREATE TRIGGER dbo.trgInsMemID
ON dbo.Table_B
AFTER INSERT
AS
    BEGIN
        UPDATE dbo.Table_B
            SET MemID = 
                (SELECT MemID 
                FROM inserted i 
                JOIN dbo.Table_A c ON c.EmpID = i.EmpID)
            FROM dbo.Table_B b
            JOIN inserted i ON i.MemID = b.MemID
    END

1 个解决方案

#1

I believe you'll want your update statement changed to look like this:

我相信您希望更新语句更改为如下所示:

UPDATE b
SET b.MemId = a.MemId
FROM dbo.Table_B AS b
INNER JOIN Inserted AS i ON b.EmpId = i.EmpId
INNER JOIN dbo.Table_A AS a ON b.EmpId = a.EmpId

This will update only the records in b that were also in the insterted table. (first INNER JOIN)

这将仅更新b中也在insterted表中的记录。 (第一次INNER JOIN)

It will then INNER JOIN into Table_A on EmpId to pull the proper MemId to update your records with.

然后INNER JOIN进入EmpId上的Table_A以拉出适当的MemId来更新你的记录。

#1