时间戳

首页 > 解决方案 > 甲骨文数据库。在内部插入带有 Merge 状态的触发器。表正在变异

oracle - 甲骨文数据库。在内部插入带有 Merge 状态的触发器。表正在变异

问题描述

我有两个共享 Oracle 数据库的后端系统（旧的和新的）。

在旧系统中，为了保存客户数据，有两个表

客户_A

ID   NAME  ETC
1    PETE  ....

客户_B

ID NAME ETC
1  JOSH ...
2  ROSS ...

在新系统中，我创建了一个名为All_Costumer的新表，用于连接这些表。这个新表分别包含类型 A 和 B 的客户 ID。

All_客户

ID           ID_CUSTOMER_A    ID_CUSTOMER_B
A19E----D2B0     1                 null
A19E----D2B1    null                 1
A19E----D2B2    null                 2

因此，当新系统创建类型 A 的新客户时，数据将插入到customer_A和All_Customers表中，客户类型 B 也一样。

目前，旧系统也在工作，当创建 A 类型的新客户时，数据仅插入到 customer_A 表中，但我也需要 All_Customers 中的数据。

为了解决这个问题，我创建了一个内部带有 MERGE INTO 语句的 TRIGGER，如果此表上不存在，则在 All_Customers 中插入一行（当旧系统创建类型 A 的新客户时）

CREATE OR REPLACE TRIGGER customers_trg

AFTER INSERT
ON customer_A

FOR EACH ROW

DECLARE
variables that doesn't matters

BEGIN
    MERGE INTO all_customers
        USING (SELECT :new.id id FROM customer_A where id = :new.id) customer
        ON (all_customers.id_customer_a = customer.id)
    WHEN NOT MATCHED THEN
        INSERT (id, id_customer_a)
        VALUES (SYS_GUID(), :new.id, null);
    COMMIT;
END;

但是当我尝试从旧系统创建新客户时，我收到此错误：

ORA-04091: table **customer_A** is mutating, trigger/function may not see it

有什么办法解决这个问题吗？我尝试PRAGMA AUTONOMOUS_TRANSACTION;在 DECLARE 部分添加，但没有奏效。

注意：我不能修改旧系统

标签： oracleplsqldatabase-trigger

解决方案

直接的问题是您正在table_a针对该表查询触发器；但你不需要。您的合并查询

SELECT :new.id id FROM customer_A where id = :new.id

可以简单地做

SELECT :new.id id FROM dual

即该子句变为：

...
        USING (SELECT :new.id id FROM dual) customer
        ON (all_customers.id_customer_a = customer.id)
...

您也不能在触发器中提交 - 除非它是自主的，这不应该是。您说您已经尝试过了，但是如果回滚插入，它会中断，因为合并的行仍然存在。因此，希望该提交只是尝试和拒绝该方法的结果。

但无论如何，它在这个 db<>fiddle中有效。

如果您没有添加 GUID，则可以通过视图获得相同的效果：

create or replace view all_customers (id_customer_a, id_customer_b) as
select id, null from customers_a
union all
select null, id from customers_b;

db<>小提琴

推荐阅读