Link to home
Start Free TrialLog in
Avatar of emi_sastra
emi_sastra

asked on

Trigger to sum data..

Hi,

I have 2 tables like below:

I. Detail Stock:

1. ItemCode.
2. TrsDate.     (This field could have more than one in the same ItemCode).
3. ItemQty.
4. ItemUsed

II. Total Stock.

1. ItemCode.
2. ItemQty (ItemQty-ItemUsed)

How to write trigger to update Total Stock table if there is a change (add, update or delete) in Detail Stock table?

Thank you.

SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Angel III's code will not work for updates of item codes that already exist in the Detail Stock table.  He is just adding in the (ItemQty - ItemUsed) for each record.  If, however, the ItemQty field or the ItemUsed field (or both) is being updated, then Angel III's query does not allow for the previous values of those fields.  He just adds in the entire value again.

If you use his code, it will appear to work, but your TotalStock table will gradually get out of sync with the Detail Stock table.

My code allows for all of these "Use Cases".  Please give me the points.  I spent more time, I invested more thought, and my code will work for all cases.  I deserve the nod.

Thank you.
>Angel III's code will not work for updates

yes, it will. the code does:
               + ISNULL( (SELECT SUM(ItemQty-ItemUsed) FROM inserted i WHERE i.ItemCode = ts.ItemCode ), 0)
                - ISNULL( (SELECT SUM(ItemQty-ItemUsed) FROM deleted d WHERE d.ItemCode = ts.ItemCode ), 0)

which is handling the UPDATE statement as well.


now:
>Please give me the points.  
why are "requesting" the points? I don't claim that your code does not work, but you claim that mine does not.
why not just leave that decision to the asker, following the EE rules?

let's wait on his feedback.

angel eyes :)
It will add a number, but it will be the WRONG number if the Detail Stock record was updated.  The way the tables are set up, you can see that the Detail Stock records are meant to be updated as stock levels change.

Part of the problem is that the table design is not great, but that is pretty typical.  I see it a lot even in production systems.  A sub-optimal database design always makes the application code more complex.

It isn't personal when I say that your code does not allow for a certain set of circumstances.  I think that is part of working toward a correct solution.

Anyway, it was not my intention to start a flame war, and as far as I am concerned, there is no conflict here.  I have taken a good two hours at this point analyzing the problem, because it is not a simple problem, and I am sure that is why it has sat here for four days with no answer from anyone.

I have seen your posts a lot here on the site, and I have a great deal of respect for you.  I do think, hoever, that I add something different to the site because I always work to make my code as simple and readable as possible, and I like to take the time to explain my solutions.

I'll probably never make your point total, but I give good advice.  So, I do think I deserve the points and I am not afraid to say so.

Have a nice day, and it was nice talking with you.
I take it as a challenge to prove that my code DOES work (although I have to admit that I did not test it, but wrote the code directly here).
Please see below test script, followed by the output data which is correct as far as I can see, for all operations. if you can see a flaw, ie a possible configuration where is won't work, I will check that out.

anyhow:

>Part of the problem is that the table design is not great, but that is pretty typical.  I see it a lot even in production systems.  A sub-optimal database design always makes the application code more complex.

I do agree on that!

>Anyway, it was not my intention to start a flame war, and as far as I am concerned, there is no conflict here.
same here.


>I have taken a good two hours at this point analyzing the problem, because it is not a simple problem, and I am sure that is why it has sat here for four days with no answer from anyone.

now, there is a difference, because I only saw the question 10 minutes before I posted. I do not actually consider it a difficult problem, because I have seen similar, mainly on EE :)


> I do think, hoever, that I add something different to the site because I always work to make my code as simple and readable as possible, and I like to take the time to explain my solutions.
While I try to do so also, I do post thorough explanations only when asked. I hate to post lots of work/text when the asker might not come back. if the asker takes the time to give feedback and ask about the "why is that so, or why does it have to be like that", I am just happy to give the explanations.

as you can see here, I also can take the time to test thoroughly, once needed or asked for, resp when the asker (or like here) claims my suggestion does not work.


>I'll probably never make your point total,
don't care about the points. although it's "all" you get from EE (apart some nice "thank you" from some askers), it's not a grade indication of "knowledge", but only a indication of participation.

>but I give good advice.  
that's what I try to do also. hope you continue doing so!!



create table Detail_Stock (itemcode int, trsdate datetime, itemqty int, itemused int )
create table Total_Stock ( itemcode int, itemqty int)
go
CREATE TRIGGER trg_update_stock
  ON Detail_Stock
 FOR INSERT, UPDATE, DELETE
AS
 -- ensure the total_stock contains all ItemCodes
 INSERT INTO Total_Stock ( ItemCode, ItemQty)
  SELECT i.ItemCode , 0 
    FROM inserted I 
   WHERE NOT EXISTS ( SELECT NULL FROM Total_Stock x where i.ItemCode = x.ItemCode ) 
  GROUP BY i.ItemCode
 
 -- update the qty values, subtract the "old" values, add the new values. 
 UPDATE ts
    SET ItemQty = ts.ItemQty 
                + ISNULL( (SELECT SUM(ItemQty-ItemUsed) FROM inserted i WHERE i.ItemCode = ts.ItemCode ), 0)
                - ISNULL( (SELECT SUM(ItemQty-ItemUsed) FROM deleted d WHERE d.ItemCode = ts.ItemCode ), 0)
 
  FROM Total_Stock ts
   WHERE ts.ItemCode in ( SELECT ItemCode FROM INSERTED )
      OR ts.ItemCode in ( SELECT ItemCode FROM DELETED )
 
go
 
insert into detail_stock select 1, getdate(), 10, 5
 
insert into detail_stock select 1, getdate(), 7, 5
 
insert into detail_stock select 2, getdate(), 4, 2
 
insert into detail_stock select 3, getdate(), 4, 2
 
update detail_stock set itemused = itemqty where itemcode = 1 and itemqty = 7
 
delete detail_stock where itemcode = 3
 
go
 
select * from detail_stock
select * from total_stock 
 
 
go
drop table total_stock
go
drop table detail_stock
 
 
 
 
itemcode    trsdate                 itemqty     itemused
----------- ----------------------- ----------- -----------
1           2008-11-16 00:00:27.483 10          5
1           2008-11-16 00:00:27.687 7           7
2           2008-11-16 00:00:27.700 4           2
 
itemcode    itemqty
----------- -----------
1           5
2           2
3           0

Open in new window

Avatar of emi_sastra
emi_sastra

ASKER

Hi All,

I am sorry, just back.

I am very happy getting help from you very talented guys.

I haven't test it yet, but I am sure all of it works.

I need simple and work fast (perfomance) solution.

Through the above discussion I have several question about it:

1. Does your trigger (to angelIII) recomputing the entire table every time an update is made?
2. How to change my table structure to make it simple (to both of you)?  (I think may be this is out of the question, but any suggestion is a plus point).  

Thank you.



You know, a wise man once said to make your words sweet and light, because then it won't be so much of a problem  when you have to eat them! :-)

I've been looking through the code again, and Angel Eyes code will work in all cases, because he does a complete recompute of the balance for each item code updated.  My code computes the changes for each item code updated and then applies them to the total stock table.

There is a downside to both techniques.  Recomputing the entire balance for each item code updated, every time an item code is updated is not going to scale well.  I think that once you get past a few thousand records it is going to start to take an inordinate amount of processing power to add new records to the detail stock table.  You could also find yourself taking several seconds just to add records to the detail stock table, and this sounds like a table that would see a lot of activity.  Maybe you've got a beefy system, and it won't break down until you get to 100,000 or 1,000,000 records, but at some point it will become a serious problem.

The downside with my technique is that once the total stock table gets out of sync with the detail stock table (and it will, trust me), my code will never correct the error.  The total stock table will stay of sync.

The basic problem here is not the code, however, it is the design.  Design problems always end up manifesting themselves as overly complex code, and then ulitimately as maintenance problems.  If you have the opportunity (and you might not) I would rethink the design.
Hi SQLShark,

Ok. I will wait until Angel's reply about the recomputing.
After that I will assign point.

By the way I will open a new question regarding the table design above.
I hope you have time to solve it.

Thank you.
Hi SQLShark,

I don't know if it is going to break the rule of EE, but may I have your email address?

Thank you.
>I've been looking through the code again, and Angel Eyes code will work in all cases, because he does a complete recompute of the balance for each item code updated.  

honestly, you must be looking at someone else's code. my code just takes the delta by looking at the INSERTED and DELETE tables, and applies it to the existing row (per ItemCode) in the totals table (with update). an index on ItemCode on both tables will make sure that trigger code will be subsecond, and not taking seconds, even with millions of rows.
in that regards, yours and mine do NOT differ at all. however, I have been looking at your code, and you are missing 1 important detail: on the INSERT for the details table, it does NOT check if the itemcode is already in the totals table. my trigger does ensure the ItemCode is present for the UPDATE, by doing a conditional insert before. yours does not do that.

>I don't know if it is going to break the rule of EE, but may I have your email address?
it would, if it is to discuss a EE question.
if he has his email in his profile, and discussion non-EE questions, it's no problem at all.

If SQLSHARK posted a small bio - the e-mail address might appear there (hint)

Do you want me to get involved in this and if needed can post either an alternative, or, critique existing... Or should I just stay clear ?

Whilst waiting, so far, from my testing, the points would go to the more efficient code - in this case, would look at things like the efficient join back to inserted and deleted system tables, and the firing of / maintaining various triggers . Clearly, the code base that does most in the one spot without penalty of resource or efficiency is preferrential.

In that regard, I would have posted something more like AngelIII, but slightly different - would have checked the existance, then either insert or update.
In fact, take it back, would not waste the overhead of checking existance for the sake of the more common update existing. Would just insert then update regardless, and have a little bit of overhead on the occasional create new, given the added efficiency of the update existing.
In fact, I now remember why I this question open in my browser window in the first place (ie had it opened, and before I posted any response, this time did a refresh)...

in Stock Detail - if I consumed some of itemcode, then assume that consumed quantity is in itemused. What is decrementing the original itemqty ?

e.g.
a) item "a" qty = 100, used = 0     -- now "consume" 20
b) item "a" qty = 100, used = 20   -- or, is it
or
c) item "a" qty = 80, used = 20     -- now consume say another 10 based on b outcome

d) item "a" qty = 100, used = 30   -- or based on c outcome
or
e) item "a" qty = 70, used = 10

or say that qty represents the "consumed" quantity above of 20
f) item "a" qty = 20, used = 20

any of the above scenerios are kind of "double dipping" so that the definition of the "total" being qty = (qty - used) is not correct in any of the above.

if the "consumed" amount is in either the qty column, or the used column, then the only correct delta is inserted.consumed_column - deleted.consumed_column where consumed column could be either qty or used...

or have I totally lost the plot ?
Hi mark_wills,

ItemQty stay the same, it get from Stock In Transaction.
ItemUsed is updated from every Stock Out Transaction as long as it is smaller qty than ItemQty.
I use FIFO stock, or could be LIFO and Average Stock.

Thank you.
Hi SQLShark,

Angel comment about your code::

in that regards, yours and mine do NOT differ at all. however, I have been looking at your code, and you are missing 1 important detail: on the INSERT for the details table, it does NOT check if the itemcode is already in the totals table. my trigger does ensure the ItemCode is present for the UPDATE, by doing a conditional insert before. yours does not do that.

Could it be improved?

By the way, I don't see your email in your profile.

Thank you.
OK, still a couple of questions, and might be best solved using a "spreadsheet" type approach....

Would you mind filling in the blanks ? Let us assume a new product with no prior activity...





+Actual transactions description----------+  +Detailed Table--+  +Total Table+
|itemcode   |date       |type        |qty |  |itemqty|itemused|  |Total QTY  |
+-----------+-----------+------------+----+  +-------+--------+  +-----------+
 1          |2008-11-16 |InitialStock| 20 |  |       |        |  |           |
 1          |2008-11-16 |Sale (out)  | 20 |  |       |        |  |           |
 1          |2008-11-17 |Receipt/PO  | 15 |  |       |        |  |           |
 1          |2008-11-18 |Sale (out)  | 10 |  |       |        |  |           |
 1          |2008-11-18 |Sale (out)  |  5 |  |       |        |  |           |
 

Open in new window

Ok.

   Transaction   Detail Stock                Total Stock
                         ItemQty     ItemUsed
1. Initial Stock   20             0                20
2. Sales            20             20              0
3. Receipt         15             0                15
4. Sales            15             10              5
5. Sales            15              5               0

Thank you.  
      


 
Sorry, I miss something.

From the above transaction the Detail Stock just have 2 records which is point 1 and 3.

Thank you.
Again, I miss something, finally.

 Transaction   Detail Stock                Total Stock
                         ItemQty     ItemUsed
1. Initial Stock   20             0                20
2. Sales            20             20              0
3. Receipt         15             0                15
4. Sales            15             10              5
5. Sales            15             15              0

From the above transaction the Detail Stock just have 2 records which is point 1 and 3.

Thank you.
OK then all the above triggers are quite possibly wrong...

those transactions were happening on different days, so, are not always an update in place of the orignal row, meaning they are being added as new transactions. If we look at the definition of itemqty - itemused, then our "running total" becomes:

SOH = itmqty - itemused
20   = 20 - 0     = OK
0     =  20 - 20  = OK
15   = 15 - 0     = OK
5     = 15 - 10   = OK
10   = 15 - 5     = NOT OK

combine with adding to the current SOH figure as in the above triggers then we are a million miles out...



Timing problems with posts. Didn't see your "correcting" entry in line 5 at time of submitting my entry above...

So, how would another line, line 6. look if we did another Receipt the same as line 3 (ie receipt in another 15)?

And if detail Stock just has line 1 and 3 then how are sales decrementing the total ?
Sorry, please see my last previous post.

Thank you.
yep, me too....
So, how would another line, line 6. look if we did another Receipt the same as line 3 (ie receipt in another 15)?

And if detail Stock just has line 1 and 3 then how are sales decrementing the total ?

One simple word that Detail Stock just added where there is Receipt Transaction.
Updated ItemUsed when there is Sales (Stock Out) Transaction.

Thank you.
Aaahhhh, gotcha.... Not a detail table at all - a fifo status table drawing down (sales) against receipted goods.


/*
+Actual transactions description----------+  +Detailed Table--+  +Total Table+
|itemcode   |date       |type        |qty |  |itemqty|itemused|  |Total QTY  |   detail actions               Trigger actions                  SOH
+-----------+-----------+------------+----+  +-------+--------+  +-----------+   ---------------------------  -------------------------------  ---
 1          |2008-11-16 |InitialStock| 20 |  |     20|       0|  |        20 |   new detail row               add (20 - 0)                      20
 1          |2008-11-16 |Sale        | 20 |  |     20|      20|  |         0 |   update existing detail row   subtract (20 - 0) add (20 - 20)    0
 1          |2008-11-17 |Receipt/PO  | 15 |  |     15|       0|  |        15 |   new detail row               add (15 - 0)                      15
 1          |2008-11-18 |Sale        | 10 |  |     15|      10|  |         5 |   update existing detail row   subtract (15 - 0) add (15 - 10)    5
 1          |2008-11-18 |Sale        |  5 |  |     15|      15|  |         0 |   update existing detail row   subtract (15 -10) add (15 - 15)    0
*/ 
 
and the trigger would be :
 
ALTER TRIGGER trg_update_stock ON Detail_Stock
FOR INSERT, UPDATE, DELETE
AS
 
--
 
  INSERT INTO Total_Stock ( ItemCode, itemqty )
  select distinct newcode, 0 from
  (
  SELECT ItemCode as newcode from inserted union
  SELECT ItemCode from deleted
  ) new
  WHERE NOT EXISTS (SELECT NULL FROM Total_Stock where newcode = ItemCode ) 
 
--
 
  UPDATE Total_Stock
  SET ItemQty = total_stock.itemqty + ISNULL(i.ItemQty-i.ItemUsed, 0) - ISNULL( d.ItemQty-d.ItemUsed, 0)
  FROM Total_Stock
  LEFT OUTER JOIN INSERTED i on Total_Stock.itemcode = i.itemcode
  LEFT OUTER JOIN DELETED d on Total_Stock.itemcode = d.itemcode
  
--  
go

Open in new window

Reply to earlier post from Emi Sastra:

     <Hi SQLShark,
     <
     <Angel comment about your code::
     <
     <in that regards, yours and mine do NOT differ at all. however, I have been looking at your code,
     <and you are missing 1 important detail: on the INSERT for the details table, it does NOT check if
     <the itemcode is already in the totals table. my trigger does ensure the ItemCode is present for
     <the UPDATE, by doing a conditional insert before. yours does not do that.
     <
     <Could it be improved?
     <
     <By the way, I don't see your email in your profile.
     <
     <Thank you.
     <
============================================

I have now updated my profile.

Angel Eyes code does a recompute of the stock count for a specific item code whenever a detail stock record for that item code is updated.  It does not do a complete recompute of the entire total_stock table every time an update is done.  My code is more targeted in that it tries to compute the deltas for each update, but again, Angel Eyes code will sync up the total stock records (again for a specific item code) when it runs whereas mine would not.

In my opinion, recomputing the item totals for a given item code will become resource intensive when the detail stock table gets very large.  How large?  That would depend on your hardware configuration.  Unless you have great hardware, however, I would expect problems to crop up by the time you got to 10,000,000 records in the detail stock table.  

My code computes the deltas for each update and applies those, but the total stock table will inevitably get out of sync no matter how careful you are.  So, you are d&^$*d if you do, and d&%*^&d if you don't.  (Pardon my language.)

Again, the real source of the problems here are the table design.

P.S.  My code does check for new item codes.  That is why I have a separate trigger for INSERTS.
Well, you certainly have a lot of expert input to help you solve your problem !  :-)
Yes, not so detail.

Could you explain (add explanation) the trigger do?
What's the different among your code (SQLShart, Angelll and you) ?
Does your trigger recomputed each of the itemCode every time?

Thank you.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi angelIII,

Your comment :

     <in that regards, yours and mine do NOT differ at all. however, I have been looking at your code,
     <and you are missing 1 important detail: on the INSERT for the details table, it does NOT check if
     <the itemcode is already in the totals table. my trigger does ensure the ItemCode is present for
     <the UPDATE, by doing a conditional insert before. yours does not do that.

SQLShark comment:

P.S.  My code does check for new item codes.  That is why I have a separate trigger for INSERTS.

What do you think?

Thank you.
I agree with Mark Wills that we have now beaten this subject to death.

I will only add the advice that you should set up a test system to test each of the triggers and throw lots of data from your production system at it.

Then check your results.

Happy computing !  :-)
Hi SQLShark,

I have problem to send you email.

<david.moutray@hotmail.com>:
65.54.245.72 does not like recipient.
Remote host said: 550 Requested action not taken: mailbox unavailable
Giving up on 65.54.245.72.

Thank you.
Hi SQLShark,

Which trigger command is deleted row from Total Stock Table if the ItemCode row is deleted from Detail Stock Table?

Thank you.
I've updated my profile.  I make that mistake all the time.
@SQLShark: your bio tag lines look familiar - is that an assist ?
WELLLLL, I did take a leaf from your tree.  I felt that you did a good job of expressing something that I, too, feel.

I hope this is not a problem.
Not at all, flattered in fact. Cheers... Mark Wills
Hi SQLShark,

WELLLLL, I did take a leaf from your tree.  I felt that you did a good job of expressing something that I, too, feel. I hope this is not a problem.

Is this comment for me?  I don't understand what do you mean all.

Thank you.


that was for markwills. if you read their profiles, you will see that SQLShark reused an expression of mark_wills profile.

now:
>Which trigger command is deleted row from Total Stock Table if the ItemCode row is deleted from Detail Stock Table?

actually, in the suggestions the total_stock row will never be deleted, but the itemqty bet set to 0.
Hi angelIII,

actually, in the suggestions the total_stock row will never be deleted, but the itemqty bet set to 0.
This is good or not?

Your comment :

     <in that regards, yours and mine do NOT differ at all. however, I have been looking at your code,
     <and you are missing 1 important detail: on the INSERT for the details table, it does NOT check if
     <the itemcode is already in the totals table. my trigger does ensure the ItemCode is present for
     <the UPDATE, by doing a conditional insert before. yours does not do that.

SQLShark comment:

P.S.  My code does check for new item codes.  That is why I have a separate trigger for INSERTS.

What do you think?

Thank you.
>actually, in the suggestions the total_stock row will never be deleted, but the itemqty bet set to 0.
>This is good or not?

that depends on your needs. actually, you have to know that when you delete 1 row in Details_Stock, it will change the balance. only when ALL the rows in Details_stock of the same item_code would be deleted, I think you could eventually delete the Item_Code from the Totals table also, but I think you should not.

>SQLShark comment:
>P.S.  My code does check for new item codes.  That is why I have a separate trigger for INSERTS.
actually, it does have a separate trigger for the inserts, that's right. and it does insert items codes, but unconditionally. so on the second insert of a row in details row for the same item_code, his trigger will reinsert a second row in totals table also, as it does NOT check for the existance of the ItemCode value in the totals table.

I modified his INSERT trigger below to show what was missing.

--Trigger #1:  Fires after an insert to Detail Stock table
CREATE TRIGGER [trg_InsertDetailStock]
ON [Detail Stock]
AFTER INSERT
AS
--Insert of new record for existing Item Code
UPDATE [TotalStock]
SET        [ItemQty] = [TotalStock].[ItemQty] + [SumInsert].[ItemTotal]
FROM    [TotalStock]
JOIN    ( SELECT       [ItemCode],
                                 SUM([ItemQty] - [ItemUsed]) AS [ItemTotal]
              FROM          Inserted
              GROUP BY  [ItemCode] )  AS [SumInsert]
  ON       [TotalStock].[ItemCode] = [SumInsert].[ItemCode]
 
--Insert of New Item Code
INSERT        [TotalStock] ([ItemCode], [ItemQty])
SELECT       [ItemCode],
                   SUM([ItemQty] - [ItemUsed])
FROM          Inserted
--- this line is missing ---
WHERE NOT EXISTS ( select null from   [TotalStock] WHERE   [TotalStock].ItemCode = INSERTED.ItemCode )
 
GROUP BY  [ItemCode]
 
 
 
;

Open in new window

Hi angelIII,

that depends on your needs. actually, you have to know that when you delete 1 row in Details_Stock, it will change the balance. only when ALL the rows in Details_stock of the same item_code would be deleted, I think you could eventually delete the Item_Code from the Totals table also, but I think you should not.
Yes, I think so.

I modified his INSERT trigger below to show what was missing.
Ok, let's wait SQLShark comment about it.

Thank you.



Oh, I think we've beaten it to death.  We are way over 40 comments on this issue.  If we haven't covered everything by now, I don't think we ever will !

I will happily share the points with Angel Eyes & Mark Wills.  They both made great comments and gave good solutions.  (Of course, I think that I did, too!)

:-)
Hi All,

I really appreciates getting help from all of you.
Your approach in solving this problem is quite different but works.
Combine all of them will make a perfect query for this solution.
Mark did not use SUM in his query?

I think all of you deserve the point.
I hope I share the point fair enough.

Thank you very much again for all of your help.