I'm trying to design an inventory/warehouse database application and the tables have the following structure:
Name Null? Type
PARTICULARS NOT NULL VARCHAR2(40)
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:
if :particulars like 'SR%' then
if :particulars like 'CIV%' then
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.