ORA-04091和Compound Trigger(Oracle 11g)

ora-04091和compound trigger(oracle 11g)

首页 → 数据库技术

背景：

ora-04091和compound trigger(oracle 11g)

[日期：2013-12-14] 来源：Linux社区  作者：chncaesar [字体：]

Trigger

常见有两种:行(Row Trigger)和语句(Statement Trigger)

还有：Instead of Trigger和Event trigger。

例子1-Row Trigger：

CREATE OR REPLACE TRIGGER client AFTERINSERT ON tt1 FOR EACH row

BEGIN

 dbms_application_info.set_client_info(userenv('client_info')+1 );

END;

例子2-Statement Trigger

CREATE OR REPLACE TRIGGER client_1 AFTERINSERT ON tt1

BEGIN

 dbms_application_info.set_client_info(userenv('client_info')-1 );

END;

ORA-04091错误
Tom Kyte有一篇文章很好的解释了ORA-04091。

部分摘抄如下：

Suppose wehave a table that includes country currency combinations with a primarycurrency. The following is sample data:

CountryCurrency  Primary_Currency

US      USD      Y

US      USN      N

US      USS      N

We need toenforce the rule that at most one currency can be primary for a given country.We have a BEFORE UPDATE trigger on the above table for each row (usingautonomous transaction to avoid the mutating error) to check whether thecountry has any primary currency.

That was allI needed to read. I knew they had a serious bug on their hands when Iread—paraphrasing:

At most one currency can be primary (we have a constraint that crosses rows in the table).
We have a . . . trigger.
We are using an autonomous transaction to avoid the mutating table error.

BibiGPT-哔哔终结者

B站视频总结器-一键总结 音视频内容

871

查看详情

The trigger would have looked something like this:

SQL

  2    trigger currencies_trigger

  3    before update on currencies

  4    for each row

  5    declare

  6      PRAGMA AUTONOMOUS_TRANSACTION;

  7      l_cnt number;

  8    begin

  9        select count(*)

 10          into l_cnt

 11          from currencies

 12        where primary_currency='Y'

 13            and country = :new.country;

 14        if ( l_cnt

 15        then

 16            raise_application_error

 17            (-20000, 'only one allowed');

 18        end if;

 19    end;

 20    /

Oracle EBS R12 for Linux安装

MySQL使用Federate引擎实现操作本地表映射远程操作表

相关资讯       ORA-04091 

图片资讯      

本文评论 　　查看全部评论 (0)

评论声明

最新资讯

本周热门

Linux公社简介 - 广告服务 - 网站地图 - 帮助信息 - 联系我们
本站（LinuxIDC）所刊载文章不代表同意其说法或描述，，仅为提供更多信息，也不构成任何建议。


Copyright © 2006-2011　Linux公社　All rights reserved 浙ICP备06018118号