Declare a table has a column that is the product of two columns

Question

Table Product

id  name   price   quantity   total

1     food    50      1         50
2     drink   20      2         40
3     dress   100     3         300

How do I declare a table that has a column that is the product of two columns?

I have this code:

CREATE TABLE [dbo].[Orders] (
    [Id]          INT        IDENTITY (1, 1) NOT NULL,
    [ProductName] NCHAR (70) NULL,
    [Price]       INT        NULL,
    [Quantity]    INT        NULL,
    [Total] INT NULL, 
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Do you really want a field to store the `total` -- perhaps that would be more appropriate to calculate in your sql or in a `view`? If you do want a `computed column`, declare it like such: `[Total] INT AS (Price * Quantity)` — sgeddes, Feb 27 '15 at 00:12
`[Total] INT AS (Price * Quantity),` gives me error in the use of AS in T-SQL in C# — nAPL, Feb 27 '15 at 00:28
Looks like it's not supported: http://stackoverflow.com/questions/5422729/mysql-computed-column — JohnLBevan, Feb 27 '15 at 00:37
Maybe you want a separate table for storing the tables of multiplication? — wildplasser, Feb 27 '15 at 00:41
No, i have in this table. And dont work this code `ALTER TABLE [Orders] ADD TOT INT AS Price * Quantity;` :( — nAPL, Feb 27 '15 at 00:46

score 2 · Answer 1 · answered Feb 27 '15 at 00:47

Sounds like you want a VIEW.

Their example is exactly what you're describing

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

Avidos · Answer 2 · 2015-02-27T01:11:39.977

You can try this mate:

DROP TRIGGER IF EXISTS trg_product_total;
DELIMITER //
CREATE TRIGGER trg_product_total AFTER INSERT ON product
FOR EACH ROW BEGIN
  SET @price = NULL, @quantity = NULL;  

  SELECT price INTO @price FROM product
  WHERE id = NEW.id;  

  SELECT quantity INTO @quantity
  WHERE id = NEW.id;  

  UPDATE product SET total = @price * @quantity
  WHERE id = NEW.id;
END;

You can use this kind of approach if you don't really want to process the product.total before inserting it into the DB.

The Trigger will execute each time a new record is added into the table, wherein the expected insert for the total column is either 'NULL' or '0' depending on your default value.

But I think it would be better if you calculate it before the insert.

The flow would be like:

Application side
1. get price and quantity for the product
2. calculate for the total
3. insert values into the query
4. execute query

In case you want to learn more about MySQL Trigger: Link
Also, PHP Transaction: Link

Declare a table has a column that is the product of two columns

2 Answers2