sql-server - DML 触发器,在插入/更新/删除后为多个客户创建新行
问题描述
我正在使用 SQL Server 2017。
我想要实现的目标:数据库包含客户、优惠券代码和供应商的表格。客户就是公司。一个客户可能是一个独立的公司,它可能属于另一个公司,它可能有“子公司”(子公司和母公司之间的联系是通过客户表中的 ParentID 列建立的)。
优惠券代码与客户相关。当“母公司”激活访问优惠券代码时,该代码应该可供母公司的所有子公司使用。同样,如果母公司停用对优惠券代码的访问权限,子公司也应该失去访问权限。到今天为止,这必须手动修复。我正在编写一个将接管此手动任务的触发器。
表CompanyCodes存储公司和优惠券代码之间的关系。“客户”栏存储公司ID号,“供应商”栏存储优惠券代码供应商的ID,“代码”栏存储优惠券代码。
每当在 CompanyCodes 表中插入、更新或删除一行时,触发器首先检查相关客户是否有任何子公司。如果是这样,则触发器应该为公司的每个子公司插入/更新/删除一行,以便将更改反映在所有子公司中。
例如,客户 1 是客户 2 和客户 3 的父级。客户 1 激活对供应商 X 的新优惠券代码的访问,并在 CompanyCodes 中插入包含相关信息的新行。在这种情况下,触发器应该在表中插入两个新行,一个用于客户 2,一个用于客户 3,其中所有信息(公司 ID 除外)与为客户 1 插入的信息相同。
我被困在哪里:如果客户有多家子公司,我该如何进行这项工作?
这是我当前查询的相关部分(这都在触发触发操作为“INSERTED”时执行的 IF 语句中)
DECLARE @insertedSupplier AS INT
DECLARE @insertedCode AS VARCHAR(50)
SET @insertedSupplier = (SELECT Supplier FROM inserted)
SET @insertedCode = (SELECT Code FROM inserted)
INSERT INTO dbo.CompanyCodes (Customer, Supplier, Code)
VALUES ((SELECT id FROM Customers WHERE Customers.ParentID = (SELECT Customer FROM inserted))
,@insertedSupplier, @insertedCode)
如果最多有一个可能的子公司,则上述方法应该有效。但是当有多个时,“(SELECT id FROM Customers WHERE Customers.ParentID = (SELECT Customer FROM inserted)”将返回几行。
什么是最简单(最好是最佳实践?)的方式来做我想做的事情?
使用显示所需输出的示例进行编辑:
客户表
| Customer | ID | ParentID |
|----------|----|----------|
|Customer1 | 1 | NULL |
|Customer2 | 2 | 1 |
|Customer3 | 3 | 1 |
然后 Customer1 继续激活对新优惠券代码的访问,CompanyCodes 表更改为以下内容:
| Customer | Supplier | Code |
|----------|----------|----------|
|Customer1 | 123456 | abcdefgh |
然后触发器触发,并为 Customer1 的两个子公司中的每一个添加一行(其中除客户名称外的所有信息都与 Customer1 相同):
| Customer | Supplier | Code |
|----------|----------|----------|
|Customer1 | 123456 | abcdefgh |
|Customer2 | 123456 | abcdefgh |
|Customer3 | 123456 | abcdefgh |
编辑 2:此外,我们系统的工作方式,CompanyCodes 表中的一行永远不会超过一个插入/更新/删除(没有批处理作业)