CODEWITHSUNDEEP
sqlsql-server
Iatrochemist
Iatrochemist

Reputation: 276

computing a column value using columns from two tables using trigger

i want to create a trigger to compute the 'totalPrice' column in the following table here

 create table materialPriceAndStandard (
 id int identity(700,3),
 materialName nvarchar(100) not null default (0),
 unitPrice decimal(19,2) not null default (0),
 carbohydrate tinyint not null default (0),
 protein tinyint not null default (0),
 fat tinyint not null default (0),
 humidity tinyint not null default (0) ,
 minerals tinyint not null default (0),
 totalPrice decimal(19,2) default(0),
 constraint PK_id_materialPriceAndStandard primary key (id),
 constraint UQ_materialName_materialPriceAndStandard unique (materialName), 
 constraint CHECK_totlaMineralAmount check
 (carbohydrate + protein + fat + humidity + minerals  =100 ))

this is the trigger : 

create trigger totalPrice 
ON materialPriceAndStandard
AFTER insert
as 
begin 
MaterialPriceAndStandard.totalPrice =
((select _weight from ingredients where
ingredients.material = 
materialPriceAndStandard.materialName * materialPriceAndStandard.unitPrice))
end

but i get the syntax error , how should i write the trigger to compute the value of 'totalPrice' using the '_weight' column from ingredients table.

totalPrice = _weight * unitPrice. _weight belongs to ingredients table unitPrice belongs to materialPriceAndStandard

Upvotes: 1

Views: 62

Answers (1)

marc_s
marc_s

Reputation: 755023

You need to use proper T-SQL to handle your updating. Try something like this:

CREATE TRIGGER totalPrice 
ON materialPriceAndStandard
AFTER insert
AS BEGIN
    UPDATE mp
    SET mp.totalPrice = SUM(ing._weight) * mp.unitPrice
    FROM dbo.MaterialPriceAndStandard mp
    INNER JOIN Inserted i ON mp.ID = i.ID   -- make sure to update *only* those new rows!
    INNER JOIN dbo.Ingredients ing ON mp.ID = ing.ID
END

Also: your current code is very unclear (to me) on how to join the two tables, and how to calculate the new TotalPrice (the price is the sum of your _weight?!?!?!? Seems very very odd.....) - you might need to adapt this.

Also: I would probably not do this in a trigger - what happens if something gets updated? What happens in the _weight column of dbo.Ingredients changes? Wouldn't you have to update the TotalPrice column, too? Why don't you just calculate this TotalPrice when you really need it - e.g. in a view or on a reporting tool.

Upvotes: 1

Related Questions