Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    13

    Unanswered: Check my code...

    Hi all, can someone check my Trigger code... I never learn this stuff before... and I try to learn it...

    Example,
    Question: A student and his/her mentor cannot enrol in the same course (i.e. mentors and students they mentoring are not enrolled in the same course)
    PHP Code:
    CREATE OR REPLACE TRIGGER nevermentor
    AFTER UPDATE OF STUDENT

    DECLARE stdno STUDENT.STDNO%TYPE
           
    BEGIN
    SELECT S
    .STDNO 
    INTO stdno
    FROM S STUDENT
    WHERE S
    .STDNO = :NEW.MENTOR;

    IF NEW.
    MENTOR =:S.STDNO 
    AND S.COURSECODE  =:NEW.COURSE THEN
        raise_application_error
    (-20012'Mentor and his/her student cannot enrol in the same course');
    END IF;
    END
    Thanks in advance....
    Attached Thumbnails Attached Thumbnails studenttable.gif  
    Correct me if I wrong.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Check my code...

    There are some mistakes and issues in your code:

    1) "IF NEW.MENTOR = .STDNO" is invalid. That line should read:

    IF :NEW.MENTOR = stdno

    2) S.COURSECODE is meaningless outside of the SELECT statement above it.

    3) Table alias must come after table name not before ("STUDENT S" not "S STUDENT")

    4) :NEW and :OLD can only be used in a FOR EACH ROW trigger, which this is not. And FOR EACH ROW triggers cannot select from the table they are based on, or you will get the "mutating table" error.

    5) You do not deal with the case where the mentor's record is updated.

    6) You do not handle INSERTs.

    I think your trigger needs to be more like this:
    PHP Code:
    CREATE OR REPLACE TRIGGER nevermentor
    AFTER INSERT 
    OR UPDATE OF STUDENT
    DECLARE 
      
    v_count INTEGER;
    BEGIN
      SELECT COUNT
    (*)
      
    INTO v_count
      FROM DUAL
      WHERE EXISTS
      
    SELECT NULL
        FROM STUDENT S
    STUDENT M
        WHERE M
    .STDNO S.MENTOR
        
    AND S.COURSECODE M.COURSECODE
        
    AND ROWNUM 1
      
    );

      IF 
    v_count 0 THEN
        raise_application_error
    (-20012'Mentor and his/her student cannot enrol in the same course');
      
    END IF;
    END
    This looks for any student/mentor pairs that attend the same course after the update/insert.

    Since this question is Oracle-specific I will move it to the appropriate forum.

  3. #3
    Join Date
    Apr 2004
    Posts
    13
    Thanks... is this an Oracle? I though it was PL/SQL... I dont know what is the diff. between them..
    Correct me if I wrong.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oracle = the make of database management system (DBMS) and all its components, including PL/SQL.

    PL/SQL = Oracle's own procedural language for writing code to work with an Oracle database.

  5. #5
    Join Date
    Apr 2004
    Posts
    13
    i see, thanks so much, what about SQL?
    Correct me if I wrong.

  6. #6
    Join Date
    Apr 2004
    Posts
    13
    I've got this error... Im using Oracle iSQL *Plus to upload it.
    ___________________
    DECLARE v_count INTEGER;
    *
    ERROR at line 3:
    ORA-00969: missing ON keyword
    Correct me if I wrong.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by DonnyDB
    I've got this error... Im using Oracle iSQL *Plus to upload it.
    ___________________
    DECLARE v_count INTEGER;
    *
    ERROR at line 3:
    ORA-00969: missing ON keyword
    Impossible to debug without seeing the whole trigger(?) code.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    SQL = Structured Query Language. This is common to many DBMSs: Oracle, Microsoft SQL Server, IBM DB2, mySQL, ... (though there are always some differences). It includes SELECT, INSERT, UPDATE, DELETE statements.

    You really are new to all this, aren't you?!!

Posting Permissions

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