How to create a derived attribute from a foreign table using Alter Table?

I can't create a derived column in the CREATE TABLE Command this time. I tried with the Alter Table but found the following error:

"Subqueries are not allowed in this context. Only scalar expressions are allowed."

My database:

CREATE TABLE Bem (
   ID INT PRIMARY KEY IDENTITY(1,1),
   PRECO INT,
   NOME VARCHAR(128),
   CATEGORIA VARCHAR(128)
);
 

CREATE TABLE Orcamento (
   ID INT PRIMARY KEY IDENTITY(1,1),
   Nome VARCHAR(128),
   Valor INT
);

CREATE TABLE Inventario (
  ID_Bem INT FOREIGN KEY REFERENCES Bem(ID),
  ID_Orc INT FOREIGN KEY REFERENCES Orcamento(ID),
  PRIMARY KEY (ID_Bem,ID_Orc)
);

I want Orcamento(Valor) to be a derived column, a Sum of Bem(Preco) for every item found on the Inventorio table that matches the Orcamento's ID.

This is what i tried:

ALTER TABLE Orcamento
  ADD ValorT
     AS
     (SELECT SUM(Bem.Preco)
         FROM Bem, Inventario
         WHERE Bem.ID = Inventario.ID_Bem 
            AND Inventario.ID_Orc = Orcamento.ID
     )
;

Using UPDATE worked, but it'd have to run this UPDATE every time i added data to the Inventario table. I'm wondering for better alternatives

Solution

Running it manually every time you want to update the column can be arduous. Would make sense to create a scalar UDF that populates this, something like the below, bit of a workaround.

Scalar UDF

CREATE FUNCTION dbo.GetOrcamentoValor (@OrcamentoID INT)  
RETURNS INT  
AS  
BEGIN  
DECLARE @Valor INT;  
SELECT @Valor = SUM(Bem.Preco)  
FROM Bem  
JOIN Inventario ON Bem.ID = Inventario.ID_Bem  
WHERE Inventario.ID_Orc = @OrcamentoID;  
RETURN ISNULL(@Valor, 0);  
END;

After this, change the Orcamento table to add the computed column using the UDF.

ALTER TABLE Orcamento  
ADD Valor AS dbo.GetOrcamentoValor(ID);

Whenever you insert or update a row in the Orcamento table, the Valor column will be automatically calculated using the logic defined in the scalar UDF.