使用触发器来确保数据一致性 [英] using triggers for ensuring data consistency

问题描述

我有一个存储在表中的层次结构.每个元素都有一个指向其上一个，下一个和父级的指针

I have a hierarchical structure stored in a table. Each element has a pointer to its previous, next and a parent

create table CATALOGUE
(
  NAME VARCHAR2(300) not null,
  NEXT_ID NUMBER(38),
  PARENT_ID NUMBER(38),
  PREVIOUS_ID NUMBER(38),
  XID NUMBER(38)
);

我有一个Java应用程序，它使用O/R映射来访问和修改此表.有时我的目录损坏了，例如它们是没有相同父项的链接元素.我想知道是否可以使用Oracle触发器或其他纯SQL技术(没有Java代码)确保数据一致性.

I have a java application, which uses O/R mapping to access and modify this table. Sometimes my catalog got corrupted, e.g. they are linked elements which don't have the same parent. I'm wondering if I can ensure data consistency using Oracle triggers or other pure SQL technique (without java code).

这是做事的正确方法"吗?

Is this "the correct way" of doing things?

如何实现触发器?我可以实现一个存储过程来验证我的表.像

How can I implement a trigger? I can implement a stored procedure which would validate my table. Something like

select count(*) 
from catalogue c1, catalogue c2 
where c1.next_id = c2.previous_id and c1.parent_id != c2.parent_id

应返回0.

但是如何在提交时调用它呢?我不想在每次行更新时都调用它，就在提交完成之前，如果我的表无效，则有可能回滚.

But how can I call it on commit? I don't want to call it on every row update, just before the commit is finished with a possibility to rollback if my table is not valid.

推荐答案

通过将物化视图(MV)和对MV的约束结合使用，可以强制执行此操作，如我所描述的

It may be possible to enforce this by a combination of a materialized view (MV) and a constraint on the MV as I have described here in my blog.

该想法将是创建一个仅包含规则异常的MV，然后创建一个约束，该约束在将一行输入到MV中时始终会失败.像这样:

The idea would be to create an MV that held only exceptions to the rule, and then to have a constraint that always fails when a row is entered into the MV. Something like this:

create materialized view check_mv
refresh complete on commit as
select 1 dummy
from catalogue c1, catalogue c2 
where c1.next_id = c2.previous_id and c1.parent_id != c2.parent_id

alter table check_mv
add constraint check_mv_chk
check (1=0) deferrable;

这篇关于使用触发器来确保数据一致性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！