oracle数据库在级联和触发时遇到删除错误
问题描述:
我在数据库中有两个表,一个是PS_POST,一个是PS_STAR。oracle数据库在级联和触发时遇到删除错误
这里是有DDL:
--------------------------------------------------------
-- DDL for Table PS_POST
--------------------------------------------------------
CREATE TABLE "C##STY"."PS_POST"
( "POST_ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ,
"USER_ID" NUMBER,
"GROUP_ID" NUMBER DEFAULT 0,
"TIME" DATE,
"TITLE" VARCHAR2(200 BYTE) DEFAULT 'Default_title',
"STAR_NUMBER" NUMBER DEFAULT 0,
"CONTENT" VARCHAR2(4000 BYTE) DEFAULT 'Default_content'
) ;
--------------------------------------------------------
-- DDL for Index POST_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "C##STY"."POST_PK" ON "C##STY"."PS_POST" ("POST_ID") ;
--------------------------------------------------------
-- Constraints for Table PS_POST
--------------------------------------------------------
ALTER TABLE "C##STY"."PS_POST" MODIFY ("USER_ID" NOT NULL ENABLE);
ALTER TABLE "C##STY"."PS_POST" ADD CONSTRAINT "POST_PK" PRIMARY KEY ("POST_ID")
ENABLE;
ALTER TABLE "C##STY"."PS_POST" MODIFY ("POST_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table PS_STAR
--------------------------------------------------------
CREATE TABLE "C##STY"."PS_STAR"
( "USER_ID" NUMBER,
"POST_ID" NUMBER,
"TIME" DATE
) ;
--------------------------------------------------------
-- DDL for Index STAR_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "C##STY"."STAR_PK" ON "C##STY"."PS_STAR" ("USER_ID", "POST_ID")
;
--------------------------------------------------------
-- DDL for Trigger STAR_TRIGGER
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER "C##STY"."STAR_TRIGGER" after insert on PS_STAR
referencing new as new old as old
for each row
begin
update PS_POST
set
STAR_NUMBER = STAR_NUMBER + 1
where POST_ID = :new.POST_ID;
end;
/
ALTER TRIGGER "C##STY"."STAR_TRIGGER" ENABLE;
--------------------------------------------------------
-- DDL for Trigger STAR_DELETE_TRIGGER
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER "C##STY"."STAR_DELETE_TRIGGER" before delete on PS_STAR
referencing new as new old as old
for each row
begin
update PS_POST
set
STAR_NUMBER = STAR_NUMBER - 1
where POST_ID = :old.POST_ID;
end;
/
ALTER TRIGGER "C##STY"."STAR_DELETE_TRIGGER" ENABLE;
--------------------------------------------------------
-- Constraints for Table PS_STAR
--------------------------------------------------------
ALTER TABLE "C##STY"."PS_STAR" ADD CONSTRAINT "STAR_PK" PRIMARY KEY ("USER_ID", "POST_ID")
ENABLE;
ALTER TABLE "C##STY"."PS_STAR" MODIFY ("POST_ID" NOT NULL ENABLE);
ALTER TABLE "C##STY"."PS_STAR" MODIFY ("USER_ID" NOT NULL ENABLE);
所以我设置PS_STAR当帖子被删除被级联删除。我还有一个触发器,用于在删除星形记录时更新PS_POST表中的STAR_NUMBER。当我尝试删除PS_POST中的某个项目时,似乎PS_STAR中的触发器无法正常工作,并且出现错误:
ORA-04091: C##STY.PS_POST is mutating, trigger/function may not see it
如何解决此问题?提前致谢。
答
可能最好的方案是从PS_POST表中删除非规范化的 STAR_NUMBER列,并删除触发器 - 只需要计算PS_STAR记录的数量即可。除非在一些极端情况下,否则“表现非规范化”很少有理由。
但是,如果您确实需要保留它,则需要防止触发器在从中删除记录时尝试更新PS_POST表。这可以通过使用一个包在里面,而实现与一个全局变量是这样的:
create or replace package ps_post_pkg is
deleting_post boolean default false;
end;
然后添加两个声明 - 电平触发到PS_POST:
create or replace trigger ps_post_before_delete_stmt
before delete on ps_post
begin
ps_post_pkg.deleting_post := true;
end;
create or replace trigger ps_post_after_delete_stmt
after delete on ps_post
begin
ps_post_pkg.deleting_post := false;
end;
现在修改你START_DELETE_TRIGGER的身体:
begin
if not ps_post_pkg.deleting_post then
update PS_POST
set STAR_NUMBER = STAR_NUMBER - 1
where POST_ID = :old.POST_ID;
end if;
end;
现在,当记录从PO_POST中删除时,删除将级联到PS_STAR,但START_DELETE_TRIGGER将n不要尝试更新刚删除的PS_POST记录。
我将很多不相关的存储子句等等从DDL中删除掉了,所以我们可以更轻松地读取它。现在看起来这里根本就没有外键定义 - 我回去检查了一下,在我的编辑之前也没有! –