Home » Developer & Programmer » Forms » PL/SQL question - HELP!!!
PL/SQL question - HELP!!! [message #78781] Mon, 18 March 2002 01:41 Go to next message
Gav Craddock
Messages: 3
Registered: March 2002
Junior Member
Hi, I'm really stuck on my university final year project at the moment - if anyone can help, I'll give you a mention in the finished report!!!

I'm trying to construct an Oracle trigger that will essentially read in a set
of data (about 5 or 6 records) and insert some of the data into a new table.
However, I get a "table is mutating" error, presumably because the SELECT
statement returns multiple tuples and there's only one INSERT statement.

I know what I want to do - put the returned tuples into an array, and then loop
through the array inserting one set of data at a time. My question is, how do I
read in multiple records and insert them into an array? Or, alternatively, how
can I read in record 1/6, then read in record 2/6, etc...

Any help would be massively appreciated!

Gavin Craddock

The code I'm using at the moment is below :

create or replace trigger register_student
after insert on student
for each row
when (new.status='FT')
declare
core_module varchar2(15);

begin
SELECT MODULE.MODULE_ID INTO CORE_MODULE
FROM STUDENT, AWARD, AWARD_CORE, MODULE
WHERE STUDENT.AWARD_ID = AWARD.AWARD_ID
AND STUDENT.AWARD_ID = AWARD_CORE.AWARD_ID
AND AWARD_CORE.MODULE_ID = MODULE.MODULE_ID
AND AWARD_CORE.MOD_TYPE='C'
AND STUDENT.STUDENT_ID=:new.student_id;

INSERT INTO REGISTRATION
VALUES(seq_registration.nextval, core_module, :new.student_id, 'P');
end;
/
Re: PL/SQL question - HELP!!! [message #78782 is a reply to message #78781] Mon, 18 March 2002 01:56 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
Hi,

u have to create a package and two trigger instead of one

have a look at the code below, i have not compiled the code,
if the is some problem then change it.if u have still problem then
feel free to discuss

1.
create or replace package student_pack as
type stud_array is array of student.student_id%type index by binary_integer;
v_stud_array stud_array;
cnt number:=0;
end;

2.
create or replace trigger register_student
after insert on student
for each row
when (new.status='FT')
declare
student_pack.v_stud_array(student_pack.cnt+1):=:new.student_id;
student_pack.cnt:=student_pack.cnt+1;
begin
end;
/

3.
create or replace trigger register_student_1
after insert on student
declare
core_module varchar2(15);
begin
for i in 1..student_pack.v_stud_array.count
loop
SELECT MODULE.MODULE_ID INTO CORE_MODULE
FROM STUDENT, AWARD, AWARD_CORE, MODULE
WHERE STUDENT.AWARD_ID = AWARD.AWARD_ID
AND STUDENT.AWARD_ID = AWARD_CORE.AWARD_ID
AND AWARD_CORE.MODULE_ID = MODULE.MODULE_ID
AND AWARD_CORE.MOD_TYPE='C'
AND STUDENT.STUDENT_ID=student_pack.v_stud_array(i);
--
INSERT INTO REGISTRATION
VALUES(seq_registration.nextval, core_module, :new.student_id, 'P');
end loop;
student_pack.cnt:=0;
begin
end;
/
Re: PL/SQL question - HELP!!! [message #78784 is a reply to message #78781] Mon, 18 March 2002 03:30 Go to previous messageGo to next message
Gav Craddock
Messages: 3
Registered: March 2002
Junior Member
Thanks for that, it's given me some good ideas as to how to get it working!!

Just one thing - the SELECT statement brings back more than one record, how do I put each individual record into the array??

Thanks

Gavin
Re: PL/SQL question - HELP!!! [message #78786 is a reply to message #78784] Mon, 18 March 2002 03:45 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
Replace
--------

loop
SELECT MODULE.MODULE_ID INTO CORE_MODULE
FROM STUDENT, AWARD, AWARD_CORE, MODULE
WHERE STUDENT.AWARD_ID = AWARD.AWARD_ID
AND STUDENT.AWARD_ID = AWARD_CORE.AWARD_ID
AND AWARD_CORE.MODULE_ID = MODULE.MODULE_ID
AND AWARD_CORE.MOD_TYPE='C'
AND STUDENT.STUDENT_ID=student_pack.v_stud_array(i);
--
INSERT INTO REGISTRATION
VALUES(seq_registration.nextval, core_module, :new.student_id, 'P');
end loop;

with
----

loop
--
INSERT INTO REGISTRATION
select seq_registration.nextval,MODULE.MODULE_ID
, :new.student_id, 'P'
FROM STUDENT, AWARD, AWARD_CORE, MODULE
WHERE STUDENT.AWARD_ID = AWARD.AWARD_ID
AND STUDENT.AWARD_ID = AWARD_CORE.AWARD_ID
AND AWARD_CORE.MODULE_ID = MODULE.MODULE_ID
AND AWARD_CORE.MOD_TYPE='C'
AND STUDENT.STUDENT_ID=student_pack.v_stud_array(i);
--
end loop;

it will insert one row for each row returned by the select statement

hope it helps!!!
Re: PL/SQL question - HELP!!! [message #78793 is a reply to message #78784] Mon, 18 March 2002 06:03 Go to previous messageGo to next message
Gav Craddock
Messages: 3
Registered: March 2002
Junior Member
I've got it working now mate, thanks a lot for all your help.

I had to change a few things, you can't use :new or :old in statement level triggers for example :-)

Cheers a million, I'll give you a credit in my dissertation. If you remember, go to www.cradd.co.uk in about 6 months and the report will be there.

Gav
Re: PL/SQL question - HELP!!! [message #79294 is a reply to message #78784] Wed, 22 May 2002 01:33 Go to previous message
suman
Messages: 8
Registered: January 2002
Junior Member
During the Database creation,ie; in SQL how can i insert MULTIPLE RECORDS at a time?
Previous Topic: printing reportes
Next Topic: Is This A FORMS BUG???
Goto Forum:
  


Current Time: Thu Jun 04 04:05:14 CDT 2020