gpt4 book ai didi

Sql Server,使用自引用外键构建非循环父子关系

转载 作者:搜寻专家 更新时间:2023-10-30 20:49:24 28 4
gpt4 key购买 nike

请注意:我是一名软件开发人员,对数据库编程/管理的了解有限。


我正在尝试建立一种结构,在这种结构中,公司之间可以建立父子关系。基本想法是,您可能在世界不同地区运营一家公司的不同分支机构,它们共享一些数据但不是所有数据(例如邮寄地址和本地联系人)。

表格的精简版看起来像这样:

CREATE TABLE COMPANY (
COMPANY_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
PARENT_COMPANY INT FOREIGN KEY REFERENCES COMPANY(COMPANY_ID),
NAME NVARCHAR(256)
);

我遇到的问题是这种结构允许循环关系,在这种关系中,母公司可能成为其后代之一的 child 。下面的sql会导致出现这种情况。

INTO COMPANY
(COMPANY_ID, PARENT_COMPANY, NAME)
VALUES
(1, null, 'Company1'),
(2, 1, 'Company2'),
(3, 2, 'Company3');

UPDATE COMPANY
SET PARENT_COMPANY = 3
WHERE COMPANY_ID = 1;

因为这种关系可能会导致无限循环,所以我想防止这种情况发生。


我能想到的最好的想法是在 COMPANY 表上运行一个触发器,它会检查以确保 PARENT_COMPANY 列中更新的任何值不会导致循环关系。但是 Sql Server 没有 BEFORE UPDATE 触发器;只有 AFTERINSTEAD OF 触发器,它们都在表已经更新后运行。这意味着在我有机会检查它之前就已经创建了循环关系。此时我可能会从触发器 DELETED 临时表和 Company 表本身重建表的“之前”版本;然后在该表中搜索循环关系;但这似乎非常麻烦且效率低下。

有没有其他方法可以检查自引用 Sql Server 结构中的循环关系。


PS.我正计划使用类似这样的东西来搜索触发器中的循环关系:

DECLARE @CompanyId int = 1 -- ID of company thats been changed
;WITH cte AS
(
SELECT a.COMPANY_ID, a.PARENT_COMPANY, a.NAME
FROM COMPANY a
WHERE COMPANY_ID = @CompanyId
UNION ALL
SELECT a.COMPANY_ID, a.PARENT_COMPANY, a.NAME
FROM COMPANY a JOIN cte c ON a.PARENT_COMPANY = c.COMPANY_ID
)
SELECT COMPANY_ID, PARENT_COMPANY, NAME
FROM cte
ORDER BY NAME

最佳答案

首先我想纠正你的一些说法:

  1. AFTER 触发器:触发器是导致触发器触发的批处理操作的一部分(例如 UPDATE),因此除非触发器成功,否则记录不会提交(和可见)。

.... an after trigger fires before the an implicit transaction is committed. A rollback in a trigger will rollback the statement that fired the trigger and abort the entire batch as well

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/73862414-d770-46bb-97fb-249a3fb38680/does-a-insert-trigger-fire-when-the-insert-is-committed?forum=transactsql

绕过此问题的唯一方法是使用显式READ UNCOMMITTED 提示运行SELECT 查询,除非出于非常特殊的目的,否则通常不会这样做。

  1. INSTEAD OF 触发器:顾名思义,它们运行而不是常规的插入/更新操作。换句话说,如果定义了操作(例如更新)的 INSTEAD OF 触发器,它会覆盖更新行为。查看更多:https://technet.microsoft.com/en-us/library/ms179288(v=sql.105).aspx

总结:

在您的情况下,我建议创建一个简单的 AFTER UPDATE 触发器,您将在其中检查数据,如果发现问题,则抛出有意义的错误并执行 ROLLBACK TRANSACTION “取消”此更改。通过这种方式,您可以实现与约束等工作方式类似的行为。

我建议您也看看这篇关于在数据库中存储层次关系的综合文章:What are the options for storing hierarchical data in a relational database?

关于Sql Server,使用自引用外键构建非循环父子关系,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47824241/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com