Search code examples
sqldb2ibm-cloud

Formula / Calculation columns in a database table

I dno't think this is possible or that it makes sense, but most people here are much smarter then me and it never hurts to ask. Is it possible to create a table in a database that is a formula. I don't mean a query. I mean let's say I have a table and one of the columns is "price" another one is "quantity" and I would like a column "cost" to calculate (price * quantity) .... as the data gets imported. So that later I can just:

select * from table;

If you tell me this is a stupid question I'll understand but I got it from an internal client and need to investigate it. The db I am running with is DB2 on BlueMix.

Solution

  • You can set default value for cost column as

    ALTER TABLE table-name ALTER COLUMN cost set cost=price*quantity;

    Or you can create trigger as follows,

    db2 create trigger trigger_name after insert on table_name 
for each row mode db2sql begin atomic update tablle_name 
set cost=qty*price; end