Solved

Form Builder Trigger

Posted on 2001-06-26
4
2,426 Views
Last Modified: 2012-06-21

I'm trying to design an inventory/warehouse database application and the tables have the following structure:

               
Name                            Null?         Type

RECEIVED_DATE                                 DATE
PARTICULARS                     NOT NULL      VARCHAR2(40)
RECEIVED                                      NUMBER(10)
ISSUED                                        NUMBER(10)
BALANCE                                       NUMBER(10)

      We are basically catering two different types of records in case of PARTICULARS column. One type of record are specifically used in case of receiving items, i.e., if we have records in the 'SR' category then the record entered for this particular type should only have values in the RECEIVED column and the ISSUED column will be left blank. Similarly in case of 'CIV' type records the RECEIVED column should be left blank and the ISSUED column will have certain values. The table after being populated looks something like the following:

     
Received Date     Particulars     Received   Issued  Balance
     
19-JUN-2001     SR1                2                   2                                
20-JUN-2001     CIV1                         1        1                        

 I want to write a trigger on the BALANCE field/item, so that every time we enter values in the RECEIVED or ISSUED column, the BALANCE column should be automatically updated and the value should be obtained from the addition or subtraction of the BALANCE column value of the previous record.
I have written the following trigger:



declare
bal number;
begin
          if :particulars like 'SR%' then
               previous_record;
               bal:=:balance;
               next_record;
     :balance:=bal+:received;
          else
          if :particulars like 'CIV%' then
               previous_record;
               bal:=:balance;
               next_record;
               :balance:=bal-:issued;
          end if;
          end if;
end;

This trigger works perfectly fine except when we are inserting the first record of the table, as the cursor goes to the first item of the first record and no values is inserted in the BALANCE column (cause we are using previous_record built-in whcih checks the values for the previous record and since there is no previous record thats why it returns no value. But if  I have inserted the first record in the database and then inserts the rest of the records from the Form, the trigger works fine. Is there any other way we can deal with this problem.  

Thanks


Hassan
0
Comment
Question by:hayub
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 

Expert Comment

by:okhari
ID: 6228146
I think u can have a parameter variable and in the when-validate-record u can add or substract the value to the parameter based on received or issued.This parameter can be used to substitue the value for the balance column.For example
If :particulars like 'SR%' then
              :parameter.value := :parameter.value  + +:received;
else
         if :particulars like 'CIV%' then
              :parameter.value := :parameter.value -:issued ;
end if;
end if;
:balance := :parameter.value;

I think this should solve ur problem.
         
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6228430
Have you tried using "nvl", like this:
bal := nvl(:balance,0);

That should do the job.  I am concerned though about this table design.  It is a violation of data normalization to carry a running total (or balance) on a detail record.  There may be advantages to having this balance stored, but that can lead to complicated programming and/or inaccurate data at times.  What happens if a record is deleted, for example?

0
 

Accepted Solution

by:
danhw earned 100 total points
ID: 6240221
To get around the problem you are describing, you can change your trigger to this:

declare
bal number;
starting_record integer;
begin
     starting_record := :system.trigger_record;
     if :particulars like 'SR%' then
              if :starting_record > 1 then
                  previous_record;
                  bal:=:balance;
                  next_record;
                 :balance:=bal+:received;
              else
                 :balance := :received;
              end if;
     elsif :particulars like 'CIV%' then
              if :starting_record > 1 then
                 previous_record;
                 bal:=:balance;
                 next_record;
                 :balance:=bal-:issued;
              else
                 :balance := :issued;  
              end if;
      end if;
end;


However, following markgeer's comment, what happens when you have 3 rows, and you change the 2nd row? It updates itself, but row 3's balance needs to be updated also, doesn't it?

0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6837070
ADMINISTRATION WILL BE CONTACTING YOU.  THIS IS BEING POSTED IN ALL YOUR OPEN QUESTIONS TODAY.

This question appears to have been abandoned. Your options are:
 
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question. Again, you must tell the other participants why you wish to do this.

For special handling needs, please post a zero point question in the link below, include the question QID/link.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click this Help Desk link for Member Guidelines, Member Agreement and the Question/Answer process:  Click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues.
http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.11756459.html
http://www.experts-exchange.com/questions/Q.11966818.html
http://www.experts-exchange.com/questions/Q.20002985.html
http://www.experts-exchange.com/questions/Q.20080116.html
http://www.experts-exchange.com/questions/Q.20117054.html
http://www.experts-exchange.com/questions/Q.20136568.html
http://www.experts-exchange.com/questions/Q.20141808.html
http://www.experts-exchange.com/questions/Q.20170683.html
http://www.experts-exchange.com/questions/Q.20177412.html
http://www.experts-exchange.com/questions/Q.20192197.html
http://www.experts-exchange.com/questions/Q.20211088.html
http://www.experts-exchange.com/questions/Q.20244652.html
http://www.experts-exchange.com/questions/Q.20245681.html
http://www.experts-exchange.com/questions/Q.20250442.html
http://www.experts-exchange.com/questions/Q.20259920.html
http://www.experts-exchange.com/questions/Q.20263344.html
http://www.experts-exchange.com/questions/Q.20272565.html
LOCKED AWAITING ACCEPTANCE
http://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20254380
http://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20170530
http://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20156898
http://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20073456
http://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20015528


PLEASE DO NOT AWARD THE POINTS TO ME.  
 
------------>  EXPERTS:
 
Please leave any comments regarding this question here on closing recommendations if this item remains inactive another three days.
 
Thank you everyone.
 
Moondancer
Moderator @ Experts Exchange


P.S.  For year 2000 questions, special attention is needed to ensure the first correct response is awarded, since they are not in the comment date order, but rather in Member ID order.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

691 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