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

    Unanswered: How I can make it shorter...

    Hi all, I'm quite new in this field, so I need someone help, can I? emm, how I can make this Trigger code shorter, is there any other way how I can do this?
    PHP Code:
    Create Trigger Enrolment
    AFTER INSERT OF SubjectName ON SUBJECT 
    For Each Row
    Declare Stud Student%RowTYPE;
    Begin 
        SELECT 
    *
            
    INTO Stud 
            FROM Student
            WHERE stdno
    = :New.stdno;
        IF 
    SubjectName "DAtabase 1" THEN
            SET balance 
    balance - (:New.crtunits *100)
        ELSE

        IF 
    SubjectName "DAtabase 2" THEN
            SET balance 
    balance - (:New.crtunits *100)
        ELSE

        IF 
    SubjectName "Ecommerce" THEN
            SET balance 
    balance - (:New.crtunits *100)
        ELSE

        IF 
    SubjectName "Accounting 1" THEN
            SET balance 
    balance - (:New.crtunits *100)
        ELSE

        IF 
    SubjectName "Book Keeping" THEN
            SET balance 
    balance - (:New.crtunits *100)
        ELSE

        IF 
    SubjectName "Physic 1" THEN
            SET balance 
    balance - (:New.crtunits *100)
        ELSE

        IF 
    SubjectName "Engineering Math" THEN
            SET balance 
    balance - (:New.crtunits *100)
        ELSE

        IF 
    SubjectName "Java Programing" THEN
            SET balance 
    balance - (:New.crtunits *100)
        ELSE

        IF 
    SubjectName "C++ Programing" THEN
            SET balance 
    balance - (:New.crtunits *100)
        ELSE

        IF 
    SubjectName "Advanced C++" THEN
            SET balance 
    balance - (:New.crtunits *100)
        ELSE

        IF 
    SubjectName "Management Information System" THEN
            SET balance 
    balance - (:New.crtunits *100)
        ELSE

        IF 
    SubjectName "Database 3" THEN
            SET balance 
    balance - (:New.crtunits *100)
        
    end if
        
    end if
        
    end if
        
    end if
        
    end if
        
    end if
        
    end if
        
    end if
        
    end if
        
    end if
        
    end if
        
    end if        
    END
    Correct me if I wrong.

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: How I can make it shorter...

    Use elsif to shorten them up -

    EX:

    If 1 = 1 then
    do this

    elsif a=b then
    do this

    elsif a<> b then
    do this

    end if;

    Note you only need one end if, and there is no 'E' in elsif

  3. #3
    Join Date
    Apr 2004
    Posts
    13
    I've receive this msg....
    __________________________________________________ _
    AFTER INSERT OF SubjectName ON SUBJECT
    ____________*
    ERROR at line 2:
    ORA-04073: column list not valid for this trigger type
    __________________________________________________ _
    Correct me if I wrong.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Only UPDATE trigger can contain "OF" clause.

    And, if it is Oracle SQL, it lacks in UPDATE table statement.

    Your trigger (which will compile) could be something like this:
    PHP Code:

    CREATE TRIGGER enrolment
       AFTER UPDATE OF subjectname
       ON subject
       
    FOR EACH ROW
    DECLARE
       
    stud   student%ROWTYPE;
    BEGIN
       SELECT 
    *
         
    INTO stud
         FROM student
        WHERE stdno 
    = :NEW.stdno;

       IF 
    subjectname "DAtabase 1"
       
    THEN
          UPDATE subject
             SET balance 
    balance - (:NEW.crtunits 100);
       
    ELSIF subjectname "DAtabase 2"
       
    THEN
          UPDATE subject
             SET balance 
    balance - (:NEW.crtunits 100);
       
    ELSIF subjectname "Ecommerce"
       
    THEN
          UPDATE subject
             SET balance 
    balance - (:NEW.crtunits 100);
       
    /* etc. etc. */
       
    ELSIF subjectname "Database 3"
       
    THEN
          UPDATE subject
             SET balance 
    balance - (:NEW.crtunits 100);
       
    END IF;
    END
    Last edited by Littlefoot; 04-16-04 at 08:54.

Posting Permissions

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