Home » SQL & PL/SQL » SQL & PL/SQL » INSERT A RECORD (oracle 10g)
INSERT A RECORD [message #426278] Wed, 14 October 2009 12:15 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi friends needyour help again
I hava table dept_temp
create table  dept_temp(order_no number  ,dept varchar2(20),primary key(order_no));

insert into dept_temp values(1,'SALES');
insert into dept_temp values(2,'HR');
insert into dept_temp values(3,'PRODUCTION');


i want to insert a record
insert into dept_temp values(3,'FINANCE');


since there is a primary key on order_no column , it wont allow to insert the record.
My requirement is
while inserting the record ,if the order_no is there just update the order_no to next no (order_no +1 ).

I can make it through procedure but the buisness ppl dont want anew procedure. is it possible through trigger ?
or any other alternatives

Re: INSERT A RECORD [message #426279 is a reply to message #426278] Wed, 14 October 2009 12:25 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
This is why most of us use sequences.
You can do what you want from a trigger so long as you never try to use insert/select (you'll get a mutating table error otherwise).
But doing that or using a procedure still leaves you with the problem that if two users try to insert into this table at the same time they'll still get a primary key violation.
Plus this approach doesn't scale very well at all.

You really, really should use a sequence for this.
Re: INSERT A RECORD [message #426281 is a reply to message #426278] Wed, 14 October 2009 12:46 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
The table has these many rows

SQL> select * from dept_temp;

  ORDER_NO DEPT
---------- --------------------
         1 SALES
         2 HR
         3 PRODUCTION

while writing the following insert statment how sequence will
update order_no of dept to 4
insert into dept_temp values(3,'FINANCE');


my required output is
SQL> select * from dept_temp;

  ORDER_NO DEPT
---------- --------------------
         1 SALES
         2 HR
         3 FINANCE
         4 PRODUCTION

[Updated on: Wed, 14 October 2009 12:48]

Report message to a moderator

Re: INSERT A RECORD [message #426282 is a reply to message #426281] Wed, 14 October 2009 12:52 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
I think cookiemonster's answer was perfect. What did asking the question a second time add to the original question?
Re: INSERT A RECORD [message #426285 is a reply to message #426281] Wed, 14 October 2009 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is the rationale to change the identifier of the previous row to a new value?
A primary key value NEVER changes.

Regards
Michel

[Updated on: Wed, 14 October 2009 13:22]

Report message to a moderator

Re: INSERT A RECORD [message #426286 is a reply to message #426285] Wed, 14 October 2009 13:21 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
YES,but my reqrment is to change it .
is there any way to make it happen
Re: INSERT A RECORD [message #426287 is a reply to message #426286] Wed, 14 October 2009 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will NEVER work unless you lock the whole table each time you want to insert something.

Regards
Michel
Re: INSERT A RECORD [message #426288 is a reply to message #426287] Wed, 14 October 2009 13:25 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
could you pls explain it in more detail. if you can give example ,that ll be a great help.
Re: INSERT A RECORD [message #426289 is a reply to message #426288] Wed, 14 October 2009 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just think about it: what will happen if 2 sessions insert at the same time a row that has the same identifier than a previous one? Or if 2 sessions insert a row with the same identifier? Or if one session rolls back? And so on.

Regards
Michel

[Updated on: Wed, 14 October 2009 13:31]

Report message to a moderator

Re: INSERT A RECORD [message #426290 is a reply to message #426289] Wed, 14 October 2009 13:37 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
kindly explain how to overcome this issue .
is it possible to insert that row or not.
Re: INSERT A RECORD [message #426291 is a reply to message #426290] Wed, 14 October 2009 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cookiemonster gave you the solution to overcome the problem.

Regards
Michel
Re: INSERT A RECORD [message #426292 is a reply to message #426291] Wed, 14 October 2009 14:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well. What you want to do might be possible. Theoretically.

The same way it might be theoretically possible to change the wing of an aircraft while it is flying.

Theoretically possible with very much effort if there is some obscure reason to do it, but a seriously bad idea in 99.99999% of cases.

You might want to consider filling the primary key with a sequence, and then add an additional "order" column to the table, if you need the output ordered in some way somewhere.
Re: INSERT A RECORD [message #426318 is a reply to message #426278] Wed, 14 October 2009 23:25 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
The information you have been given in this thread is both correct and incorrect.

The short answer that you want is NO.

You cannot do that in a table based trigger. Since you are basing your detection of duplicate keys on your primary key constraint, you won't know that the row is bad until after the insert trigger has failed with the error telling you it is a duplicate key. At that point the trigger is done so you cannot do anything else with the row you are trying to insert.

If you are desperate, you may try to use a view and instead-of-trigger to do it. Since instead of triggers are not subject to the mutating/constraining table error issues, it is trivial to do what you want in an instead-of-trigger. You check the table (or in this case the view) to see if the key is there and if so get a different one and repeat the process. Unfortunately this is a misleading solution because of the concurrent users issue that people have been talking about. It is possible, indeed likely, that two people will try to insert two new rows and each would acquire the same as yet unused key. Each user process thinking it now has a good key, will attempt to insert with that new key. One will succeed and the other will hang until the first commits at which point the second will fail with the same duplicate key error.

So in the end, in a multi-user environment, even the instead-of-trigger won't fix the problem. It is a fix good only in a single user system.

You must ask you business people two questions:

1) what are they trying to achieve with this numbering scheme and why? You need to understand the underlying business requirements so you can craft a workable solution.

2) what are they doing tonight? Since they seem so interested is writing code (???OH NO... you can't write a procedure to do it???), they can stop by your desk and use your terminal to write the code themselves. If they think they can do your job then you are happy to provide them with access to a compiler and the database. They should just remember to wake you from your nap when they are done writing the code so you can give them access to some data so they can test it.

If these are business people be polite but tell them to stick to the business and you will decide if a new procedure is needed or not, not them. They are the masters, we are the hound dogs. They decide what they want, but we go fetch it. There is a clear division of labor and they need to respect it, so no more dictating the code solution or solution design.

It sounds like you might be trying to create a gapless sequence in which case you can forget it. Gapless sequences are and always have been a fantasy. Just think of what happens when you delete a row. Do you have to renumber everthing after the row you deleted? If not, then why is it so important that the next number you use be last_number+1?

if you are generating something like a LINE NUMBER for a line item on an invoice, then you can use the instead of trigger solution as long as the first thing you do in your trigger is lock the invoice row that is the parent of the line items you are generating. This is a reasonable solution and is 100% safe as long as everyone updates via the view with its instead-of-trigger. You will have to include the same invoice master row lock in update and delete triggers as well.

Good luck, Kevin

[Updated on: Wed, 14 October 2009 23:44]

Report message to a moderator

Re: INSERT A RECORD [message #426386 is a reply to message #426318] Thu, 15 October 2009 03:52 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
Kevin Meade wrote on Thu, 15 October 2009 05:25

You cannot do that in a table based trigger. Since you are basing your detection of duplicate keys on your primary key constraint, you won't know that the row is bad until after the insert trigger has failed with the error telling you it is a duplicate key. At that point the trigger is done so you cannot do anything else with the row you are trying to insert.

If you are desperate, you may try to use a view and instead-of-trigger to do it. Since instead of triggers are not subject to the mutating/constraining table error issues, it is trivial to do what you want in an instead-of-trigger. You check the table (or in this case the view) to see if the key is there and if so get a different one and repeat the process. Unfortunately this is a misleading solution because of the concurrent users issue that people have been talking about. It is possible, indeed likely, that two people will try to insert two new rows and each would acquire the same as yet unused key. Each user process thinking it now has a good key, will attempt to insert with that new key. One will succeed and the other will hang until the first commits at which point the second will fail with the same duplicate key error.



Actually you can do that with a before insert row trigger, so long as you only ever do insert values and not insert select. Oracle bypasses mutating table constraints in that case as it knows only one row is being inserted. Still has the same multi-user issues as the instead of trigger of course.
Re: INSERT A RECORD [message #426432 is a reply to message #426318] Thu, 15 October 2009 07:56 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Kevin Meade wrote on Thu, 15 October 2009 00:25

2) what are they doing tonight? Since they seem so interested is writing code (???OH NO... you can't write a procedure to do it???), they can stop by your desk and use your terminal to write the code themselves. If they think they can do your job then you are happy to provide them with access to a compiler and the database. They should just remember to wake you from your nap when they are done writing the code so you can give them access to some data so they can test it.

If these are business people be polite but tell them to stick to the business and you will decide if a new procedure is needed or not, not them. They are the masters, we are the hound dogs. They decide what they want, but we go fetch it. There is a clear division of labor and they need to respect it, so no more dictating the code solution or solution design.


Oh Kevin, you say it so eloquently. Good work!
I might add that you may also want to suggest to them how they can do their job as they are so apt to tell you how to do your job. This one is even more difficult to achieve than what Kevin says in the second paragraph I quoted.
Re: INSERT A RECORD [message #426439 is a reply to message #426278] Thu, 15 October 2009 08:35 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks Joy, hehe.

Thanks CookieMonster for the clarification. You are correct but I should hope that on one would ever write a trigger that relys on all code going forward to never issue a

INSERT INTO TABLEX SELECT FROM ...

A trigger written to work around the the quirks of mutating/constraining tables is a trigger written to break someday. And not only would the trigger break, but there would be no easy solution for it. Either the developer writing the new code would have to change their application entirely, or the trigger solution would have to be scrapped and replaced with something else. No good solution either way.

But your inclusion of the details is a good one. It provides us with additional insight into the underlying workings of the trigger mechanism.

Kevin

[Updated on: Thu, 15 October 2009 08:36]

Report message to a moderator

Re: INSERT A RECORD [message #426445 is a reply to message #426439] Thu, 15 October 2009 08:43 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
Kevin Meade wrote on Thu, 15 October 2009 14:35
Thanks Joy, hehe.

Thanks CookieMonster for the clarification. You are correct but I should hope that on one would ever write a trigger that relys on all code going forward to never issue a

INSERT INTO TABLEX SELECT FROM ...

A trigger written to work around the the quirks of mutating/constraining tables is a trigger written to break someday. And not only would the trigger break, but there would be no easy solution for it. Either the developer writing the new code would have to change their application entirely, or the trigger solution would have to be scrapped and replaced with something else. No good solution either way.

But your inclusion of the details is a good one. It provides us with additional insight into the underlying workings of the trigger mechanism.

Kevin


Indeed - which is why I wish oracle hadn't actually included that feature, and while you'd like to hope no one would ever do that, somebody was always going to.
Indeed I've inherited a couple of triggers that do do that. I wasn't amused when I found out.
Re: INSERT A RECORD [message #426450 is a reply to message #426278] Thu, 15 October 2009 08:49 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
ha, how unfortunate for you..

Its all about learning curve I guess.

Triggers in general, and instead-of-triggers in particular are to me a necessary feature set if we are to take our databases to the next level. Without them, there is little chance of truely encapsulating logic inside the database.

Of course as you have pointed out, triggers are a feature ripe for abuse and oracle was never very good at pointing out the pitfals of same. We had to wait several years after their introduction for people like Tom Kyte to start telling us of how the can be used incorrectly, and that was only after he was bitten by the same bug. He wrote some code with the same mistakes we made in our early days and that is how he found out. So even the oracle internal developer staff was poorly informed about how triggers could be misused. At least that is what I recall reading.

But let me reiterate, I LOVE INSTEAD-OF-TRIGGERS. To me these are one of the TOP 10 best features ever delivered by Oracle.

Kevin

[Updated on: Thu, 15 October 2009 08:50]

Report message to a moderator

Previous Topic: ORA-06502
Next Topic: select then union with dummy column (merged 2)
Goto Forum:
  


Current Time: Wed Dec 07 06:56:12 CST 2016

Total time taken to generate the page: 0.14473 seconds