ValiidationTriggerSolution

I have a before insert trigger that fires for each row on a detail table

invoice_detail
--------------
invoice_no
item_no
product_no
etc...

(100, 1, AB123)
(100, 2, CD222)
(100, 3, DD111)

(200, 1, DD222)
(200, 2, D444)
(200, 3, D777)

The trigger checks if the new invoice number entered existed in the detail table and raise an error if it does.

The problem is that after the first row in the current transaction gets inserted the trigger for second row think the invoice number exists and raises the exception.

Is there a way to make it only fire once in the begining or end of transaction. I cant change the application code for this.
It has to something in he database.

I was thinking of statement level trigger but that would per statement and every insert for detail iten will be considered a statement.

I am wondering if i should create a new column for that sessionid and then do the count excluding the session id like

:new.session = session_id_for_transaction;

select count(*) from invoice_detail wheree....and sessionid <> 'sessionid_for_transaction'

Another option is  to create a small master table for all existing invoice numbers and then check against that instead of detail table.

I am not sure if this will work.

ANy ideas?
sam15Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Where does the commit happen in the 'current transaction'?

In a single transaction there should only be a single commit.  If this is happening in a trigger then it is likely AUTONOUMOUS.


>>:new.session = session_id_for_transaction;

That might work but seems to be a patch fix.  The SID and/or SERIAL# can be retrieved through SYS_CONTEXT.
0
sam15Author Commented:
it happens in the lcient application that inserts the data.

there must be a better solution that the session id one. not sure if it works. i have to create a new column in the table and assign the :new.session id for each insert so when the 2nd insert firest he trigger and it does the count it will exclude the first row just inserted.

Is not there a way to count only committed records in oracle and not the ones isnerted without commit.
0
sam15Author Commented:
the session id actually wont work for multiple user situation when many people execute transactions concurrently.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MikeOM_DBACommented:
The trigger checks if the new invoice number entered existed in the detail table and raise an error if it does.

In order to avoid this situation, you need to create a referential constraint. -- DO NOT try to fix architecture errors using triggers.
:p
0
sam15Author Commented:
I do not think you can do a constraint for this.

It is a detail table (no master table) and we want to make sure different clients do not enter same invoice number.
0
MikeOM_DBACommented:

Still, you can create "UNIQUE" constraint.
0
sam15Author Commented:
can you show me how?

I have several clients and i want unique invoice number per client transaction.

so if client A entered this it is accepted

(100, 1, AB123)
(100, 2, CD222)
(100, 3, DD111)

when client B tries to use 100 it rejects it.

I do not think you can do it with current database design.
0
slightwv (䄆 Netminder) Commented:
I agree that I do not think a constraint will work unless you can get the session id (or some other client specific id, maybe ip address?) as part of the unique key.

This will be allowed with a unique constraint but will fail the requirement.
clientA: insert 100,1
clientB: insert 100,2

>>Is not there a way to count only committed records in oracle and not the ones isnerted without commit.

I'm not following where you are going with this.  Only the session that inserts data can 'count' it before commit.  Until it is committed no other session can see the data.




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikeOM_DBACommented:

How come you have an invoice_detail table but not and invoice (master) table?

With an invoice master table you could have a FK constraint.
0
slightwv (䄆 Netminder) Commented:
>>With an invoice master table you could have a FK constraint.
 
I still don't think this will work without some changes somewhere.

The requirement as I understand it is that if 'userA' starts a 'new' id, say 100, then NO OTHER user can insert a 100.  Only userA can use 100 in the detail records.

There has to be some way to identify that 100 goes with 'userA'.  Once you get that then a PK/FK can likely be used.
0
MikeOM_DBACommented:

I still don't think this will work without some changes somewhere.

Correct...
We may be missing something.

Is there an invoice master table? If not why?

Normally when you have some "detail" table, it implies the is a "master" table and therefore the uniqueness of the key is preserved in the master by a PK constraint.
Existence of this key can be automatically validated in the detail tables by creating a FK constraint.

If for some weird reason there is no invoice master table, then you will need to create one.







0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.