Hi,

We are using Oracle 8.1.6 on Win 2000. I am inserting records in TblTest table with Insert INTO ...SELECT .. FROM query and generating TestId with Trigger. I can't use Sequences. I have also taken care to avoid mutating error. When I insert single record into Tbltest table. It works properly,but when try to insert Insert INTO ...SELECT .. FROM, Get error ORA-00001: unique constraint (SYSADM.XAK1I1DDTABLE) violated. Is there any other way to find out MAX(testid) from Test table before inserting new record into it?

How can I avoid this? please help.

Thanks & Regards,

Shailesh
---------------------------------------
CREATE OR REPLACE PACKAGE PkgTest As
TYPE REC IS RECORD(
nTestId TblTest.TestId%TYPE);
TYPE Tab_Rec IS TABLE OF REC INDEX BY BINARY_INTEGER;
Arr_Rec Tab_Rec;
Null_Rec Tab_Rec;
nCounter number:=0;
END PkgTest;
/
/***********************************************************************/

CREATE OR REPLACe PROCEDURE ProIns (nTestId IN NUMBER) AS
nID TblTest.TestId%TYPE;
BEGIN
SELECT NVL(MAX(TestId) + 1,1) INTO nID FROM TblTest;
UPDATE TblTest SET TestId = nID WHERE TestId = nTestId;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
/
/***********************************************************************/

CREATE OR REPLACE TRIGGER TRGROW_Ins1
BEFORE INSERT
ON TblTest
FOR EACH ROW
Begin
:NEW.TestId:=0;
PkgTest.nCounter:= PkgTest.nCounter + 1;
PkgTest.arr_rec(PkgTest.nCounter).nTestId:=:NEW.TestId;

End;
/
/***********************************************************************/

CREATE OR REPLACE TRIGGER TRGSTMT_Ins1
AFTER INSERT ON TblTest

BEGIN
For I in 1..PkgTest.nCounter LOOP
ProIns(PkgTest.arr_Rec(I).nTestId);
END LOOP;
PkgTest.nCounter := 0;
PkgTest.arr_Rec:=PkgTest.Null_Rec;

End;
/