Trigger

Question

hello team:

i have two tables order and orderdetial, i want from the orderdetail the sum of the quantity of products  for each order and then insert this sum in order table infront of each order

this is the first part:

declare @s decimal
select @s=sum(f.quantity) from orderdetails as f
inner join orders as a
on a.orders= f.orders
group by f.orderdetails

any one have advise, thanks lot

Answer 1

Hassano,

It is considered a very bad idea to store data that can be calculated from other data. It becomes 'easy' to have the data desyncronized. Your idea of using a TRIGGER may help offset that concern -but at a significant performance cost.

The use of a TRIGGER on the [Order Details] table is not the best idea since the TRIGGER code will have to execute every time there is a new row INSERT, a current row UPDATED, or even DELETED. It could cause a lot of unnecessary code execution. Imagine adding an order with ten rows in the [Order Details] table -the TRIGGER code would have to execute ten times.

A better solution would be to have a VIEW that joins the two tables, and calculates the total of the order. Then the code would only execute when you use the VIEW.

If you are insistent upon using a TRIGGER, let us know and we will help you with it -even thought it is not a good idea.

Answer 2

I realy thank you very much , if you mind to help me to wright the code of the view

Thanks lot

Answer 3

I realy thank you very much , if you mind to help me to wright the code of the view

The point is that i want the user to enter values in order detail with out seeing the total in the same form .

Thanks lot

Answer 4

In order to help you with the VIEW, we need to know the table definitions. What are the columns and datatypes.

(Or, by chance, are you using the Northwind database as an example?)

Answer 5

i am not using the northwind but i found that the nearst example for my case is order and orderdetails to not wright many details to be easier to understand   

for ex:

The order table  pk : orderid , orderdate, ordername ,totalquantity  -------  of products 

     orderdetails   fk   : orderid , prodcutname , quantity

can you learn me the two ways if you mind the trigger and the view , thank you very much

is it better to make another table like a 'store' to save the totals

Thanks lot

Answer 6

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. 

 

 

Answer 7

Re ask :

   my question is :

   i have master and detail tables , the pk of the master used many times as fk in the detail table, i want to make for ex a trigger to get the sum for a feild of the fk then insert this sum in the master table in feild total

like productid and store for example

 

how can i do that VIP

thank you very much

Answer 8

Hello Team no answer for the re ask

Answer 9

On the Trigger (Insert trigger on OrderDetails)..

Update Ordermaster

Set

 Total = TotalValue

From

  (Select Sum(OrderValue) as [TotalValue] From OrderDetails Where OrderDetails.OrderId = New.OrderId) as Data

Where

  Ordermaster.OrderId = New.OrderId

Answer 10

Thank you very much the view is accuret Thanks lot

about the trigger i hava a question

what is  'set    orderTotal = orderTotal.orderAmount '    <-- i couldnt understand it

Thank you ver much

Answer 11

Look at the join for the derived table (bold)

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

This derived table is where I calculated the orderAmount, and the derived table is named orderTotal (in italics).  Derived tables are really great for doing this sort of thing