DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: URGENT: Mutating table

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Unhappy

    Hi,

    Currently I want to write a trigger that will update registration_date whenever the payment_date is being received for a student. However I ran into mutating errors and I have changed my codes to the following:

    create or replace package check_payment as
    type t_payment_date is table of date
    index by binary_integer;
    type t_registration_date is table of date
    index by binary_integer;
    type t_due_date is table of date
    index by binary_integer;
    type t_years is table of number(2)
    index by binary_integer;
    type t_student_id is table of students.id%TYPE
    index by binary_integer;

    v_pay_date t_payment_date;
    v_reg_date t_registration_date;
    v_due_date t_due_date;
    v_years t_years;
    v_student_id t_student_id;
    end check_payment;

    create or replace trigger pay_trigger1
    before insert or update of payment_date on invoices
    for each row
    begin
    check_payment.v_pay_date := :new.payment_date;
    check_payment.v_reg_date := check_payment.v_pay_date;
    check_payment.v_due_date := check_payment.v_pay_date + check_payment.v_period;
    ling_check_payment.v_student_id := :new.student_id;
    end pay_trigger1;

    create or replace trigger pay_trigger2
    after insert or update of pay_receive_date on user_invoices_2112
    begin
    IF pay_trigger1.v_pay_date is not null THEN
    update invoices
    set reg_date = check_payment.v_reg_date
    where student_id = check_payment.v_student_id;
    END IF;
    end pay_trigger2;

    My problems:
    1. I am currently encountering a problem at pay_trigger1, what is wrong with the type?
    2/2 PL/SQL: Statement ignored
    2/35 PLS-00382: expression is of wrong type

    2. My due date should be payment date + 12 months (a yr), how do i convert it to add in months?

    3. In pay_trigger2, how do I pass in the student id to update? or do i have to do a loop?

    Please help and contribute if u can, thanks a lot! ......... :|




  2. #2
    Join Date
    Dec 2000
    Posts
    10
    Hello,

    You are using a table into your triggers, so i think that this code:

    check_payment.v_pay_date := :new.payment_date

    becomes:
    check_payment.v_pay_date(i) := :new.payment_date
    where i is an integer;

    To add some months to a date you can use the add_months function.

    Ex:

    select add_months(to_date('11-DEC-00', 'DD-MON-RR'), 12) from dual

    So the result is '11-DEC-01'.

    For trigger with student id, maybe could you use a package variable.

    Hope this help,
    hace a nice day,
    Stephane.

  3. #3
    Join Date
    Jan 2000
    Posts
    387
    Hi,

    Thanks for your reply... however I do not really get what do u mean.

    1. u mentioned that :
    check_payment.v_pay_date(i) := :new.payment_date
    where i is an integer;

    Why do i need to declare and pass the value in?
    Anyway I did tried your suggestion as follows and I am still encountering the same error :(

    create or replace trigger pay_trigger1
    before insert or update of payment_date on invoices
    for each row
    declare
    i_no integer;
    begin
    check_payment.v_pay_date(i_no) := :new.payment_date;
    check_payment.v_reg_date(i_no) := check_payment.v_pay_date;
    check_payment.v_due_date(i_no) := check_payment.v_pay_date + check_payment.v_period;
    ling_check_payment.v_student_id(i_no) := :new.student_id;
    end pay_trigger1;

    2. I know that I can use add_months in an sql, can i do it this way too for teh pay_trigger1?
    check_payment.v_due_date(i_no) := add_months(check_payment.v_pay_date, check_payment.v_period);

    I do not think this will work right?

    3. How do i pass the student id upon each update of the payment into the trigger?

    Thank 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
  •  


Click Here to Expand Forum to Full Width