Create a column from 2 columns of 2 different tables

CREATE TABLE OrderLine (
    OrderID int NOT NULL FOREIGN KEY REFERENCES Orders(OrderID),
    RewardID int NOT NULL FOREIGN KEY REFERENCES Rewards(RewardID),
    LineTotal int,
    Quantity int
)

CREATE TABLE Rewards (
    RewardID int NOT NULL PRIMARY KEY IDENTITY,
    RewardName varchar(50),
    RewardDesc varchar(255),
    Price int
)

I have these 2 tables. What I want is that LineTotal is equal to Quantity*Price. I know that if I have Price in my OrderLine I could do LineTotal as ([quantity]*[price]). But that’s not what I want. I also read something about triggers but it wouldn’t work due to errors. What I tried

CREATE TRIGGER linetotal_insert
ON OrderLine   
FOR INSERT OrderLine.LineTotal = orderLine.Quantity*Rewards.Price

I use SQL Server 2018

Answer

Here is an example for a Trigger – however, this might fir the current requirement, but you should get familiar with further possibilities of SQL Triggers (such as handling inserts and updates differently in case quantity in OrderLine changes or even in case the price in Rewards changes):

CREATE TABLE OrderLine (
    OrderID int NOT NULL ,
    RewardID int NOT NULL,
    LineTotal int,
    Quantity int
)
GO

CREATE TABLE Rewards (
      RewardID int NOT NULL,
    RewardName varchar(50),
    RewardDesc varchar(255),
    Price int
)
GO

CREATE TRIGGER T1
ON OrderLine
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO OrderLine
  SELECT i.OrderID, i.RewardID, i.Quantity * ISNULL(r.Price, 0), i.Quantity
    FROM inserted i
    LEFT JOIN Rewards AS r ON r.RewardID = i.RewardID
END
GO

CREATE TRIGGER T2
ON OrderLine
INSTEAD OF UPDATE
AS
BEGIN
  IF(UPDATE(Quantity)) 
  BEGIN
    UPDATE o
      SET LineTotal = ISNULL(r.price, 0) * i.quantity, o.Quantity = i.Quantity
      FROM inserted i
      JOIN Orderline o ON o.OrderID = i.OrderID
      LEFT JOIN Rewards r ON r.RewardID = i.RewardID
  END
END
GO

Fiddle: http://sqlfiddle.com/#!18/0a4a9/1/4