Results 1 to 10 of 10
  1. #1
    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;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,006
    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;
    /

  3. #3
    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

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,006
    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").

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,431
    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.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,006
    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>

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,431
    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.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,006
    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

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,431
    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.

  10. #10
    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'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •