Oracle更新后触发创建公共数据库链接 [英] Oracle after update trigger creating public database link

问题描述

我有一个错误:尝试在更新触发器之后在一个简单的oracle中执行ddl命令时，出现"ORA-04092:无法在触发器中提交"的错误.列中的一个字段更新后，触发器需要创建公共数据库链接.来源:

create or replace
TRIGGER CreateLinkTrigger
after UPDATE of Year ON tableInit 
for each row
DECLARE
    add_link VARCHAR2(200);
BEGIN
IF :new.year = '2014'
then
    add_link := q'{create public database link p2014 connect to test14 identified by temp using 'ora'}';
    execute immediate add_link;
END IF;
END;

因此，如您所见，在新年激活后，我需要创建新的公共数据库链接. 因此，当我尝试用年值'2014'更新表'tableInit'时，出现ORA-04092错误. 有什么办法可以避免此错误，或者对此有其他解决方案? 谢谢...

解决方案

动态创建数据库链接似乎是一件不寻常的事情.您的架构通常应该是静态且稳定的.但是，如果必须的话，将更新和链接包装在一个过程中或只发出两个语句会更简单-大概执行更新的操作无论如何都受到相当的控制，否则，您将不得不处理触发此多次更新的多个人时间，甚至更是一团糟.

您可以通过在触发器中添加PRAGMA autonomous_transaction;来完成这项工作，如类似问题(创建视图而不是链接)所示在此答案中所述所述， 在此答案中的更多示例，您将在其中更改作业的匿名块以执行您的execute immediate.

最好在维护时段内，按时间表或从过程中提前创建未来几年的链接；而不是尝试将架构更改与数据更改相关联.

I have an error: 'ORA-04092: cannot COMMIT in a trigger' when trying to execute ddl command in one simple oracle after update trigger. Trigger needs to create public database link after one field in column is updated. Here is the source:

create or replace
TRIGGER CreateLinkTrigger
after UPDATE of Year ON tableInit 
for each row
DECLARE
    add_link VARCHAR2(200);
BEGIN
IF :new.year = '2014'
then
    add_link := q'{create public database link p2014 connect to test14 identified by temp using 'ora'}';
    execute immediate add_link;
END IF;
END;

So, as You can see i need to create new public database link after new year has been activated. So when i try to update table 'tableInit' with year value of '2014' i get ORA-04092 error. Is there any way to avoid this error, or another solution for this? Thanks...

解决方案

Creating a database link on the fly seems like an unusual thing to do; your schema should generally be static and stable. However, if you must, it would be simpler to wrap the update and the link in a procedure, or just issue two statements - presumably whatever performs the update is fairly controlled anyway, otherwise you'd have to deal with multiple people triggering this multiple times, which would be even more of a mess.

You can probably make this work by adding PRAGMA autonomous_transaction; to your trigger, as demonstrated for a similar issue (creating a view rather than a link) in this answer, but I'm not in a position to test that at the moment.

create or replace
TRIGGER CreateLinkTrigger
after UPDATE of Year ON tableInit 
for each row
DECLARE
    add_link VARCHAR2(200);
    PRAGMA autonomous_transaction;
BEGIN
    ...

You could also make the trigger submit an asynchronous job to perform the DDL, as described in this answer, and there's more of an example in this answer, where you'd change the job's anonymous block to do your execute immediate.

It would probably be better to just create the links for the next few years in advance during a maintenance window, or on a schedule, or from a procedure; rather than trying to associate a schema change to a data change.

这篇关于Oracle更新后触发创建公共数据库链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！