使用触发器来确保数据一致性

发布于 2024-09-07 20:24:01 字数 634 浏览 1 评论 0原文

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

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 代码）来确保数据一致性。

这是做事的“正确方式”吗？

我怎样才能实现触发器？我可以实现一个存储过程来验证我的表。类似的东西

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

应该返回 0。

但是我怎样才能在提交时调用它呢？我不想在每行更新时调用它，就在提交完成之前，如果我的表无效，则可以回滚。

原文

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)
);

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

should return 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.

分享到QQ

分享到微博

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

发布评论

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

世俗缘 2024-09-14 20:24:01

正如我所描述的，可以通过物化视图（MV）和对 MV 的约束的组合来强制执行此操作在我的博客中。

这个想法是创建一个仅包含规则例外的 MV，然后设置一个在将行输入 MV 时始终失败的约束。像这样的东西：

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 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.

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;

回复收藏 0 原文