如何创建触发器来模拟多个 ON DELETE CASCADE？

发布于 2024-12-04 18:32:34 字数 580 浏览 1 评论 0原文

我有带有外键的表，如下所示：（

Table1A
|
|- Table2A3A

Table1B
|
|- Table2B
   |
   |- Table2A3A

即，Table2A3A 引用 Table1A 和 Table2B，后者引用 Table1B。）

我想创建一个触发器，以便每当表 Table1A、Table1B 或 Table2B 中的任何内容被删除时，Table2A3A 中的内容都会被删除。引用已删除的行也会被删除。

我之所以这样问，是因为如果我尝试在 SQL Server 中添加删除级联，它会返回在表“Table2A3A”上引入外键约束可能会导致循环或多个级联路径。

这种触发器的 SQL 代码是什么？

编辑：

抱歉，我读错了我的表格：（

Table1A
|
|- Table2A3A
|
|- Table2B
   |
   |- Table2A3A

也就是说，Table2A3A 引用 Table1A 和 Table2B，后者引用 Table1A。）

原文

I have tables with foreign keys as follows:

Table1A
|
|- Table2A3A

Table1B
|
|- Table2B
   |
   |- Table2A3A

(That is, Table2A3A references Table1A and Table2B, which references Table1B.)

I want to create a trigger so that, whenever anything in tables Table1A, Table1B, or Table2B gets deleted, that which in Table2A3A references the deleted row also gets deleted.

I am asking because if I try to add on delete cascade in SQL Server, it returns Introducing FOREIGN KEY constraint on table 'Table2A3A' may cause cycles or multiple cascade paths.

What would the SQL code for such a trigger be?

EDIT:

Sorry, I misread my tables:

Table1A
|
|- Table2A3A
|
|- Table2B
   |
   |- Table2A3A

(That is, Table2A3A references Table1A and Table2B, which references Table1A.)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

反目相谮 2024-12-11 18:32:34

根据您的图表，无论是在关系理论还是在 SQL 实践中，级联删除都没有理由导致循环或多个级联路径。。也许您应该发布每个表的 DDL，以便我们可以了解可能发生的其他情况。

这是我为 PostgreSQL 测试编写的内容。（可能会节省其他人一些时间。）我可以使用级联插入和删除。

create table Table1A (
  ta_id integer primary key
);

create table Table1B (
  tb_id integer primary key
);

create table Table2B (
  tb_id integer primary key references Table1B (tb_id) on delete cascade
);

create table Table2A3A (
  tb_id integer not null references Table2B (tb_id) on delete cascade,
  ta_id integer not null references Table1A (ta_id) on delete cascade,
  primary key (tb_id, ta_id)
);

There's no reason either in relational theory or in SQL practice that a cascade delete should cause either a cycle or multiple cascade paths according to your diagram. Maybe you should post the DDL for each of the tables, so we can see what else might be going on.

Here's what I wrote as a test for PostgreSQL. (Might save someone else some time.) I can insert and delete with cascades.

create table Table1A (
  ta_id integer primary key
);

create table Table1B (
  tb_id integer primary key
);

create table Table2B (
  tb_id integer primary key references Table1B (tb_id) on delete cascade
);

create table Table2A3A (
  tb_id integer not null references Table2B (tb_id) on delete cascade,
  ta_id integer not null references Table1A (ta_id) on delete cascade,
  primary key (tb_id, ta_id)
);

回复收藏 0 原文

~没有更多了~