| problem with sequence generation [message #227166] |
Tue, 27 March 2007 08:13  |
karthi_smarty04
Messages: 19
Registered: January 2007
Location: COIMBATORE
|
Junior Member |
|
|
hi experts,
iam currently developing an application system using oracle forms9i.
table name: emp
table desc: emp_id number,emp_name varchar2(20),emp_no number
i have to generate the emp_id automatically.i have created a sequence for that with
create sequence emp_seq
start with 100
increment by 1
The code which i have written in Pre_insert trigger is
begin
select emp_seq.nextval into :emp.emp_id from dual
end
the sequence number is getting incremented even if the record is not inserted(record not inserted due to frm 50016 error).please help me out..
i want the sequence number to be generated only when the record id inserted.also i should display the emp_id to the user as soon as it is generated
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: problem with sequence generation [message #227332 is a reply to message #227282] |
Wed, 28 March 2007 01:06  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| djmartin wrote on Wed, 28 March 2007 03:09 | Then in the 'insert' trigger at the database level use the 'nvl(max(emp_id),0)+1' method to give you the 'holeless sequence'.
|
Make sure you lock your entire table for this, to prevent multiple users from working in parallel. Better yet, send all but one of your users home. Make sure that single user never deletes a row.
That is about the only way to make this work.
In the end, you WILL end up with duplicates..
|
|
|
|
|
|
|
|
|
|
| Re: problem with sequence generation [message #227685 is a reply to message #227638] |
Thu, 29 March 2007 01:05 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
"While the triggers are running"
This is/could be just a very small fraction of the time between the insert and the commit (the transaction can involve more than just this one table).
In addition, you encounter the Mutating Trigger problem, another very clear indication that there is something wrong with the design.
|
|
|
|
| Re: problem with sequence generation [message #227855 is a reply to message #227332] |
Thu, 29 March 2007 18:43 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
| Frank wrote on Wed, 28 March 2007 16:06 | | djmartin wrote on Wed, 28 March 2007 03:09 | Then in the 'insert' trigger at the database level use the 'nvl(max(emp_id),0)+1' method to give you the 'holeless sequence'.
|
Make sure you lock your entire table for this, to prevent multiple users from working in parallel. Better yet, send all but one of your users home. Make sure that single user never deletes a row.
That is about the only way to make this work.
In the end, you WILL end up with duplicates..
|
When I said "the 'insert' trigger at the database level" I meant the CREATE OR REPLACE TRIGGER xxxx_TRG
BEFORE DELETE OR INSERT OR UPDATE
ON xxxx
Yes you can get a mutating table message if you try to do an update of the table that you are already updating. However, for Oracle to maintain its primary key functionality the database must know that it has sole modification access to the table in question. Note that you cannot have a 'commit' in a database trigger because you are already IN a 'commit'.
@others: If I have got this wrong please post a link to the doco.
David
|
|
|
|
| Re: problem with sequence generation [message #227880 is a reply to message #227855] |
Fri, 30 March 2007 00:07 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
The insert trigger at the database level has NOTHING to do with a database commit. That is exactly my point. There can be hours between the firing of a trigger, be it row level or statement level, and the actual commit:
SQL> create table faq
2 ( col1 number
3 , col2 varchar2(10)
4 );
Table created.
SQL> create or replace trigger faq_bis
2 before insert on faq
3 begin
4 dbms_output.put_line('Trigger is firing!');
5 end;
6 /
Trigger created.
SQL> insert into faq values (1, 'One');
Trigger is firing!
1 row created.
See? No commit, and yet the trigger fired.
Furthermore, the proposed functionality can only be achieved if there is a rowlevel trigger involved. Either to save rowids of newly inserted rows (the standard way to circumvent the mutating table problem) or to adjust the 'sequence'column of each individual row.
The mutating table error is not caused by an update on an already updating table, but by a select on it. Therefore, your select max will cause it.
[Updated on: Fri, 30 March 2007 00:24] Report message to a moderator |
|
|
|
|
|
| Re: problem with sequence generation [message #227907 is a reply to message #227899] |
Fri, 30 March 2007 02:33 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| Quote: | If the programmer keeps their code 'simple' (no DDL) and uses Form builtins the way they were designed (no rewrites in the on-insert, etc), I believe that there will be no problems.
|
I agree.
Actually this whole thing started out because I wanted to point out that the requirement (not your solution as such) would have a negative result on the scalability of the application.
I think we agreed before that there are way too many people that go through the most impossible loop-holes to achieve nonsense requirements.
Therefor, I fear that more and more we will see that your premisse, as quoted here, will not be met... 
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
