级联更新Oracle [英] Cascade Update Oracle

问题描述

我有下表:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50),
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10),
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE SET NULL
);

我需要级联更新供应商"中的主键，并因此更新产品"中的外键.我正在考虑一个带有触发器的游标，该触发器可以更新子表，但是无法弄清楚那是怎么回事

I need to cascade update the the primary key in 'supplier' and hence foreign key in 'products'. I am thinking of a cursor with a trigger to update child table, but cannot figure out how that could be

推荐答案

通常，您将对数据模型进行结构化，以便主键是不变的，从而从一开始就避免了该问题.

Normally, you'd structure your data model such that primary keys are immutable in order to avoid the problem in the first place.

如果您绝对需要允许级联更新，则可能要使用Tom Kyte的级联更新脚本<​​/a>.对于每个表，这将动态生成一个程序包和三个触发器(语句之前，行之前和语句之后)，这些触发器将实现级联更新所需的所有逻辑.这可能比单个开发人员共同努力要高效得多(而且更准确).但是，仅由于需要创建大量对象，所以维护的开销非常大.

If you absolutely need to allow for cascading updates, you probably want to use Tom Kyte's cascading update script. For each table, this will dynamically generate a package and three triggers (before statement, before row, and after statement) that will implement all the logic needed to cascade the update. This is likely to be vastly more efficient (and more accurate) than anything that a single developer would knock together. Just because of the rather large number of objects that need to be created, though, it's a pretty heavy overhead to maintain.

这篇关于级联更新Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！