带有嵌套的原子事务BEFORE INSERT/UPDATE触发器
问题描述:
目前我正在实现一个过程,该过程在模板外的某些相关表中创建了几行。因此,我的过程包含SAVEPOINT
后跟一些INSERT
语句在不同的表上,以及一个Cursor用于在引用新创建的主键时将更多行插入到其他表中。带有嵌套的原子事务BEFORE INSERT/UPDATE触发器
每个这些表的定义了一个BEFORE INSERT/UPDATE触发器,其目的到:
- 获得从序新的主键,如果没有在INSERT语句中定义(有这样的情况:我需要明确地设置主键稍后引用它在同一事务)
- 设置一些默认值,如果他们是NULL
- 设置审核字段(last_change_date,last_change_user,等..)
交易失败ORA-04091:表变异,触发/功能可能无法看到它
我的理解,我可以解决此,通过在每个触发器声明PRAGMA自治事务,但我的交易将不再是原子,因为它是所有这些数据集应该作为一个整体创建/插入的要求,或者它们都不是。
那么我在做数据库设计时做错了什么?
UPDATE:这是触发的代码
CREATE TRIGGER TRG_AUFTRAG_B_IU
BEFORE INSERT OR UPDATE
ON AUFTRAG
FOR EACH ROW
BEGIN
IF INSERTING THEN
IF :new.id is NULL or :new.id = 0 THEN
SELECT SEQ_AUFTRAG.nextval into :new.id from dual;
END IF;
IF :new.nummer is NULL or :new.nummer = 0 THEN
SELECT nvl(MAX(NUMMER),0)+1 INTO :new.nummer FROM AUFTRAG WHERE EXTRACT(YEAR from DATUM) = EXTRACT(YEAR from :new.DATUM);
END IF;
--DEFAULT Values
IF :new.BETR_GRENZWERTE_RELEVANT is NULL THEN
SELECT 0 INTO :new.BETR_GRENZWERTE_RELEVANT FROM dual;
END IF;
IF :new.DOKUMENTE_ABGELEGT is NULL THEN
SELECT 0 INTO :new.DOKUMENTE_ABGELEGT FROM dual;
END IF;
IF :new.EXT_ORG is NULL or :new.EXT_ORG < 1 THEN
SELECT 1 INTO :new.EXT_ORG FROM dual;
END IF;
:new.ERSTELLT_VON := nvl(:new.ERSTELLT_VON,user);
:new.ERSTELLT_DATUM := nvl(:new.ERSTELLT_DATUM,sysdate);
END IF;
:new.GEAENDERT_VON := user;
:new.GEAENDERT_DATUM := sysdate;
END;
答
你可以写它更紧凑是这样的:
CREATE TRIGGER TRG_AUFTRAG_B_IU
BEFORE INSERT OR UPDATE
ON AUFTRAG
FOR EACH ROW
BEGIN
IF INSERTING THEN
:new.id = NVL(NULLIF(:new.id, 0), SEQ_AUFTRAG.nextval);
--DEFAULT Values
:new.BETR_GRENZWERTE_RELEVANT := NVL(:new.BETR_GRENZWERTE_RELEVANT, 0);
:new.DOKUMENTE_ABGELEGT := NVL(:new.DOKUMENTE_ABGELEGT, 0);
IF :new.EXT_ORG is NULL or :new.EXT_ORG < 1 THEN
:new.EXT_ORG := 1;
END IF;
:new.ERSTELLT_VON := nvl(:new.ERSTELLT_VON,user);
:new.ERSTELLT_DATUM := nvl(:new.ERSTELLT_DATUM,sysdate);
END IF;
:new.GEAENDERT_VON := user;
:new.GEAENDERT_DATUM := sysdate;
END;
唯一的 “问题” 是这部分
IF :new.nummer is NULL or :new.nummer = 0 THEN
SELECT nvl(MAX(NUMMER),0)+1 INTO :new.nummer
FROM AUFTRAG
WHERE EXTRACT(YEAR from DATUM) = EXTRACT(YEAR from :new.DATUM);
END IF;
这一个你应该放入你的程序或在一个语句触发器(即没有FOR EACH ROW
子句)如下:
CREATE TRIGGER TRG_AUFTRAG_B_A
AFTER INSERT ON AUFTRAG
BEGIN
UPDATE
(SELECT ID, NUMMER,
ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR from DATUM) ORDER BY ID) as N
FROM AUFTRAG)
SET NUMMER = N
WHERE NUMMER IS NULL;
END;
+0
非常感谢!不仅为了让这些概念 - 变异表 - 对我来说很清楚,而且还用于提示代码的改进。 –
请向我们展示您的触发器代码。实际上你列出的动作对于触发器来说是非常典型和有用的,它们应该没有任何问题地工作。 –
看起来你正在触发器内的同一个表中执行一个'select'。根据你所说的触发器需要做的事情,没有任何“选择”的要求。你说得对 - '编译自主事务'不能解决这类问题。 –
啊!我想我已经看到了什么问题......这是:新:NUMMER部分是不是?这是为了创建一个业务需求的识别号码。它会在AFTER INSERT TRIGGER中做到这一点吗? –