Hi Adam,
thks for such a lenghty explanation..
Yu said..Use triggers to enforce database consistency..

Now,from application point of view..all business objects validatae
data as well as enforce business rules..
Our database team says triggers need to be used since if someone
(support team for the software) access a database directly and
changes data in the table directly..then trigger should be fired to
enforce other changes that should take place..i think this is
wrong..why should anyone be allowed such an access...or is this an
industry practice...maybe provide views through which changes can
take place..
vishy

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@y...> wrote:
> 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