Solved

SQL: Trigger Help

Posted on 2014-04-22
13
553 Views
Last Modified: 2014-04-22
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;
/
0
Comment
Question by:Pancake_Effect
  • 7
  • 6
13 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015229
Try this:
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...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015238
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;

...
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015294
>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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015304
>>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?
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015331
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?

CREATE OR REPLACE TRIGGER UPDATE_BALANCE
AFTER INSERT OF INV_AMOUNT ON INVOICE
      BEGIN
            UPDATE CUSTOMER
            SET CUST_BAL = CUST_BAL + INV_AMOUNT
END;
/

Open in new window


On your first post you used ":new.INV_AMOUNT" what does that exactly do?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015371
>>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:

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;
/ 

Open in new window


You are also missing the closing ';' on the UPDATE statement.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015420
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.

error
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40015435
Just noticed another issue with the trigger.  No WHERE clause on the update.

Try 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;
/

Open in new window



Here is my test case setup:
create table customer(id number, cust_bal number);

insert into customer values(1,10);
insert into customer values(2,20);
commit;

create table invoice(cust_id number, inv_amount number);

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;
/

show errors

select * from customer;
insert into invoice values(1,10);
select * from customer;

Open in new window

0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015452
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:

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;
/

Open in new window

0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 40015474
>>bad bind variable 'NEW.CUST_ID'1

Do you have a CUST_ID column in your table?

Remember the ":NEW" piece is a reference to the row being updated so anything after that is a column in that row.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015517
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:

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;

Open in new window

?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015552
>>Does that mean:

Seems OK to me (meaning nothing jumps out at me as wrong).  Does it work?
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015610
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now