Odp: Triggers on a table

> o I don't understand why select emp_seq.nextval from dual
> in a trigger have to do with the EMP table being modified; I missed
> something their.

The trigger is fired when the table EMP is modified (a record is being inserted which means that Orcl does sth like this:

          insert into EMP (id,emp_name,emp_status,dept_no) values (emp_seq.NEXTVALUE,'scott',3,1);
After this event - WITHOUT COMMIT - the trigger tries to do (= I try to do it) another insert:

          insert into EMP (id,emp_name,emp_status,dept_no) values (emp_seq.NEXTVALUE,'scott',1,2);
But Oracle says: 'ERROR, ' because it can't call 'emp_seq.nextval' twice in one transaction (there MUST be commit in between, but I can't commit in the trigger).

I've tried to call a procedure from the trigger with a 'commit' in the body, but Orcl still says it's wrong - 'you can't commit in the trigger'.

What can I do to make it work?
Please Help.
AW

> In article <jDSS4.56073$O4.1165144_at_news.tpnet.pl>,
> "AW" <wysza_at_polbox.com> wrote:
> > Oracle v 7.3
> > SO: Unix
> > Table Name: EMP
> >
> > fields/columns:
> > id
> > emp_name
> > emp_status
> > dept_no
> >
> > Situation:
> > I want to fire a trigger on this table which would act like this:
> > if inserting new employee with (emp_status=3) and (dept_no=1) then
> > insert new employee with (emp_status=1) and (dept_no=2).
> > It makes two inserts into the same table.
> >
> > Problem:
> > The problem is that I can't use a sequence to get the next id value
> > (emp_seq.nextval) because the table is modified.
> > What should I do?
> >
> > Thanks for advise
> > AW
> >
> >
>
> One possible solution is:
> o have a view sitting on top of EMP table
> o have a trigger on the view (use instead of insert on EMP_view)
> o carry out your inserts in the trigger
> o I don't understand why select emp_seq.nextval from dual
> in a trigger have to do with the EMP table being modified; I missed
> something their.
> Cheers
> JC
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed May 17 2000 - 00:00:00 CDT