Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql
pl/sql [message #208094] Fri, 08 December 2006 02:41 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #208260 is a reply to message #208258] Fri, 08 December 2006 21:07 Go to previous messageGo to next message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
but still cant get a way round it
Re: pl/sql [message #208298 is a reply to message #208260] Sat, 09 December 2006 03:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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 Go to previous message
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
Previous Topic: chr(10) function
Next Topic: tigger
Goto Forum:
  


Current Time: Fri Dec 02 12:39:12 CST 2016

Total time taken to generate the page: 0.07989 seconds