How I can make it shorter...

DonnyDB · #1 (**permalink**) 04-15-04, 21:22

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;

ss659 · #2 (**permalink**) 04-15-04, 22:49

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

DonnyDB · #3 (**permalink**) 04-15-04, 22:59

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
__________________________________________________ _

Littlefoot · #4 (**permalink**) 04-16-04, 07:49

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;

Internet Services

Web Development

Digital Marketing

Consumer Tech