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_CurrencyUS USD YUS USN NUS 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_Refset 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_CurrencyUS USD NUS USN NUS USS N
and try to update the data with the below condition
Update Country_Currency_Refset 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.