Trigger to subtract values from different tables

[stuckDuck]

i want to automate the subtraction of the pay_amount from the balance. The columns are on different tables?

create or replace trigger test1
after insert or update or delete
of pay_amount on payment
for each row
BEGIN
select pay_amount into :new.balance from payment;
END;
/

 

[stuckDuck]

I forgot the subtraction part..

select pay_amount - balance into :new.balance from payment;

 

[Dagon]

Your spec is a bit sketchy, to say the least. However, I will tell you that you can't do:

select pay_amount into :new.balance from payment;

when the trigger is on the PAYMENT table. You will receive a mutating table error.

It you really have to do a query against the table that you are updating, you will not be able to use a FOR EACH ROW trigger. You will need to use a statement level trigger.

For Oracle-related work, contact me through Linked-In.

 

[stuckDuck]

thank you thats exactly the error message i received. I will try this suggestion.

 

[Beilstwh]

and if it only updating the new_balance on the one row then


create or replace trigger test1
after insert or update or delete
of pay_amount on payment
for each row
BEGIN
:new.balance := ld.balance + :new.pay_amount;
END;
/

Bill
Lead Application Developer
New York State, USA