Home » SQL & PL/SQL » SQL & PL/SQL » how can i create a trigger (Oracle 10g Windows)
how can i create a trigger [message #314666] Thu, 17 April 2008 07:45 Go to next message
dbagopi
Messages: 3
Registered: June 2006
Junior Member
Hi Friends,
I have two tables customers and purchases. And definitions of the tables are as follows:

TABLE1 : customers (id number(10) CONSTRAINT CUSTOMER_ID_PK PRIMARY KEY,
name VARCHAR2(100),
total_purchases NUMBER(10,2))

TABLE2 : purchases (id NUMBER(10) CONSTRATINT PURCHASES_ID_PK,
custid NUMBER(10) CONSTRAINT PURCHASES_NUMBER_NN NOT NULL
CONSTRAINT PURCHASES_CUSTID_FK REFERENCES CUSTOMER(ID),value NUMBER(10))


My problem is If a row is inserted in purchases table (means after insert trigger) a row in customer table with the same custid must be updated. For this I have to write a trigger.
Eg: If a row is inserted in Purchases table with custid=100, Then the row in customers table with id=100 should be updated like this:

update customers
set customers.total_purchases=customers.total_purchases+
purchases.value
where customer.id=purchases.custid;

Can any one help me to write a trigger for this.
Regards
Gopi
Re: how can i create a trigger [message #314667 is a reply to message #314666] Thu, 17 April 2008 07:46 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
This is as basic as basic can be for a trigger. Why don't you read up on the documentation about triggers in Oracle? It's probably on the first page.

http://tahiti.oracle.com
Re: how can i create a trigger [message #314671 is a reply to message #314666] Thu, 17 April 2008 07:54 Go to previous messageGo to next message
dbagopi
Messages: 3
Registered: June 2006
Junior Member
Hi Joy division,
I referred some books. In all those books, they have given examples for insert only(target). But my goal is after inserting a row in 2nd table, I have to update a row in 1st table.
I am thankful to you if you give a solution or you may suggest a book for it

[Updated on: Thu, 17 April 2008 08:00]

Report message to a moderator

Re: how can i create a trigger [message #314698 is a reply to message #314671] Thu, 17 April 2008 09:12 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Did you look at the link that Joy provided?
Re: how can i create a trigger [message #314758 is a reply to message #314667] Thu, 17 April 2008 13:14 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
And just to make it more specific, http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#SQLRF01405
Re: how can i create a trigger [message #314903 is a reply to message #314758] Fri, 18 April 2008 04:14 Go to previous message
dbagopi
Messages: 3
Registered: June 2006
Junior Member
Hi joy and all,
Got the solution.
I am very new to write triggers. So I took the help.
Thanx for your response.
Gopi
Previous Topic: Oracle error
Next Topic: How to drop and recreate a schema?
Goto Forum:
  


Current Time: Thu Dec 08 16:45:04 CST 2016

Total time taken to generate the page: 0.16364 seconds