div.b-mobile {display:none;}

Monday, May 12, 2008

Another 'question of the day'...

This time, the question is not because it was poorly phrased.  It is because it is regarding an area I want to bring to the attention of a larger audience.

The question went like this:

 

<quote>

Suppose, I have a table having country currency combinations with a primary currency. The below is a sample data.

Country        Currency    Primary_Currency
US USD Y
US USN N
US USS N




And, I have a before update trigger on the above table for each row

(using autonomous transaction to avoid mutating error)
to check whether


the country has any primary currency. I am giving the below update.



</quote>




Ok, serious red flags here - They have a business rule apparently that AT MOST one currency can be "primary" for a given country - that is - COUNTRY must be UNIQUE when PRIMARY_CURRENCY='Y' (what I call selective uniqueness).  Now, I might suggest we are missing an entity in this model - that is, the primary currency 'flag' should not be there, but rather there should be two entities - primary_currency, and other_currencies - perhaps.  Then, the problem is simple - COUNTRY would be the primary key of the primary_currency table - and COUNTRY,CURRENCY would be the primary key of the other - an in fact, if the rule was "A country may have at most one primary currency and must have at least one primary currency" - then enforcing that more complex one would be trivial - just add a foreign key from other_currencies to primary_currency - and you are done.  Could not be easier. 



But - back to the red flags:




  • I have a before update trigger:  I have written many times that you cannot enforce integrity constraints that cross objects (tables) in the database (like referential integrity does), nor constraints that cross rows in a single table (like uniqueness does) without using the LOCK TABLE command.  Since reads are not blocked by writes - and you cannot see others uncommitted work - you need to serialize access.


  • Using autonomous_transaction:  These are almost universally "misapplied".  They have one use - in a "LOG_ERRORS" routine, called from a WHEN OTHERS exception handler (which would of course be followed by RAISE;)


  • Using autonomous_transaction to avoid mutating error:  Ok, now I know we are in serious trouble here.  If you have to use an autonomous transaction to avoid a mutating table constraint (not error, a constraint, a subtle warning to the developer "what you are trying to do is something you should not be doing") - you have a bug in your code, I'm 99.999999% certain of that - I would be very hard pressed to come up with a valid reason in real life to use an autonomous transaction to avoid a mutating table constraint.



So, we have these HUGE red flags - all of which get proven out in the next bit - everything I guessed:




  • This does not work in single user scenarios


  • This does not work in multi user scenarios


  • In short, this does not work - it is a huge bug



Are shown true:




Update Country_Currency_Ref
set is_primary_currency = 'Y'
where (country_code = 'US' and currency_code = 'USN');




And the trigger is working fine and giving the correct message that it cannot be updated as there

is already one primary currency against that country Now I update the data as below

(No primary currency)



Country        Currency    Primary_Currency
US USD N
US USN N
US USS N




and try to update the data with the below condition



Update Country_Currency_Ref
set is_primary_currency = 'Y'
where (country_code = 'US' and currency_code <> 'USD');




In this case, the trigger is failing and updating other two records. Why is this behaviour?


And how can we over come the same?




Sorry, the trigger is not failing, the trigger is just doing precisely what it was coded to do.  The problem is the person coding the trigger doesn't understand transactions and concurrency controls and how the database works.  By using the autonomous transaction to query the table - it is as if they started a brand new session to query that table.  Of course that brand new session cannot see any changes made by not yet committed!  Including the changes the trigger is trying to validate.  This is PRECISELY why we have a mutating table constraint - your row level trigger is being fired as the rows are modified - if you were able to query the table in the trigger without the autonomous transaction - you would see the table half updated (and then what, what a MESS that would be).  So, since Oracle is evil and prevents you from doing something bad here - you use an autonomous transaction - you get a consistent view of the table to be sure, but you cannot see the data you are trying to validate!!!  That makes validation pretty hard.



Even if you did the "common mutating table constraint workaround" - by deferring your reads until the AFTER (not for each row) trigger, you cannot do this check in a trigger without LOCKING THE TABLE.  You would have to serialize access in order to prevent two concurrent sessions from each creating a primary currency record at the same time. 



I would prefer a two table solution here:





ops$tkyte%ORA11GR1> create table primary_currency
2 ( country varchar2(2) primary key,
3 currency varchar2(3)
4 )
5 organization index
6 /

Table created.

ops$tkyte%ORA11GR1> create table other_currencies
2 ( country varchar2(2),
3 currency varchar2(3),
4 constraint other_currencies_pk
5 primary key(country,currency),
6 constraint must_have_at_least_one_primary
7 foreign key(country)
8 references primary_currency(country)
9 )
10 organization index
11 /

Table created.


That solves the problem quite elegantly - and even enforces the complex "must have a primary currency" constraint if you implement the foreign key.  However, if they were to keep this single table, then I would say:




drop your trigger.



and promise to never use autonomous transactions to avoid mutating table constraints (I said "mutating table CONSTRAINTS", not error - the error is you using an autonomous transaction to destroy your data integrity) ever again.



add:



create unique index only_one_can_be_primary on country_currency_ref

( case when is_primary_currency = 'Y' then country_code end );




 



I hate triggers

I hate autonomous transactions


I hate when others



If we removed those three things from PLSQL - we would solve 90% of all application bugs I think... No kidding.  I know, in the right hands, they are powerful tools.  However, they fall into the wrong hands far too often.

POST A COMMENT

48 Comments:

Blogger mathewbutler said....

Hi Tom,

Good post. With a view to trying to change tack slightly ( the last few posts have been slightly rantish )...I do like rants but...

You are known for saying that you learn something new about Oracle every day. As a suggestion for a post, how about you document all the things you learnt over the period of a week? If you also included, why you encountered the issue and how you arrived at the learning, it might be a useful to anyone reading who wants some insight into efficient means of problem solving.

All the best.

Mat.

Mon May 12, 11:56:00 AM EDT  

Blogger Toon said....

In your preferred 2-table solution (which I fully agree with, and most likely they probably already have a table with country as its primary key) you would need another constraint to ensure that a primary currency is not a 'other' currency as well, not?

So, if we would have had assertion-support, something like this should be added:

create assertion Primary_currency_cannot_be_other_currency_as_well as
check(
not exists(select 'a joined tuple'
from primary_currency p
,other_currency c
where p.country = c.country
and p.currency = c.currency));

(btw: this is what I refer to as a tuple-in-join rule in my AM4DP book. A very common pattern of constraint).

Toon

Mon May 12, 03:46:00 PM EDT  

Blogger Stew said....

Gosh I sure hope Oracle doesn't listen to you and drop Triggers, Autonomous Transactions and WHEN OTHERS from the next version! For those of us who know how to use them correctly, they're pretty handy! :-)

Maybe the Oracle installation should default to not granting developers access to those features. Then the programmer has to specifically request them from their DBA, maybe after passing a little test on how to use them! I'd guess the CREATE TRIGGERS right could be taken away easily, maybe not so much on the others. :-)

Another good sermon. Thanks.

Mon May 12, 03:54:00 PM EDT  

OpenID sorek said....

Sorry if this is a duplicate. Comment entry screen is giving me issues.

I had a similar problem Jan 5 2007. See this post on comp.databases.oracle.server. In that post I was describing "a question, one correct answer and one or more incorrect answers" but what I really had was sn entity (like a country) that had an attribute (like currency) and a column to indicate whether the value of the attribute was the default (what the OP here called "primary").

Mon May 12, 06:12:00 PM EDT  

Blogger oraclenerd said....

"I hate triggers

I hate autonomous transactions

I hate when others

If we removed those three things from PLSQL - we would solve 90% of all application bugs I think..."

I wouldn't want them to go away...I think they're kind of like Toad is to Database Developers, the right tool mostly in the wrong hands. We have people at work who don't even know how to find sql*plus yet are considered Database Developers. How?

I also just answered a comment in one of my posts pointing me to autonomous transactions to perform a specific task, I told them I only really think about them when it concerns logging. So glad I said that!

Mon May 12, 09:38:00 PM EDT  

Blogger Mathieu said....

Your 2-table solution is pretty.

If you would like to have it as a single table, you could also union the two tables in a view.

With the instead-of-triggers for the insert/update/delete it would behave like a single table.

If you are saying 'triggers should be removed from the database', I would refine it to 'no triggers on tables'. Instead-of-triggers on views/XML tables are very powerful.

Tue May 13, 03:39:00 AM EDT  

Blogger Wiktor said....

Hi,

Great post. I recently had a problem to enforce similiar constraint.

The constraint that I wanted to enforce was that having the row with "a", "b" values in table, I wanted to prevent inserting "b", "a" row.

The proper solution is then
create unique index my_unq_constr on my_table (case when col1 < col2 then col1 else col2 end, case when col1 < col2 then col2 else col1 end);

It's a shame that one couldn't use
(min(col1,col2), max(col1,col2)) :-)

Tue May 13, 04:04:00 AM EDT  

Blogger Thomas Kyte said....

@Mathieu

don't even get me started on "instead of trigger" - an aberration and is even more misused and abused and misunderstood and misapplied than triggers on tables...

Many developers love "magic", instead of triggers let them to "magic" - 'Hey look, insert into this thing and look at the 500 things that actually happen'.

Some of the least performing, hardest to understand, impossible to maintain and buggy applications - built 100% on views with instead of triggers - instead of triggers written with a 'single user mentality' (that is, they don't really work when you have two people going at the same time - I'm not saying they would get it right if they used stored procedures - but it would be so much easier to fix)....

Tue May 13, 06:11:00 AM EDT  

Blogger JCFlack said....

I had a similar problem to the original, and solved it with the unique index based on a function as you described. There are two problems with this solution. First of all, it solves the "may not be more than one primary" problem, but does not solve the "must be at least one primary" problem. Secondly, because this is a unique index, not a unique constraint (you can't define a unique constraint with a function), there is no way to defer enforcement. In other words, if in a single transaction I want to change one row from primary to not primary, and another row from not primary to primary, uniqueness will not be violated when the transaction is committed, but it will be violated temporarily during the transaction. With a unique constraint, you would simply make it deferrable, but you can't do that with a unique index. Of course, since the "must be at least one" rule isn't being enforced, I can make one row not primary in one transaction, and make one row primary in another, but I MUST do it in that order.

I'd switch to the two table solution, but I think I'd have to make too many application changes. Instead, I think I'll create a new column same as the PK, use a before insert and update trigger to maintain it with the same function as the index, and change the unique index to a deferrable unique constraint on the new column.

Tue May 13, 08:58:00 AM EDT  

Anonymous aja said....

I hate triggers
I hate autonomous transactions
I hate when others

To that I would add
I hate select across commit
I would love if Oracle were to add an init.ora parameter, something like
_SELECT_ACROSS_COMMIT = ALLOW | DISALLOW
'Cause some developers just do not get it.

It appears to me, having worked in a few shops now, that new developers lack the knowledge that comes from working with experienced (and I mean really experienced - years of experience, not months) developers and updating/modifying/supporting well written and robust code. Code that handles exceptions correctly. Code that handles multi-query transactions correctly. Code that does not do any unnecessary work. Code that has plenty of internal documentation and debugging.

The new developers coming out of school, in my experience, do not have the opportunity to learn how to do 'it' in the real world - getting the correct result set was acceptable in school, but now we also care about HOW you got the correct result.

They want to do a good job, they just do not know how. And there is no one to show them how.

Tue May 13, 09:18:00 AM EDT  

Anonymous Brian Tkatch said....

Tom, those TABLEs would require NOT NULLs, right? Otherwise, no guarantee that there actually is a primary currency. Cool idea with the FK on Country.

@Toon

That assertion can be made with a little added. That is, ADD a primary currency COLUMN to the other_currencies TABLE, and extend the must_have_at_least_one_primary FK to include it. Then, ADD a CONSTRAINT CHECK (primary_currency <> currency).

Just a thought.

Tue May 13, 09:31:00 AM EDT  

Blogger Thomas Kyte said....

@JCFlack

Well, since the rule is:

AT MOST one primary...

then the transaction that sets a currency to primary should be and update that unflags any 'Y' records and flags the one you want - optionally using bulk collect to ensure yourself you set a row to 'Y' if you want (to make sure your country/currency was actually found, part of your "at least one" processing, which can safely be done in the application in a nice secure stored procedure since it does not cross rows in the table - you are sure there is at least one, because you just set it)

like this:

ops$tkyte%ORA11GR1> create table t
2 ( country_code varchar2(2),
3 is_primary varchar2(1),
4 currency varchar2(3)
5 )
6 /

Table created.

ops$tkyte%ORA11GR1> create unique index t_idx on t( case when is_primary = 'Y' then country_code end );

Index created.

ops$tkyte%ORA11GR1> insert into t (country_code,is_primary,currency) values ( 'US','Y','USD' );

1 row created.

ops$tkyte%ORA11GR1> insert into t (country_code,is_primary,currency) values ( 'US','N','XXX' );

1 row created.

ops$tkyte%ORA11GR1> insert into t (country_code,is_primary,currency) values ( 'US','N','YYY' );

1 row created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> variable primary_currency varchar2(3);
ops$tkyte%ORA11GR1> variable country_code varchar2(2);
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> exec :country_code := 'US'; :primary_currency := 'XXX';

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select * from t;

CO I CUR
-- - ---
US Y USD
US N XXX
US N YYY

ops$tkyte%ORA11GR1> declare
2 type array is table of t%rowtype index by binary_integer;
3 l_data array;
4 begin
5 update t
6 set is_primary = decode(currency,:primary_currency,'Y','N')
7 where country_code = :country_code
8 and (currency = :primary_currency or is_primary = 'Y')
9 returning country_code, is_primary, currency bulk collect into l_data;
10
11 for i in 1 .. l_data.count
12 loop
13 dbms_output.put_line
14 ( '*** ' || l_data(i).country_code || ', ' ||
15 l_data(i).is_primary || ', ' || l_data(i).currency );
16 end loop;
17 end;
18 /
*** US, N, USD
*** US, Y, XXX

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select * from t;

CO I CUR
-- - ---
US N USD
US Y XXX
US N YYY

Tue May 13, 09:33:00 AM EDT  

Blogger Thomas Kyte said....

@Aja -

you have hit the nail on the proverbial head.


when I started, 21 years ago, I had a mentor. This guy taught me everything. How to code defensively, how to do a code review, how to write maintainable code, how to write code to be proud of, how it doesn't necessarily matter if it is fast - if it isn't safe, and so on.

Tue May 13, 09:36:00 AM EDT  

Blogger Thomas Kyte said....

@Brian

not sure what tables you mean - my two table has primary keys, the keys are NOT NULLABLE, the at least one is enforced by the foreign key.

and if the primary currency was to be NOT NULL (yet another rule) then yes, it would be NOT NULL (it would not be entirely invalid for the primary currency to be "unknown" depending on your needs)

Tue May 13, 09:46:00 AM EDT  

Anonymous Anonymous said....

Tom,
Can you show an example of using "WHEN OTHERS"?
Can't think of it in better hands than yours!
Thanks!!
-GND

Tue May 13, 12:55:00 PM EDT  

Blogger Drew said....

As an application support rep, I liked to use triggers to "fix" customer data issues. It was much easier than getting a Dev resource! As a developer, I never use them - it just feels dirty. There always seems to be a way to do it without triggers. It helps that I look at my code as something that I might have to troubleshoot later - makes me add better comments, etc. :)

Wed May 14, 10:51:00 AM EDT  

Anonymous Anonymous said....

Hi Tom,

in 11g one of your red flags has gone away: WHEN OTHERS without RAISE or RAISE_APPLICATION_ERROR will throw a compile error.

Regards
Wolfgang

Wed May 14, 04:41:00 PM EDT  

Anonymous aja said....

@Anonymous

I will give you one guess who gets credit for that.

Bet you didn't know he has that much pull.

Wed May 14, 05:17:00 PM EDT  

Blogger JCFlack said....

My understanding is that WHEN OTHERS without a RAISE or RAISE_APPLICATION_ERROR generates a warning, not a fatal error. I hope that such a thing will compile - when I write a PL/SQL web application, I need to write an appropriate error page if an exception is raised. I call a procedure that uses HTP procedures but I don't raise a new exception. An exception would just show the user a 500 page - not much information there to help us debug.

Thu May 15, 12:44:00 PM EDT  

Anonymous Bauer Wolfgang said....

JCFlack,

you are right. It is just a warning not a compile error:
PLW-06009: procedure "XYZ" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

Regards
Wolfgang

Thu May 15, 02:07:00 PM EDT  

Blogger Gary Myers said....

In 11g (not sure about 10g), you can choose to treat them as errors or warnings.
With
alter session set PLSQL_WARNINGS = 'ERROR:ALL';
you'll get a compilation error.

Thu May 15, 07:04:00 PM EDT  

Anonymous Anonymous said....

Hi Tom

Sorry for posting here something which is a different from the subject you were posting.
I am a fan of your books and i am impatient waiting for a new one as you announced. "Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions" should have 3 parts. What can you say about your plans to publish new books? About Oracle 11g etc......
Best regards
Adrian

Fri May 16, 11:15:00 AM EDT  

Anonymous Mark Brady said....

"However, they fall into the wrong hands far too often."

I think that's because there are far too many wrong hands, not necessarily because of the commands.

Mon May 19, 10:42:00 AM EDT  

Anonymous Martin said....

Tom - sorry completely off topic, but wondering what laptop you are using these days?
I need to buy one and will be using it primarily for Oracle stuff - personal edition etc so interested to know what you use

Thanks

Martin

Thu May 22, 12:43:00 PM EDT  

Blogger Narendra said....

Tom,

Would you mind suggesting how to address the issue raised by Toon above ?
In your preferred 2-Table solution, what will be the best way to enforce the rule that "A primary currency is not an other currency" ?

Tue May 27, 05:00:00 AM EDT  

Blogger Thomas Kyte said....

@Narenda -

an on commit fast refresh materialized view could be used - just join the two together and have a constraint on it such that any time there is a row in the materialized view - it fails (we want no joins to actually 'work')

ops$tkyte%ORA10GR2> create table primary_currency
2 ( country varchar2(2) primary key,
3 currency varchar2(3)
4 )
5 /

Table created.

ops$tkyte%ORA10GR2> create materialized view log on primary_currency with rowid including new
2 values;

Materialized view log created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table other_currencies
2 ( country varchar2(2),
3 currency varchar2(3),
4 constraint other_currencies_pk
5 primary key(country,currency),
6 constraint must_have_at_least_one_primary
7 foreign key(country)
8 references primary_currency(country)
9 )
10 /

Table created.

ops$tkyte%ORA10GR2> create materialized view log on other_currencies with rowid including new
2 values;

Materialized view log created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create materialized view primary_is_not_other
2 refresh fast
3 on commit
4 as
5 select a.rowid arid, b.rowid brid
6 from primary_currency a, other_currencies b
7 where a.country = b.country
8 and a.currency = b.currency
9 /

Materialized view created.

ops$tkyte%ORA10GR2> alter table primary_is_not_other
2 add constraint primary_curr_cannot_be_other
3 check (arid = brid)
4 /

Table altered.

ops$tkyte%ORA10GR2> insert into primary_currency values ('xx', 'abc' );

1 row created.

ops$tkyte%ORA10GR2> insert into other_currencies values ('xx', 'def' );

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into primary_currency values ('US', 'USD' );

1 row created.

ops$tkyte%ORA10GR2> insert into other_currencies values ('US', 'USD' );

1 row created.

ops$tkyte%ORA10GR2> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (OPS$TKYTE.PRIMARY_CURR_CANNOT_BE_OTHER) violated

Tue May 27, 06:40:00 AM EDT  

Blogger Narendra said....

Thanks Tom.
The example makes the solution crystal clear (as always).

Wed May 28, 12:02:00 PM EDT  

Blogger Jerry said....

Tom you had nice illustration of the issue there.. Anyway, if we remove triggers, will it reduce application bugs? I dont think so.. they are just so everywhere.

Thu Jun 12, 02:53:00 PM EDT  

Blogger Thomas Kyte said....

@Jerry

if we remove triggers, will it reduce application bugs? I dont think so.. they are just so everywhere.

but they WON'T be just "so everywhere" if we removed them.

for almost every trigger removed, the number of software bugs on the planet will be reduced.

Thu Jun 12, 03:19:00 PM EDT  

Anonymous Anonymous said....

You made a good point, although some commentators seem to have missed the wood for the trees a bit.Your life is so wonderful,Reading your article is a kind of enjoyment.Thank you.


Tactical Flashlights
r c helicopter
video game

Mon Jun 23, 02:26:00 AM EDT  

Anonymous Jan said....

I'm a bit surprised you're saying to not use trigger. Doesn't really match with what you said about 'put you're code as close to the data as possible'.

I have this rule that I have to create a code for each record in the form of yydddXX (year, day and sequence number). I would populate the field in an insert-trigger (via a function offcourse). Is their a reason not to?

Jan

Fri Jul 18, 06:38:00 PM EDT  

Blogger Thomas Kyte said....

@jan

triggers are just evil. They are not "next to the data", they subvert the data - they are like "magic", "side effects", "hidden"

A code in the form of

yydddxx

ouch. ouch ouch ouch.

populate the field in the insert if you must - via that function. At least then it is not automagic, can be overridden whenever needed - and it will be.

data lives a long time, applications don't. Your followers in the future will be hating your code - when it starts repeating.


"intelligent keys" are dumb :)

Fri Jul 18, 08:12:00 PM EDT  

Blogger Thomas Kyte said....

@jan again

and when I said "your code", I didn't mean code you write, I mean that "code field" you have to deal with. yy is bad, yyddd is bad as part of a code - makes it "intelligent" meaning "see, we've hidden some data in here for you! go for it"

Fri Jul 18, 08:19:00 PM EDT  

Anonymous Jan said....

The 'code' is an train-request-number, which they've been using long time. It just data to store and show, no PK, no calculation depending upon it afterwards.

So your advise is to never use triggers? Even autogenerated sequences for the PK? I don't see much harm in:

IF :new.id IS NULL THEN
:new.id := get_sequence;
END IF;

I have to make one exception though, and this it when using apex (not used on the train-project). I first started of writting my own inserts, but not using the automated row processing means developmenttime * 10. So triggers being my last resort.

And triggers are not magic, as long as if everyone knows they are being used. (It's not 'black' magic you're car won't start, as a driver you should know it runs out of gas after a while.. even though you didn't ask for it).

But I get the point you're trying to make. Putting complex logic into the trigger gets easier overlooked. I'll consider this advise whenever I'm tempted to use the 'CREATE TRIGGER'-command again. Thanks.

Regards, Jan

Sat Jul 19, 04:44:00 AM EDT  

Anonymous Jan said....

data lives a long time, applications don't. Your followers in the future will be hating your code - when it starts repeating.

I misinterpeted this the first time, now I'm getting it, and you are totally right. Thank you Tom.

Sat Jul 19, 04:58:00 AM EDT  

Blogger dina said....

I have gone through your two table solution.You need to add some other constraint to make sure that primary key is not other currency.
======================
dina123
Trivia Game Challenge

Mon Aug 18, 01:34:00 AM EDT  

Blogger Thomas Kyte said....

@dina123

we did that already

this link demonstrates that.

Mon Aug 18, 07:34:00 AM EDT  

Anonymous Anonymous said....

Hi,

I have AfterInsert Trigger on a table which has to select an id from the same table based on the inserted value and inserts a record into another table.
This trigger was giving mutating trigger error. I have added Autonomous_transaction and given commit inside the trigger.
but now, when the original insert rollbacks, the insert inside the trigger is commiting which should not happen. How to solve this/ Please advice.

Thanks in advance.
shiren.

Sun Jul 05, 03:01:00 PM EDT  

Blogger Thomas Kyte said....

@last anonymous person

first - read the entire article over and over and over - many many times....


and then, and only then, provide as a SPECIFICATION what you are actually trying to do, I'm not at all sure what your "goal" is. Don't post your broken, incorrect implementation (that doesn't tell us what you want to do after all) - but do tell us IN A SPECIFICATION what you are trying to accomplish.

Mon Jul 06, 04:58:00 AM EDT  

Blogger Sireesha said....

I have a table 'A1'. Whenever a record inserts into this table, i want to select value from the same table based on newly inserted value. And then insert a record into another table 'B1'

Now, i have written a AfterInsert trigger on table 'A1' to insert into table 'B1'. But this trigger is giving mutating trigger error. So i have used Autonomous_transaction and commit in the trigger (which am not sure can be used).This solved the problem, but when the original insert on A1 fails also, a record is inserting into table 'B1' which should not happen.
Please advice.
Hope I am clear this time.

Mon Jul 06, 06:02:00 AM EDT  

Blogger Thomas Kyte said....

@Sireesha

something is fishy about what you say. you say you have a trigger AFTER INSERT (must be FOR EACH ROW, else you would not get mutating table constraint).

You say in this trigger, you use an autonomous transaction.

You say this trigger can see the newly inserted row

but - IT CANNOT, it is not committed yet, the autonomous transaction *cannot see it* - period.


In the AFTER INSERT FOR EACH ROW trigger, if you need to access the newly inserted record ( which is what you state your goal to be ), then just access the :new record


:new.column1, :new.column2, :new.column3, .....

they are all already there, you do not need to select them out again. This is not SQL server.

Mon Jul 06, 02:19:00 PM EDT  

Anonymous Anonymous said....

I have moved select statement to function, now it is wroking.
But is it correct way of doing it?
When i remove AUTONOMOUS_TRANSACTION from function it is giving mutating error.


Table A1 has columns Ac1,AC2,Ac3.
Table B1 has columns Bc1,BC2,BC3.




CREATE OR REPLACE TRIGGER T_INS_AFTER_A1
AFTER INSERT ON A1 FOR EACH ROW

BEGIN


FOR l_rec IN (SELECT * FROM B1 WHERE Bc3 =
(SELECT f_get_value(:new.AC3) from DUAL)
) LOOP

-- Inserts into B1
BEGIN
INSERT INTO B1
( BC1
,BC2
,BC3
)
VALUES
(V1
,V2
,V3);

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(SQLCODE,
'**'||SQLERRM);
END;

END LOOP;

EXCEPTION
WHEN OTHERS THEN
RAISE;
END;



CREATE OR REPLACE FUNCTION f_get_value (i_num IN NUMBER)
RETURN NUMBER AS
PRAGMA AUTONOMOUS_TRANSACTION;
l_value NUMBER;
BEGIN
BEGIN
SELECT Bc3
INTO l_value
FROM A1
WHERE Ac1= i_num ;
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
END;
RETURN l_value;
END;

Tue Jul 07, 12:49:00 AM EDT  

Blogger Thomas Kyte said....

@last comment

you scare the #$@$#@$# out of me.

You do not really understand what you are doing here at all.

You don't give a schema
I don't know what your keys are

All I know is your function which reads the table upon which the trigger fires cannot see the row you inserted.
I have no clue what you are really trying to do - it is beyond comprehension.

Let me just say this:

if you are using an autonomous transaction in a trigger, and this autonomous transaction IS NOT SIMPLY TO LOG AN ERROR, you have a massive, huge, large, grievous error in your logic - it is wrong, it is incorrect, it must be erased.


How about this, without giving us code, you explain IN THE FORM OF A CLEAR DETAILED SPECIFICATION what you think you need to do. Do not simply state "i need to log something", be so precise, so detailed that a programmer could write code from your specification.

explain to us please why this second table even *exists* - what is it's purpose?

Also, stop doing this:

EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

that is just beyond *not smart*. It provides NOTHING useful, only makes you look bad and makes it harder to debug your code.

this:

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(SQLCODE,
'**'||SQLERRM);
END;

is even worse, STOP IT, please - for the love of whatever, stop it. I wish I could figure out where this bad habit comes from so I could address the source, that is among the worst of programming practices *ever*.

Tue Jul 07, 01:03:00 AM EDT  

Anonymous Anonymous said....

I have a table company_hierarchy which stores parent, child relation between companies.
When ever a new company record (with columns company_id,parent_company_id,category_id,location etc)inserts into this table, i have to do
select * from company_hierarchy where company_id = :new.parent_company_id.
And then based on the category_id of this selected parent_company_id, i have to get all users from category_users and inserts same users for this parent categoory also.

The select stament
'select * from company_hierarchy where company_id = :new.parent_company_id'
is not selecting the same record which is newly inserted, but still it is giving mutating error.
So i have moved this select to a autonomous function. you have mentioned autonomous transactions can not see row that is newly inserted, but i need data from another row based on newly inserted value.

In this case, is it fine to go with this? please advice.
Thanks in advance.

Wed Jul 08, 03:03:00 PM EDT  

Blogger Thomas Kyte said....

@last comment

please read, re-read, and then read again the original article here.

do NOT use an autonomous transaction - it should be painfully clear from the article that an autonomous transaction is completely wrong for you.


I would suggest you just stop using triggers altogether.



read this for why:

http://www.oracle.com/technology/oramag/oracle/08-sep/o58asktom.html


triggers are evil - and you are using them wrong.

Wed Jul 08, 03:09:00 PM EDT  

Blogger Nishadha Silva said....

Hi Tom,
I have a question about using Autonomouse tranasactions that is kind of unique. Problem is our company is trying to standardise the code to support XA transactions , so partial commits are not supposed to be in the business logic but they can exist in autonomous transactions. so moving code inside autonomous transaction seems to be a easy way to go about it. But everywhere I read the recommendation is not to use autonomous transactions unless its absoloutly necessary. So its a bit of a dilemma
I have the a code similar to below

savepoint load1;
LOOP

IF create_ok()
update state
END IF
END LOOP

IF (some error)
ROLLBACK to load1;
ELSE

END IF

procedure create_ok
begin
savepoint s1;
exception
rollback to s1;
end

I was thinking of making create_ok an autonomous transaction so it will be ok with xa transactions ? whats is your opinion about that solution ? Thanks in advance

Fri Oct 30, 10:51:00 AM EDT  

Blogger Thomas Kyte said....

@ Nishadha Silva


why would you rollback at all - just return the error to the client and we silently, transparently and completely rollback the STATEMENT (we are ACID - statement level ATOMICITY is a key feature)

just remove your when others, let the error go back to the client and when a procedure fails - it will be as if the statement (procedure) NEVER HAPPENED.


If you are using XA, the use of an autonomous transaction would really screw everything up - you would break ALL of the clients transactional control.

do not even consider autonomous transactions, period

Fri Oct 30, 10:57:00 AM EDT  

Anonymous Anonymous said....

Hi Tom,
in XA if I have a situation to use Autonomous transaction or rollback to savepoint, how it should be handled?

Wed Nov 18, 12:48:00 AM EST  

POST A COMMENT

<< Home