If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Before insert Trigger

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 51
Before insert Trigger

I have a table known as Registration in which there are columns StudentID, ModuleId, year, semester

semester = 1 or 2

I want to write a trigger so that student can register on a maximum of 4 modules per semester

i.e.
select count(*), semester from Registration where studentid='241234' group by semester
I tried the following trigger but I am stuck

If I dont use "for each row", I am not allowed to use :new and ld and when I use "for each row", count would be for each row which would be 1 or 0
How can I solve this problem?
Code:
create or replace trigger checkmodulecount
  before insert on Registration

DECLARE
noCourse NUMBER;
nCount NUMBER;
FAILED EXCEPTION;

BEGIN

Select count(*) into nCount 
from Registration
where studentid=:old.studentid;


if noCourse > 3 then
   RAISE FAILED;
END IF;

EXCEPTION

WHEN FAILED THEN

raise_application_error(-20000,'Cannot reigster for more than four courses');
END;
Reply With Quote
  #2 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 4,003
Something like this could do the job. Semester is not included into the SELECT statement; you could, perhaps, use only MAX(semester) because, well, I guess you can register courses for the last semester and not previous one, which is already over. Right?
Code:
CREATE OR REPLACE TRIGGER trg_check
  BEFORE INSERT ON registration
  FOR EACH ROW
DECLARE
  no_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO no_count
    FROM registration
    WHERE studentid = :NEW.studentid;
	
  IF no_count = 4 THEN
     RAISE_APPLICATION_ERROR(-20101, 'Can not register for more than 4 courses');
  END IF;
END;
/
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 51
Thanks one more question:

why this wont work?
select M.MODULEID, M.MODULETITLE, R.YEAR, R.SEMESTER, avg(R.CWMARK), AVG(R.EXAMMARK)
from Registration R, MODULE M WHERE M.MODULEID like 'AR%'
and R.MODULEID=M.MODULEID group by M.MODULEID;

It gives the errror as
not a GROUP BY EXPRESSION
Reply With Quote
  #4 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 4,003
It is because you didn't read manual about the GROUP BY clause carefully enough.
Quote:
Originally Posted by Oracle
ORA-00979 not a GROUP BY expression

Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.

Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.
In other words, include all columns that are not included into AVG function into the GROUP BY (those would be "M.MODULEID, M.MODULETITLE, R.YEAR, R.SEMESTER").
Reply With Quote
  #5 (permalink)  
Old
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,419
Quote:
Originally Posted by Littlefoot
Something like this could do the job. Semester is not included into the SELECT statement; you could, perhaps, use only MAX(semester) because, well, I guess you can register courses for the last semester and not previous one, which is already over. Right?
Code:
CREATE OR REPLACE TRIGGER trg_check
  BEFORE INSERT ON registration
  FOR EACH ROW
DECLARE
  no_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO no_count
    FROM registration
    WHERE studentid = :NEW.studentid;
    
  IF no_count = 4 THEN
     RAISE_APPLICATION_ERROR(-20101, 'Can not register for more than 4 courses');
  END IF;
END;
/
This will not work either, You will get the dreaded ORA-04091(Mutating table). See the following link from asktom on a technique to avoid the error.

http://asktom.oracle.com/~tkyte/Mutate/index.html

__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.
Reply With Quote
  #6 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 4,003
Sorry, Bill, but it wouldn't raise murating table error and, actually, would work:
Code:
SQL> CREATE TABLE registration (studentid NUMBER, course_id NUMBER);

Table created.

SQL> CREATE OR REPLACE TRIGGER trg_check
  2    BEFORE INSERT ON registration
  3    FOR EACH ROW
  4  DECLARE
  5    no_count NUMBER;
  6  BEGIN
  7    SELECT COUNT(*) INTO no_count
  8      FROM registration
  9      WHERE studentid = :NEW.studentid;
 10
 11    IF no_count = 4 THEN
 12       RAISE_APPLICATION_ERROR(-20101, 'Can not register for more than 4 cour
ses');
 13    END IF;
 14  END;
 15  /

Trigger created.

SQL>
Now, try to enter more than 4 courses:
Code:
SQL> INSERT INTO registration (studentid, course_Id) VALUES (1, 101);

1 row created.

SQL> INSERT INTO registration (studentid, course_Id) VALUES (1, 102);

1 row created.

SQL> INSERT INTO registration (studentid, course_Id) VALUES (1, 103);

1 row created.

SQL> INSERT INTO registration (studentid, course_Id) VALUES (1, 104);

1 row created.

SQL> INSERT INTO registration (studentid, course_Id) VALUES (1, 105);
INSERT INTO registration (studentid, course_Id) VALUES (1, 105)
            *
ERROR at line 1:
ORA-20101: Can not register for more than 4 courses
ORA-06512: at "SCOTT.TRG_CHECK", line 9
ORA-04088: error during execution of trigger 'SCOTT.TRG_CHECK'


SQL>
Reply With Quote
  #7 (permalink)  
Old
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,419
Littlefoot,
What version of Oracle are you running. I had heard that they were making changes to allows selects on the trigger table in oracle 10
__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.
Reply With Quote
  #8 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 4,003
This one:
Code:
SQL*Plus: Release 9.0.1.4.0 - Production on Sri Stu 15 14:58:28 2006

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Reply With Quote
  #9 (permalink)  
Old
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,419
I was wrong (the shame of it all), it isn't select that will cause the error. It is update or delete on the triggering table. Sorry.
__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.
Reply With Quote
  #10 (permalink)  
Old
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
That trigger can cause the "table is mutating error", but not with an INSERT...VALUES statement. Suppose we try to copy the 4 rows for student_id=1 to student_id=2:

Code:
SQL> INSERT INTO registration (studentid, course_Id)
    2  SELECT 2, course_id  from registration where studentid=1;

ORA-04091: table TACL.REGISTRATION is mutating, trigger/function may not see it
ORA-06512: at "TACL.TRG_CHECK", line 4
ORA-04088: error during execution of trigger 'TACL.TRG_CHECK'
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On