SQL: Trigger Help
I'm working on trying to understand triggers. (I'm basically going through oracles site with my book I bought) I'm trying to learn SQL, because I do it a little on the side with my job. I have an example here that I'm trying to work out.
Basically there are two tables. A invoice table and a customer table. The invoice houses any new invoice information that comes in. The Customer table has a column that states the customer overall balance.
So if a invoice is entered with a new amount, I want that amount to be added to the customer's balance column. Here is what I'm thinking it should be below. but doesn't work sadly:
CREATE OR REPLACE TRIGGER UPDATE_BALANCE
AFTER INSERT OR UPDATE OF INV_AMOUNT ON INVOICE
BEGIN
UPDATE CUSTOMER
SET CUST_BAL = CUST_BAL + INV_AMOUNT
END;
/
Basically there are two tables. A invoice table and a customer table. The invoice houses any new invoice information that comes in. The Customer table has a column that states the customer overall balance.
So if a invoice is entered with a new amount, I want that amount to be added to the customer's balance column. Here is what I'm thinking it should be below. but doesn't work sadly:
CREATE OR REPLACE TRIGGER UPDATE_BALANCE
AFTER INSERT OR UPDATE OF INV_AMOUNT ON INVOICE
BEGIN
UPDATE CUSTOMER
SET CUST_BAL = CUST_BAL + INV_AMOUNT
END;
/
You might need to check in the trigger it you are inserting or updating.
Something like:
....
If inserting then
UPDATE CUSTOMER SET CUST_BAL = CUST_BAL + :new.INV_AMOUNT;
else --must be updating
UPDATE CUSTOMER SET CUST_BAL = (CUST_BAL + :new.INV_AMOUNT) - :old.INV_AMOUNT;
end if;
...
Something like:
....
If inserting then
UPDATE CUSTOMER SET CUST_BAL = CUST_BAL + :new.INV_AMOUNT;
else --must be updating
UPDATE CUSTOMER SET CUST_BAL = (CUST_BAL + :new.INV_AMOUNT) - :old.INV_AMOUNT;
end if;
...
ASKER
>Say the original value was 10 so cust_bal was incremented by 10 on the insert.
>Now you update the column to 20. What happens to cust_bal? It is now off...
I believe the intention. For example if the CUST_BAL has a currently has a balance of 10...if a invoice comes in with another 10 dollars, we do want it to be added..so the CUST_BAL is now 20. Does that make sense?
>Now you update the column to 20. What happens to cust_bal? It is now off...
I believe the intention. For example if the CUST_BAL has a currently has a balance of 10...if a invoice comes in with another 10 dollars, we do want it to be added..so the CUST_BAL is now 20. Does that make sense?
>>Does that make sense?
Yes but your trigger is also for UPDATEs. You need to account for updating of an existing row.
You also don't account for deletes yet.
Does that make sense?
Yes but your trigger is also for UPDATEs. You need to account for updating of an existing row.
You also don't account for deletes yet.
Does that make sense?
ASKER
Ah okay, yeh now it does. For simplicity sakes for now I kinda just want to learn how to do this for a INSERT. I'll take update out of the picture along with the delete (good thing you mentinoned that though would of never thought of it).
So something like this?
On your first post you used ":new.INV_AMOUNT" what does that exactly do?
So something like this?
CREATE OR REPLACE TRIGGER UPDATE_BALANCE
AFTER INSERT OF INV_AMOUNT ON INVOICE
BEGIN
UPDATE CUSTOMER
SET CUST_BAL = CUST_BAL + INV_AMOUNT
END;
/On your first post you used ":new.INV_AMOUNT" what does that exactly do?
>>On your first post you used ":new.INV_AMOUNT" what does that exactly do?
It is a reference to the firing row's values.
Overview:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/srvrside.htm#i13313
SQL:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#LNPLS01374
Also noticed another problem. Without the "FOR EACH ROW" syntax it is a table level trigger. For what you want you need a row level trigger:
You are also missing the closing ';' on the UPDATE statement.
It is a reference to the firing row's values.
Overview:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/srvrside.htm#i13313
SQL:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#LNPLS01374
Also noticed another problem. Without the "FOR EACH ROW" syntax it is a table level trigger. For what you want you need a row level trigger:
CREATE OR REPLACE TRIGGER UPDATE_BALANCE
AFTER INSERT OF INV_AMOUNT ON INVOICE
FOR EACH ROW
BEGIN
UPDATE CUSTOMER
SET CUST_BAL = CUST_BAL + :NEW.INV_AMOUNT;
END;
/ You are also missing the closing ';' on the UPDATE statement.
ASKER
Doesn't seem to like the row command. Thanks for the links, will be helpful.
According to the link you sent, it looks like the only other type of trigger that could work would be what they call a "statement trigger"? Not sure if that's what is causing the issue.
According to the link you sent, it looks like the only other type of trigger that could work would be what they call a "statement trigger"? Not sure if that's what is causing the issue.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hmm it's saying this:
ERROR at line 2: PLS-00049: bad bind variable 'NEW.CUST_ID'1. create or replace trigger update_balance
2. after insert on invoice
3. for each row
4. begin
using exactly this:
ERROR at line 2: PLS-00049: bad bind variable 'NEW.CUST_ID'1. create or replace trigger update_balance
2. after insert on invoice
3. for each row
4. begin
using exactly this:
create or replace trigger update_balance
after insert on invoice
for each row
begin
update customer set cust_bal = cust_bal + :new.inv_amount where id=:new.cust_id;
end;
/SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Basically the involved info is:
Customer (CUST_NUM, CUST_BAL)
INVOICE (INV_NUM, CUST_NUM, INV_AMOUNT)
Any time a INV_AMOUNT is added, the INV_AMOUNT needs to be added to CUST_BAL
Does that mean:
Customer (CUST_NUM, CUST_BAL)
INVOICE (INV_NUM, CUST_NUM, INV_AMOUNT)
Any time a INV_AMOUNT is added, the INV_AMOUNT needs to be added to CUST_BAL
Does that mean:
create or replace trigger update_balance
after insert on INVOICE
for each row
begin
update customer set CUST_BAL = CUST_BAL + :new.INV_AMOUNT
where CUST_NUM=:new.CUST_NUM;
end;
>>Does that mean:
Seems OK to me (meaning nothing jumps out at me as wrong). Does it work?
Seems OK to me (meaning nothing jumps out at me as wrong). Does it work?
ASKER
Work great now, sorry I was just double-checking I put the info in right. I think I now roughly understand triggers now (hopefully) haha. Thanks for your help again! I really appreciate it.
SET CUST_BAL = CUST_BAL + :new.INV_AMOUNT
Where you may run into problems is on the update.
Say the original value was 10 so cust_bal was incremented by 10 on the insert.
Now you update the column to 20. What happens to cust_bal? It is now off...