-
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;
/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|