Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Problem
Trigger Problem [message #249808] Thu, 05 July 2007 23:52 Go to next message
jainarayan5484
Messages: 38
Registered: August 2006
Location: DELHI
Member
I Have a Table Like AdjustSize and its Descriptiotn is given Bellow;

SQL> Desc Adjustsize;
Name Null? Type
----------------------------------------- -------- ------
RECORD_NO NOT NULL NUMBER
SR_NO NOT NULL NUMBER
ITEM_SIZE NUMBER
ITEM_CLASS VARCHA
ITEM_GROUP VARCHA
ITEM_QUALITY VARCHA
BK_EXISTING NUMBER
BK_CAL_BASIS VARCHA
BK_PER NUMBER
BK_PROPOSED NUMBER
MTR_PER_TON NUMBER
FREIGHT_BK NUMBER
DISC_BK NUMBER
COIN_BK NUMBER


Its Records is as:

SQL> Select * from Adjustsize where Record_no=34;

Record no SR_NO SIZE Class Group Quality BK_EXISTING BK_PER BK_PROPOSED MTR_PER_TON
--------- ------ ----- ----- ---- ------ -------- ------ ---------- ----------
34 1 100 L BK SS 361 -1000 351.5 106
34 2 100 L BK SS3 366 0 356.5 106


Now I Want To Calculate BK_ProPosed Column Value by Formula of

ROUND(((MTR_PER_TON*NVL(BK_EXISTING,0))+NVL(BK_PER,0))/MTR_PER_TON,2)

But this Formuala is For Only Sr_no=1 and User Wants BK_Proposed of Sr_no=2 is Calculated by Sr_no=1 by
Adding 5 more

I explane Breifly as

Bk_Proposed of sr_no=1 is Equal to ROUND(((MTR_PER_TON*NVL(BK_EXISTING,0))+NVL(BK_PER,0))/MTR_PER_TON,2);

Bk_Proposed of Sr_no=2 is Equal to (Bk_Proposed of Sr_no=1 + 5)


it is sure that Table Contain Only Two Records For a Record_no ;

How Can I Do by Using Trigger pls help me.
Re: Trigger Problem [message #249809 is a reply to message #249808] Thu, 05 July 2007 23:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Please consider reading & FOLLOWING post guideline as specified in STICKY posts at top of forum.
Please pay attention to use of code tags.
It is not clear to me what are inputs, processes & expected outputs.
Re: Trigger Problem [message #249819 is a reply to message #249808] Fri, 06 July 2007 01:01 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What should happen if someone change sr_no?
How can a trigger see sr_no=1 if sr_no=1 is not yet committed?
In short, you generally can't do it with a trigger unless you are in a very specific case like only one session handles every rows of a record_no.

Regards
Michel
Previous Topic: number of rows in all tables in a schema...
Next Topic: How to UPDATE a table column from another table value?
Goto Forum:
  


Current Time: Wed Dec 07 22:34:25 CST 2016

Total time taken to generate the page: 0.09683 seconds