-
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! ......... :|
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|