Hi,

I am starting to play with EDB v2.0 with the view of porting over one of my
projects, I am having a little problem with this Procedure.    I would also
be interested to hear view on whether this is the best approach, or whether
I should use a trigger in this case.

Basically, I have 3 tables, being AccountHeader, Transaction and AccTotals.
I want to update the AccTotals table each time a new transaction is entered.
My problem is that it always adds a new record (i.e. the BOF and EOF of the
SumCursor appear to always be true).....am I doing something wrong, or do I
not understand something.

BTW, I also found (what I believe to be) a Bug.  If I use DECLARE ATotal
FLOAT DEFAULT ' ', then it will be accepted, but naturally enough an
exception is thrown when I try to run the procedure.

BEGIN
 DECLARE TxnCursor CURSOR WITHOUT RETURN FOR TxnStmt;
 DECLARE SumCursor CURSOR WITHOUT RETURN FOR AccStmt;
 DECLARE NewTotal STATEMENT;
 DECLARE ATotal FLOAT DEFAULT 0;

 PREPARE TxnStmt FROM 'SELECT SUM(Value) AS Total FROM Transaction WHERE
Year=? AND Period=? AND AccountID=?';
 PREPARE AccStmt FROM 'SELECT * FROM AccTotals WHERE YA=? AND Period=? AND
YA=?';
 PREPARE NewTotal FROM 'INSERT INTO AccTotals ( YA, Period, IsActual,
AccountID, Amount ) VALUES( ?,?,FALSE,?,?)';

 OPEN TxnCursor USING Year, Period, AccountID;
 OPEN SumCursor USING Year, Period, AccountID;

 FETCH FROM TxnCursor( Total ) INTO ATotal;

 START TRANSACTION;
   IF BOF( SumCursor ) AND EOF( SumCursor ) THEN
     --Add a new Summary total for the account
     EXECUTE NewTotal USING Year, Period, AccountID, ATotal;
   ELSE
     --Update the existing total
     IF EOF( TxnCursor ) THEN
       UPDATE SumCursor SET Amount = 0;
     ELSE
       UPDATE SumCursor SET Amount = ATotal;
     END IF;
   END IF;
 BEGIN
   COMMIT;
 EXCEPTION
   ROLLBACK;
 END;
END

Hi,

Forget this....the problem was with my SQL, but I have also managed to
simplify it a bit.  I am still interested to know though whether it is
better to do this type of thing in this manner, or should I look at using a
trigger and passing in the old and new values?

BTW, I was planning to use this from within a Trigger....here is the new
code, if anyone is interested
BEGIN
 DECLARE TxnCursor CURSOR WITHOUT RETURN FOR TxnStmt;
 DECLARE SumCursor SENSITIVE CURSOR WITHOUT RETURN FOR AccStmt;
 DECLARE ATotal FLOAT DEFAULT 0;

 PREPARE TxnStmt FROM 'SELECT SUM(Value) AS Total FROM Transaction WHERE
Year=? AND Period=? AND AccountID=?';
 PREPARE AccStmt FROM 'SELECT * FROM AccTotals WHERE YA=? AND Period=? AND
AccountID=?';

 OPEN TxnCursor USING Year, Period, AccountID;
 OPEN SumCursor USING Year, Period, AccountID;

 FETCH FROM TxnCursor( Total ) INTO ATotal;

 START TRANSACTION;
   IF ( ROWCOUNT( SumCursor ) = 0 ) THEN
     --Add a new Summary total for the account
     INSERT INTO SumCursor ( YA, Period, IsActual, AccountID, Amount )
VALUES ( Year, Period, TRUE, AccountID, ATotal );
   ELSE
     --Update the existing total
       UPDATE SumCursor SET Amount = ATotal;
   END IF;
 BEGIN
   COMMIT;
 EXCEPTION
   ROLLBACK;
 END;
END

Trevor


Glad you found your bug. Trigger or ... I presume the alternative was to call the procedure from within your app.

Where practicable I'd go for the trigger. That way even if (when) you have multiple points where data is entered it will be captured, without any additional action on your part.

Roy Lambert

Hello Roy,

Thanks for the reply.....I was wondering though....If I do it as a Trigger,
is it preferable to use the OLDROW & NEWROW and just pass in the difference
that we want to apply....or will my method work just as well.  I am
wondering whether there would be an impact on the database if the data
starts getting too big.

The advantage that I can see to doing it this way, is that if things ever
got out of whack for some reason, then it would only take one call to this
procedure to correct the problem. Any comments?

Regards
Trevor Keegan

Trevor

>Thanks for the reply.....I was wondering though....If I do it as a Trigger,
>is it preferable to use the OLDROW & NEWROW and just pass in the difference
>that we want to apply....or will my method work just as well. I am
>wondering whether there would be an impact on the database if the data
>starts getting too big.

I've just had another look at the documentation and I can't see anyway of passing anything into a trigger. But I would keep table manipulation down as far as practicable. I'm guessing that you can pick up the year, period and account ID from the transaction you're triggering off (if not its a weird system).

>The advantage that I can see to doing it this way, is that if things ever
>got out of whack for some reason, then it would only take one call to this
>procedure to correct the problem. Any comments?

If that happens its probably symptomatic of something nasty. My normal approach is to have a suite of repair / sort it out routines separate from normal processing if only because I generally want to do a few more integrity checks.

Roy Lambert [Team Elevate]

Hello Roy,

> I've just had another look at the documentation and I can't see anyway of
> passing anything into a trigger. But I would keep table manipulation down
> as far as practicable. I'm guessing that you can pick up the year, period
> and account ID from the transaction you're triggering off (if not its a
> weird system).
>
Yes, that is no problem...I am currently calling the procedure directly from
the trigger

>>The advantage that I can see to doing it this way, is that if things ever
>>got out of whack for some reason, then it would only take one call to this
>>procedure to correct the problem. Any comments?
>
> If that happens its probably symptomatic of something nasty. My normal
> approach is to have a suite of repair / sort it out routines separate from
> normal processing if only because I generally want to do a few more
> integrity checks.
Yes, I think that you are right.  Thanks for the advise, I will look doing
it this way

Regards
Trevor Keegan