pl/sql [message #208094] |
Fri, 08 December 2006 02:41  |
seyiisq
Messages: 125 Registered: September 2005
|
Senior Member |
|
|
i have this trigger it is giving me a compilation error. can any one help
SQL> create or replace Trigger warning_trig
2 After insert or update of absent on attendance_warning
3 For each row
4 Declare
5 i_sum student_warning.absent_total%type;
6 i_student_id attendance_warning.student_id%type;
7 i_state states.states_name%type;
8 begin
9 select absent_total into i_sum FROM student_warning;
10 if
11 i_sum = 2
12 then
13 i_state := 1;
14 dbms_output.put_line ('2 LECTURES MISSED STUDENT SHOULD BE WARNED IMMEDIATELY');
15 elsif
16 i_sum = 4
17 then
18 i_state := 2;
19 dbms_output.put_line ('4 LECTURES MISSED STUDENT HAS BEEN WITHDRAWN');
20 insert into student_states values ( student_states_id_seq.nextval,i_state,i_student_id);
21 end if;
22 END warning_trig;
23
24 /
Trigger created.
SQL> insert into attendance_warning values (3,4,1,1);
insert into attendance_warning values (3,4,1,1)
*
ERROR at line 1:
ORA-04091: table SCOTT.ATTENDANCE_WARNING is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.WARNING_TRIG", line 6
ORA-04088: error during execution of trigger 'SCOTT.WARNING_TRIG'
i have this table attendandance_warning as shown above with attribute absent. on insert into absent i want the trigger to insert into student_state but it is giving a mutation error.
pls can any one help
|
|
|
Re: pl/sql [message #208095 is a reply to message #208094] |
Fri, 08 December 2006 02:51   |
ehegagoka
Messages: 493 Registered: July 2005
|
Senior Member |
|
|
hi!,
yep, i only know that it will error if you select on the table you are updating, but i think i didnt see any code selecting on attendance_warning.
|
|
|
Re: pl/sql [message #208097 is a reply to message #208094] |
Fri, 08 December 2006 02:54   |
CoolBuddy
Messages: 10 Registered: December 2006 Location: India
|
Junior Member |
|
|
Can u please explain why u r declaring the variable "i_student_id". I couldn't understand its usage in ur program.
|
|
|
Re: pl/sql [message #208099 is a reply to message #208094] |
Fri, 08 December 2006 03:04   |
seyiisq
Messages: 125 Registered: September 2005
|
Senior Member |
|
|
i am trying to insert the value of that attribute into table student_states
see line 20 of the program
the tables is as shown below
create table attendance_warning(
attendance_warning_id number(5),
student_id number(5),
lecturer_id number(5),
absent number(1),
constraint pk_attendance_warning_id primary key (attendance_warning_id),
constraint fk_aw_student_id foreign key (student_id) references student (student_id),
constraint fk_aw_lecturer_id foreign key (lecturer_id) references lecturer (lecturer_id));
create table states (
states_id number(1),
states_name varchar2(20),
constraint pk_states_id primary key (states_id));
create table student_states(
student_states_id number(5),
states_id number(5),
STUDENTS_id number(5),
constraints pk_student_states_id primary key (student_states_id),
constraints fk_s_stu_id foreign key (STUDENT_id) references (student_id),
constraints fk_s_states_id foreign key (stATES_ID) references (states_id));
[Updated on: Fri, 08 December 2006 03:08] Report message to a moderator
|
|
|
Re: pl/sql [message #208102 is a reply to message #208099] |
Fri, 08 December 2006 03:16   |
ehegagoka
Messages: 493 Registered: July 2005
|
Senior Member |
|
|
hi,
i think he's saying that you didnt assign any value to i_student_id. still cant figure out why youre getting that error.
|
|
|
Re: pl/sql [message #208105 is a reply to message #208099] |
Fri, 08 December 2006 03:35   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
We also need the script for the student_warning table, plus the script for Student_states is wrong - the FK constraints don't work.
Would there be a foreign key constraint from Student_states to Attendance_warning?
If there were, then an insert into student_states would require a select on Attendance_Warning, which leads to mutating table errors.
|
|
|
Re: pl/sql [message #208107 is a reply to message #208094] |
Fri, 08 December 2006 03:42   |
seyiisq
Messages: 125 Registered: September 2005
|
Senior Member |
|
|
create table student_states(
student_states_id number(5),
states_id number(5),
STUDENTS_id number(5),
constraints pk_student_states_id primary key (student_states_id),
constraints fk_s_stu_id foreign key (STUDENT_id) references (student_id),
constraints fk_s_states_id foreign key (stATES_ID) references (states_id));
create view student_warning as
select STUDENT_ID, sum(ABSENT) absent_total
from
attendance_warning
group by STUDENT_ID;
these are the 2 tables
[Updated on: Fri, 08 December 2006 03:44] Report message to a moderator
|
|
|
Re: pl/sql [message #208110 is a reply to message #208107] |
Fri, 08 December 2006 03:54   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
No, that isn't the correct script for the Student_States table (as I mentioned before)
A correct script would have FK constraints that referenced tables.
If you run your script, this is the result:
SQL> create table student_states(
2 student_states_id number(5),
3 states_id number(5),
4 STUDENTS_id number(5),
5 constraints pk_student_states_id primary key (student_states_id),
6 constraints fk_s_stu_id foreign key (STUDENT_id) references (student_id),
7 constraints fk_s_states_id foreign key (stATES_ID) references (states_id));
constraints fk_s_stu_id foreign key (STUDENT_id) references (student_id),
*
ERROR at line 6:
ORA-00903: invalid table name
|
|
|
Re: pl/sql [message #208177 is a reply to message #208099] |
Fri, 08 December 2006 07:55   |
CoolBuddy
Messages: 10 Registered: December 2006 Location: India
|
Junior Member |
|
|
I was just asking y u defined the variable there.
try using that :old.student_id instead of the variable directly while inserting the values.
|
|
|
Re: pl/sql [message #208254 is a reply to message #208094] |
Fri, 08 December 2006 18:48   |
seyiisq
Messages: 125 Registered: September 2005
|
Senior Member |
|
|
following your reply i tried the code as shown below
SQL> create or replace Trigger warning_trig
2 After insert or update of absent on attendance_warning
3 For each row
4 Declare
5 i_sum student_warning.absent_total%type;
6 i_state states.states_name%type;
7 begin
8 select absent_total into i_sum FROM student_warning;
9 if
10 i_sum = 2
11 then
12 i_state := 1;
13 dbms_output.put_line ('2 LECTURES MISSED STUDENT SHOULD BE WARNED IMMEDIATELY');
14 elsif
15 i_sum = 4
16 then
17 i_state := 2;
18 dbms_output.put_line ('4 LECTURES MISSED STUDENT HAS BEEN WITHDRAWN');
19 end if;
20 insert into student_states values (student_states_id_seq.nextval,i_state,:old.student_id);
21 END warning_trig;
22 /
Trigger created.
SQL> insert into attendance_warning values (3,4,1,1);
insert into attendance_warning values (3,4,1,1)
*
ERROR at line 1:
ORA-04091: table SCOTT.ATTENDANCE_WARNING is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.WARNING_TRIG", line 5
ORA-04088: error during execution of trigger 'SCOTT.WARNING_TRIG'
i am still having the same problem
|
|
|
Re: pl/sql [message #208258 is a reply to message #208254] |
Fri, 08 December 2006 20:17   |
seyiisq
Messages: 125 Registered: September 2005
|
Senior Member |
|
|
i think the problem i am having is the select statement from student_warning, kowing well that student_warning is a view from attendance_warning.
|
|
|
|
Re: pl/sql [message #208298 is a reply to message #208260] |
Sat, 09 December 2006 03:32   |
ab_trivedi
Messages: 460 Registered: August 2006 Location: Pune, India
|
Senior Member |
|
|
Hi seyisg,
I understand your problem but the why are not not followinf the instructions given by JRowBottom. Because what i can figure out is that your table designing is wrong. Anwway try it send me some fake data for table.
Bye
Ashu
|
|
|
Re: pl/sql [message #208305 is a reply to message #208298] |
Sat, 09 December 2006 04:58   |
seyiisq
Messages: 125 Registered: September 2005
|
Senior Member |
|
|
that has been corrected it was a wrong script that i posted the problem is not the table created but the trigger, why am i having the trigger mutating error thanks
|
|
|
Re: pl/sql [message #208308 is a reply to message #208305] |
Sat, 09 December 2006 05:10   |
ab_trivedi
Messages: 460 Registered: August 2006 Location: Pune, India
|
Senior Member |
|
|
hi,
Why you are facing the mutating table because JrowBottoms has already answered? Just see that .
Bye
|
|
|
Re: pl/sql [message #208360 is a reply to message #208308] |
Sat, 09 December 2006 18:02   |
seyiisq
Messages: 125 Registered: September 2005
|
Senior Member |
|
|
could it be that the problem is from the select statement from student_warning in the trigger script because i made student_warning a view from attendance_warning
|
|
|
Re: pl/sql [message #208361 is a reply to message #208360] |
Sat, 09 December 2006 18:53   |
seyiisq
Messages: 125 Registered: September 2005
|
Senior Member |
|
|
i have been working on it but still having same problem pls can u help
1 create or replace Trigger warning_trig
2 After insert or update of absent on attendance_warning
3 For each row
4 Declare
5 i_sum attendance_warning.absent%type;
6 i_state states.states_name%type;
7 begin
8 select sum(absent) into i_sum FROM attendance_warning where student_id = :new.student_id;
9 if
10 i_sum = 2
11 then
12 i_state := 1;
13 dbms_output.put_line ('2 LECTURES MISSED STUDENT SHOULD BE WARNED IMMEDIATELY');
14 elsif
15 i_sum = 4
16 then
17 i_state := 2;
18 dbms_output.put_line ('4 LECTURES MISSED STUDENT HAS BEEN WITHDRAWN');
19 end if;
20 insert into student_states values (student_states_id_seq.nextval,i_state,:new.student_id);
21* END warning_trig;
SQL> /
Trigger created.
SQL> insert into attendance_warning values(3,4,1,1,null,null);
insert into attendance_warning values(3,4,1,1,null,null)
*
ERROR at line 1:
ORA-04091: table SCOTT.ATTENDANCE_WARNING is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.WARNING_TRIG", line 5
ORA-04088: error during execution of trigger 'SCOTT.WARNING_TRIG'
|
|
|
Re: pl/sql [message #208378 is a reply to message #208361] |
Sun, 10 December 2006 03:22   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
You'll avoid mutating table error easily - just change AFTER event into BEFORE.
However, in my opinion, using DBMS_OUTPUT package won't help much in this case; I believe this application will not be used through SQL*Plus (or similar tool) which would have DBMS_OUTPUT enabled, so that operator would actually SEE the message.
If you, on the other hand, use RAISE_APPLICATION_ERROR so that insert into the 'attendance_warning' table fails and raises an error (along with creating a new record in the 'student_states' table), it would prevent anyone to insert more than 2 records for one student into the 'attendance_warning' table.
Perhaps you should think about it and make a proper decision - will it be DBMS_OUTPUT or RAISE.
Also, having the INSERT statement where you originally put it will ALWAYS create a record in the 'student_states' table; I guess it wasn't your intention.
Of course, I might be wrong, but here is one solution:CREATE OR REPLACE TRIGGER warning_trig
BEFORE INSERT OR UPDATE OF absent ON ATTENDANCE_WARNING
FOR EACH ROW
DECLARE
l_sum ATTENDANCE_WARNING.absent%TYPE;
l_state NUMBER;
BEGIN
SELECT SUM(absent) INTO l_sum
FROM ATTENDANCE_WARNING
WHERE student_id = :NEW.student_id;
IF l_sum = 2
THEN
l_state := 1;
INSERT INTO STUDENT_STATES
VALUES
(student_states_id_seq.NEXTVAL, l_state, :NEW.student_id);
DBMS_OUTPUT.PUT_LINE('2 lectures missed');
-- or RAISE_APPLICATION_ERROR (-20101, '2 lectures missed');
ELSIF l_sum = 4
THEN
l_state := 2;
INSERT INTO STUDENT_STATES
VALUES
(student_states_id_seq.NEXTVAL, l_state, :NEW.student_id);
DBMS_OUTPUT.PUT_LINE('4 lectures missed');
-- or RAISE_APPLICATION_ERROR (-20102, '4 lectures missed');
END IF;
END;
/
|
|
|
Re: pl/sql [message #208427 is a reply to message #208378] |
Sun, 10 December 2006 19:34  |
seyiisq
Messages: 125 Registered: September 2005
|
Senior Member |
|
|
thanks i got it.i was thinking it is not proper to do a select statement on the table the trigger id firing from. so it is possible to manupulate this using 'before insert'
thanks heaps
|
|
|