时间戳

首页 > 解决方案 > 删除时触发以避免悬空引用

sql - 删除时触发以避免悬空引用

问题描述

我在 Oracle 中执行某些触发器时遇到问题。

我有两种类型“t_movie”和“t_video”定义为

CREATE TYPE t_movie AS OBJECT( 
name VARCHAR(20),
year INTEGER);

和

CREATE TYPE t_video AS OBJECT( 
type CHAR,
movie REF t_movie);

我也有相关的表格

CREATE TABLE movies OF t_movie


CREATE TABLE videos OF t_video

如果我从表 movies 中删除一个元组，我将在另一个表中有一些元组引用不再存在的对象。我怎样才能避免这种情况？我认为触发器是必要的，但我不知道如何实现它。谁能帮我？

谢谢。

编辑：

我尝试了这样的触发器：

CREATE or REPLACE TRIGGER delete_movie_cascade 
  before delete on movies
  for each row
DECLARE
  movie_ref (REF t_movie);
BEGIN
  movie_ref = ref :old;
  dbms_output.put_line(deref(movie_ref).name);
  DELETE FROM videos WHERE movie = movie_ref;
END;

但是，正如预期的那样，我得到了错误

Error(6,13): PLS-00103: Encountered the symbol "(" when expecting one of the following:     
constant exception <an identifier>    <a double-quoted delimited-identifier> table long 
double ref    char time timestamp interval date binary national character    nchar

标签： sqldatabaseoracleoracle11guser-defined-types

解决方案

Oracle Objection Developer 的文档讨论了防止悬空引用：

列REF可以使用REFERENTIAL类似于外键规范的约束来约束。

不幸的是，文档没有提供如何做到这一点的实际示例。的格式REFERENTIAL表明它是一个关键字，但事实证明这是一个红鲱鱼。

解决方案实际上是定义一个实际的外键，但使用对象引用。因此，使用您发布的代码，将定义更改为videos：

CREATE TYPE t_video AS OBJECT( 
    type CHAR,
    movie REF t_movie
);
/
CREATE TABLE videos OF t_video (
    foreign key (movie) references movies
)
/

现在，如果您尝试删除由视频引用的电影，Oracle 将抛出ORA-02292: integrity constraint.

触发器永远不是在常规表或对象表上强制执行外键约束的正确解决方案。因为

  1. 在 FOR EACH ROW 触发器中查询引用表效率低下，尤其是对于多行删除。外键针对此任务进行了优化。
  2. 由于读提交隔离级别，该操作在多用户环境中是不安全的。当另一个用户在不同的会话中添加子行时，触发器将传递我们的删除。
  3. 外键约束是标准。偏离标准是不好的做法，因为它使我们的代码更难维护。
  4. 触发器中强制执行的规则不会出现在数据字典中。这将使我们的同事感到困惑，阻止数据模型的逆向工程，并将剥夺优化者一些有用的信息，以得出有效的执行计划。

推荐阅读