update balance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update balance

Author

Topic

xxzimmer
Starting Member

5 Posts

Posted - 2006-07-25 : 08:57:25

I am trying to write a trigger which updates the customers balance when an invoice is deleted. The tables are created and I can post if needed, but I get a compilation error when I use the follow code.

create or replace trigger trg_updatecustbalance2
after delete on invoice
for each row
begin
update customer
set cust_balance = cust_balance - inv_amount
where customer.cust_num = invoice.cust_num;
end;
/

Can anyone help me figure out where my error?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-25 : 09:02:36

Is this T-SQL ?

I believe it's Oracle SP

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-07-25 : 09:04:58

update customer
set cust_balance = cust_balance - inv_amount
where customer.cust_num = invoice.cust_num;

You're going to need an INVOICE table in there too, aren't you? Or does Oracle automatically use the table that the trigger is on?

Kristen

xxzimmer
Starting Member

5 Posts

Posted - 2006-07-25 : 09:05:14

should this be posted somewhere else or is oracle not discussed on this forum?

xxzimmer
Starting Member

5 Posts

Posted - 2006-07-25 : 09:05:50

there is a customer and an invoice table already created.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-07-25 : 09:08:34

MS SQL Server in it's different versions are the only databases discussed on this forum. www.dbforums.com might be the one for you...good luck

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

xxzimmer
Starting Member

5 Posts

Posted - 2006-07-25 : 09:09:31

quote:
Originally posted by Lumbago

MS SQL Server in it's different versions are the only databases discussed on this forum. www.dbforums.com might be the one for you...good luck

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

thank you

Kristen
Test

22859 Posts

Posted - 2006-07-25 : 09:11:10

"there is a customer and an invoice table already created."

OK, but in SQL Server you would need to reference the INVOICE table, as well as the CUSTOMER table, in the UPDATE statement. However, I don't know enough about Oracle to know if that is necessary in a trigger [which happens to be on the INVOICE table], or not; sorry.

Kristen

xxzimmer
Starting Member

5 Posts

Posted - 2006-07-25 : 09:13:30

quote:
Originally posted by Kristen

"there is a customer and an invoice table already created."

OK, but in SQL Server you would need to reference the INVOICE table, as well as the CUSTOMER table, in the UPDATE statement. However, I don't know enough about Oracle to know if that is necessary in a trigger [which happens to be on the INVOICE table], or not; sorry.

Kristen

OK. Thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources