Louis Davidson
The view is easy:
create view ordersWithTotal
as
select orders.orderId,
--order.columns,
sum(od.unitPrice * od.quantity) as orderAmount
from orders
join [order details] od
on orders.orderId = od.orderId
group by orders.orderId
The trigger is far less trivial. You have to take into consideration that multiple rows can be updated, so you do something like:
use northwind
go
alter table orders add orderTotal numeric(12,2) null
go
update orders
set orderTotal = orderTotal.orderAmount
from orders
join (select orders.orderId,
sum(od.unitPrice * od.quantity) as orderAmount
from orders
join [order details] od
on orders.orderId = od.orderId
where orders.orderId in (select orderId from inserted)
group by orders.orderId) as orderTotal
on orders.orderId = orderTotal.orderID
go
The bold bit is the most important part of the query as it constrains the rows updated to only those that are modified. You would create an insert and update trigger to make this work.