Hi Vishy,

Use triggers to enforce database consistency. In fact even check
constraints and foreign key constraints are essentially triggers.

> 1. Say A sale transaction takes place,which is inserted into
> transaction table..now this affects account balance of customer which
> is stored in another table..
> should trigger be used to update customer balance on insert of
> transaction table..

Yes, this is a good example of where triggers are useful. But just be
aware of a couple of things.

If a customer can have two different transactions running at the same
time, the second transaction will be effectively locked out of
updating the customer balance while the first one completes because
there will be a lock on the customers balance record.

This may or may not be an issue in your environment.

If it is an issue, then create your customer balance table like this.

CustomerBalance
(
ID,
CustomerID,
Balance
)

Now you add a trigger to your insert trigger on the transaction table
that looks something like this.

insert into CustomerBalance(ID, CustomerID, Balance) values
(gen_id(gen_CustomerBalanceID,1), new.CustomerID, new.Balance);

The update trigger would look something like

insert into CustomerBalance(ID, CustomerID, Balance) values
(gen_id(gen_CustomerBalanceID,1), new.CustomerID,
(new.Balance-old.Balance));

The same for the delete trigger, except you add a negative balance

insert into CustomerBalance(ID, CustomerID, Balance) values
(gen_id(gen_CustomerBalanceID,1), new.CustomerID, (-1 * old.Balance));

So now you are thinking, well that is great, what has been achieved by
that. Well because this is all inserting data rather than modifying
records, the transactions will not block each other from running.

Now create a stored procedure that does this.

(> added to keep the formatting)

> create procedure ReCalcBalances
> as
> declare variable customerid bigint;
> declare variable balance numeric(18,4);
> begin
> for select customerid, sum(Balance)
> from CustomerBalance
> group by customerid
> into :customerid, :balance
> do
> begin
> delete from CustomerBalance
> where CustomerID = :CustomerID;
> insert into CustomerBalance(ID, CustomerID, Balance) values
> (gen_id(gen_CustomerBalanceID,1), :CustomerID, :Balance);
> end;
> end;

You could run this stored procedure overnight in a program in the
schedule.

To get a customers balance at any time,

select sum(Balance)
from CustomerBalance
where CustomerID = 1

Now the more frequently you run the stored procedure, the less records
you will have to sum. If there are a LOT of transactions for each
customer, then you can run this even more frequently.

This solution is a small adaption to the select count(*) performance
work around. It is totally safe to do and doesn't stop transactions
being recorded while it is calculating.

I am sure without too much effort you could include a "having" line to
the procedure so it only does the customers with more than say 10
records in the balance table which should speed things up dramatically.

Adam