Solved

Form Builder Trigger

Posted on 2001-06-26
4
2,417 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
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 34

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

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

707 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

17 Experts available now in Live!

Get 1:1 Help Now