ORA-04091 and Compound Trigger (Oracle 11g)

Trigger

There are two common types: Row Trigger and Statement Trigger)

There are also: Instead of Trigger and Event trigger.

Example 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;

Example 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 error.
Tom Kyte has an article that explains ORA-04091 well.

Http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html

The excerpt is as follows:

Suppose wehave a table that includes des 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 serous 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.

The trigger wowould have looked something like this:

 

SQL <create or replace

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 <1)

15 then

16 raise_application_error

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

18 end if;

19 end;

20/

1 2 3 Next Page