Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Posts
    29

    Question Unanswered: how to rollback an updating process?

    Hi, i have a table trigger which will check for the user inputs in that table and will update accordingly to another table. My problem is , if the user entered an invalid input to the table i want the table trigger should rollback that transaction!
    Below is my trigger. if correct input i wil just update accordingly but if not then i will insert a reason to a table with the student id. I don't know how to rollback the update or inserting process to this table after i insert the reason to a table.

    ================================================== ======
    CREATE OR REPLACE TRIGGER "MCIIU_INTERFACE"."TRIG_I_U_D_OUA_STUDENT"
    BEFORE
    INSERT OR UPDATE OR DELETE
    ON "MCIIU_INTERFACE"."OINT_ADMT_STUDENT" FOR EACH ROW
    BEGIN

    IF UPDATING THEN

    if :NEW.CURRENT_SEMESTER > 0 then

    UPDATE SASMCIIULIVE.OINT_ADMT_STUDENT SET UNI_ID = :NEW.UNI_ID,
    STUDENT_ID = :NEW.STUDENT_ID,
    APP_REG_NO = :NEW.APP_REG_NO,
    ADMISSION_NO = :NEW.ADMISSION_NO,
    ADM_DATE = :NEW.ADM_DATE,
    FIRST_NAME = :NEW.FIRST_NAME,
    LAST_NAME = :NEW.LAST_NAME,
    STUDENT_NAME = :NEW.STUDENT_NAME,
    ACADEMIC_YEAR_ID = :NEW.ACADEMIC_YEAR_ID,
    ACADEMIC_INTAKE_ID = :NEW.ACADEMIC_INTAKE_ID,
    FACULTY_ID = :NEW.FACULTY_ID,
    PRG_ID = :NEW.PRG_ID,
    DEGREE_ID = :NEW.DEGREE_ID,
    STATUS_ID = :NEW.STATUS_ID,
    GENDER = :NEW.GENDER,
    TITLE_ID = :NEW.TITLE_ID,
    DATE_OF_BIRTH = :NEW.DATE_OF_BIRTH,
    IC_NO = :NEW.IC_NO,
    OLD_IC_NO = :NEW.OLD_IC_NO,
    PASSPORT_NO = :NEW.PASSPORT_NO,
    CURRENT_SEMESTER = :NEW.CURRENT_SEMESTER,
    STUDY_TYPE = :NEW.STUDY_TYPE,
    CGPA = :NEW.CGPA,
    STUDNET_TYPE = :NEW.STUDNET_TYPE,
    HOSTEL_REQ = :NEW.HOSTEL_REQ,
    CREDIT_POINTS = :NEW.CREDIT_POINTS WHERE STUDENT_ID = :OLD.STUDENT_ID;

    else

    insert into INVALID_ADMT_STUDENT (UNI_ID,STUDENT_ID,REASON) VALUES(:NEW.UNI_ID,:OLD.STUDENT_ID,'STUPIDLY INSERTED THE CURRENT SEMESTER VALUE');

    end if;
    END IF;

    END;
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

  2. #2
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    http://download-uk.oracle.com/docs/c...g13trg.htm#431
    "Also, no transaction control statements are allowed in a trigger. ROLLBACK, COMMIT, and SAVEPOINT cannot be used."
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Use RAISE to throw an exception back to the calling app. The calling app can then do a rollback.

    Alan

  4. #4
    Join Date
    Apr 2007
    Posts
    29
    Use RAISE to throw an exception back to the calling app. The calling app can then do a rollback.

    Alan
    Hi AlanP,
    mind show me with some samples...coz i use this attached to that table it self.thus, im not calling this trigger from any app! i don't know how am i suppose to throw it as an error or something then do the rollback function. Mind show me some examples..or little guide to me.thanx realy appreciated ur help!
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The RAISE automatically throws an error back to whatever was changing the table. Try it and you'll see.

    Look up exceptions in the PLSQL manual or google it.

    Alan

  6. #6
    Join Date
    Apr 2007
    Posts
    29
    Hi thanks, I found the way already finally!
    i can use raise application error
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

Posting Permissions

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