Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trigger to subtract values from different tables

Status
Not open for further replies.

stuckDuck

Programmer
Jun 30, 2010
30
CA
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;
/
 

I forgot the subtraction part..

select pay_amount - balance into :new.balance from payment;
 
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.
 
thank you thats exactly the error message i received. I will try this suggestion.
 
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 := :eek:ld.balance + :new.pay_amount;
END;
/

Bill
Lead Application Developer
New York State, USA
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top