Is it possible to "automatically" set the value of a column to the result of a query?
For example I would like to have a column e.g. balance which when I request it, it should return the running total of some transactions but I prefer if that would have been pre-calculated.
Can I somehow "link" a column to the result of a View or a Function?
PS. We use MySQL Cluster
The query looks like the following:
SET @id = 'customerid';
SET @accId = 'accid';
Select sum(trans.t) - sum(trans.c) as Balance
from ( select *,
case when ACNO2=@accId then a1 * 1 else a1 * -1 end as t,
case when (TrCode= '40' and ACNO2=@accId) then 0 else ChargeAmount1 end as c
where (((CLNO1=@id and CLNO2=@id) or (CLNO1=@id or CLNO2=@id)) and TStatus='OK')) as trans
where (ACNO1=@accId or ACNO2=@accId)
http://dev.mysql.com/doc/refman/5.7/en/create-table.html says in part:
The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP or DATETIME column.
So you can't set use the
DEFAULT syntax to define a column as the result of a subquery.
As an alternative, you could create triggers on the transactions table you want to monitor, so that any change to the transactions updates your balance column.
You should create a trigger for each action: insert, delete, and update.
CREATE TRIGGER trx_ins_balance ON transactions AFTER INSERT FOR EACH ROW UPDATE accounts SET accounts.balance = accounts.balance + NEW.amount WHERE accounts.accountid = NEW.accountid; CREATE TRIGGER trx_del_balance ON transactions AFTER DELETE FOR EACH ROW UPDATE accounts SET accounts.balance = accounts.balance - OLD.amount WHERE accounts.accountid = NEW.accountid; CREATE TRIGGER trx_upd_balance ON transactions AFTER UPDATE FOR EACH ROW UPDATE accounts SET accounts.balance = accounts.balance - OLD.amount + NEW.amount WHERE accounts.accountid = NEW.accountid;
The above is just an example. Don't ask me for a more tailored example, because you shouldn't be using code verbatim from Stack Overflow anyway. You should understand how to write your own triggers if you're going to use them.
Another solution is to create a VIEW so that your balance column is actually an expression to read the transactions table. But this won't be pre-calculated. It will re-query the transactions every time you query the view, which might be an expensive solution.