Home » SQL & PL/SQL » SQL & PL/SQL » does not insert after resolving mutating problem (Release 9.2.0.1.0 ,xp)
does not insert after resolving mutating problem [message #348428] Tue, 16 September 2008 13:51 Go to next message
ckb_ORACLE
Messages: 5
Registered: August 2008
Junior Member
Hi,
I want to give commission to only those people who are salesman.
The commission is calculated in a procedure named calccumm that I have called in the deltry1 trigger.

I wrote the following code.

create or replace paCKage z_tri as
type trig is table of employee%rowtype index by binary_integer;
  oldval trig;
  newval trig;
  
end;

create or replace trigger z_trig11 before INSERT on employee
begin
	z_tri.oldval := z_tri.newval;
	
end;

create or replace trigger deltry1 before insert on employee  for each row
declare
i number default z_tri.oldval.count+1;
x number(5);
begin
	
	z_tri.oldval(i).empno := :new.empno;
	z_tri.oldval(i).ename := :new.ename;
	z_tri.oldval(i).job := :new.job;
	z_tri.oldval(i).mgr := :new.mgr;
	z_tri.oldval(i).hiredate := :new.hiredate;
	z_tri.oldval(i).sal := :new.sal;
	x := calccumm(:new.sal,:new.job);
	z_tri.oldval(i).comm := x;
	z_tri.oldval(i).deptno := :new.deptno;

end;

create or replace trigger deltry_final2 after insert on employee 
declare
x number(4);
begin
	for i in 1..z_tri.oldval.count loop
insert into employee (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,comm,DEPTNO)
values
(z_tri.oldval(i).empno,z_tri.oldval(i).ename,z_tri.oldval(i).job,z_tri.oldval(i).mgr,z_tri.oldval(i).hiredate,
z_tri.oldval(i).sal,z_tri.oldval(i).comm,z_tri.oldval(i).deptno);

end loop;
end;

Now if i write
insert into empLoyee(empno,ename,job,mgr,sal,deptno) values
(22,'sss','SALESMAN',7788,100,20)


IT GIVES ME
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_EMPLPYEE) violated
ORA-06512: at "SCOTT.DELTRY_FINAL2", line 14
ORA-04088: error during execution of trigger 'SCOTT.DELTRY_FINAL2'

Can anybody please help me on this.

Thankyou.

Re: does not insert after resolving mutating problem [message #348430 is a reply to message #348428] Tue, 16 September 2008 13:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>ORA-00001: unique constraint (SCOTT.PK_EMPLPYEE) violated
This error results from attempting to INSERT a duplicate Primary Key (PK) value.
What is supposed to happen when the PK already exists?
Re: does not insert after resolving mutating problem [message #348434 is a reply to message #348428] Tue, 16 September 2008 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00001: unique constraint (%s.%s) violated
 *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
         For Trusted Oracle configured in DBMS MAC mode, you may see
         this message if a duplicate entry exists at a different level.
 *Action: Either remove the unique restriction or do not insert the key.

Regards
Michel
Re: does not insert after resolving mutating problem [message #348476 is a reply to message #348428] Tue, 16 September 2008 21:07 Go to previous messageGo to next message
satm2008
Messages: 10
Registered: September 2008
Location: Toronto, Canada
Junior Member
First of all, your INSERT-trigger "deltry_final2" is wrong as it attempts to insert a row for each insert you attempt to in the employee table. Meaning, same table two inserts of same data, one by you and other by trigger.
So it definitely is erroneous, duplicate primary key.

Just remove the INSERT in the trigger and let it go. I suggest you to remove the trigger itself.

Good luck Smile
Re: does not insert after resolving mutating problem [message #348491 is a reply to message #348476] Tue, 16 September 2008 23:41 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The after-insert-statement trigger should UPDATE the rows you just inserted; the insert-row trigger-code is not executed instead of an insert, it is executed before the rows are inserted.
Now if you insert again in your after statement trigger, all rows are duplicated.
So, change that insert to an update of your comm column.


Hm, on second read:
You don't need this structure here; what you use here is the framework to circumvent the mutating table problem, which occurs if you try to read OTHER rows while inserting/updating a row.
Your problem can easily be handled with a single before-row trigger. (That is, if calccum does not select any rows from the emp table)

[Updated on: Tue, 16 September 2008 23:47]

Report message to a moderator

Previous Topic: ALTER all the tables from a particular schema at one shot
Next Topic: doubt about returning
Goto Forum:
  


Current Time: Sun Dec 11 00:40:27 CST 2016

Total time taken to generate the page: 0.15639 seconds