Re: trigger problem

Denormalization (which is what you are doing by storing calculated fields) has the benefit of simplicity but the drawback of synchronization issues at one point or another. A better idea is to create a function to hide the complexity:

FUNCTION INVENTORY(I_PRODUCTID IN NUMHBER) RETURN NUMBER
IS
  CURSOR cInventory (ncvProductID NUMBER)   IS
    SELECT...   nInventory NUMBER;
BEGIN
  OPEN cInventory;
  FETCH cInventory INTO nInventory;
  CLOSE cInventory;

  RETURN nInventory;
END INVENTORY; Then in queries where you would have selected your calculated field, make a call to the INVENTORY function passing in the productid. Make sure to tune the function since it will be called for every row returned in a query. Yes, there is an added overhead (slight), but I'd rather be right and fast than wrong and faster...

Hope this was useful to your situation.

Michael J. Ort

In article <38EDC4BC.458C7FF0_at_access.stp.si>,   Edo <edo_at_access.stp.si> wrote:
> Hi,
> I'm useing Oracle 7.3
>
> I have several tables, that contains information of some transitions.
> Some of data: buyer, pruduct and amount of bought product.
> I put trigger on each of them, so they would calculate amount of
 product
> left in storage and write it down in another table table.
>
> When i tested it worked well. But lately i found some difference
 between
> data stored in table(data writen by triggers), and my own calculated
> data using querys. Retriving summary data is very simple so i didn't
 go
> wrong. Querys were tested by me and by some of my friends.
> Only Triggers can access table, except me and i never lock table or
> write in it.
>
> My question is: Why and how triggers failed?
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 07 2000 - 00:00:00 CDT