| PL/SQL question - HELP!!! [message #78781] |
Mon, 18 March 2002 01:41  |
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  |
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 |
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 |
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
