The declare table has a column that is a product of two columns

Question

Table

id name price quantity total 1 food 50 1 50 2 drink 20 2 40 3 dress 100 3 300 

How to declare a table with a column that is a 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) ); 

Answer 1

Looks like you want a VIEW .

Their example is exactly what you describe

 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 | +------+-------+-------+ 

Answer 2

You can try this partner:

 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; 

This approach can be used if you really do not want to process the product. Before embedding it in the database.

The trigger will be executed every time a new record is added to the table, where the expected insertion for the common column will be either "NULL" or "0" depending on your default value.

But I think it would be better if you calculated it before inserting.

The stream will look like this:

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

If you want to know more about MySQL Trigger: Link
Also, PHP Transaction: Link