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.

LVL 1
emi_sastraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
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 )
 
 

Open in new window

0
SQLSharkCommented:
To avoid recomputing the entire table every time an update is made, you will need THREE triggers on the Detail Stock table

--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
GROUP BY  [ItemCode]
;

--Trigger #2:  Fires after a delete from Detail Stock table
CREATE TRIGGER [trg_DeleteDetailStock]
ON [DetailStock]
AFTER DELETE
AS
UPDATE [TotalStock]
SET        [ItemQty] = [TotalStock].[ItemQty] - [SumDelete].[ItemTotal]
FROM    [TotalStock]
JOIN    ( SELECT       [ItemCode],
                                 SUM([ItemQty] - [ItemUsed]) AS [ItemTotal]
              FROM          Deleted
              GROUP BY  [ItemCode] )  AS [SumDelete]
  ON       [TotalStock].[ItemCode] = [SumDelete].[ItemCode]
;

--Trigger #3:  Fires after an update to the Detail Stock table
CREATE TRIGGER [trg_UpdateDetailStock]
ON [DetailStock]
AFTER UPDATE
AS
UPDATE [TotalStock]
SET        [ItemQty] = [TotalStock].[ItemQty]
                             + [DetailChange].[ItemQtyChange]
                             - [DetailedChange].[ItemUsedChange]
FROM    [TotalStock]
JOIN    ( SELECT        Inserted.ItemCode,
                                  SUM(Inserted.ItemQty - [DetailStock].[ItemQty])           AS [ItemQtyChange],
                                  SUM(Inserted.ItemUsed - [DetailedStock].[ItemUsed])  AS [ItemUsedChange]
               FROM           [DetailStock]
               JOIN             Inserted
                  ON            [DetailStock].[ItemCode]  = Inserted.ItemCode
                 AND           [DetailedStock].[TrsDate] = Inserted.TrsDate
               GROUP BY  Inserted.ItemCode )                                    AS [DetailChange]
    ON      [TotalStock].[ItemCode] = [DetailChange].[ItemCode]
;

OK.  That's it.  I just spent 90 minutes working on this code, which is probably why no one else responded in the last four days.  So, why don't you just be very nice to the very nice developer who just helped you out and give the points with an A rating.  :-)

Thank you.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQLSharkCommented:
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.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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 :)
0
SQLSharkCommented:
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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
emi_sastraAuthor Commented:
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.



0
SQLSharkCommented:
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.
0
emi_sastraAuthor Commented:
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.
0
emi_sastraAuthor Commented:
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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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.

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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.

0
Mark WillsTopic AdvisorCommented:
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.
0
Mark WillsTopic AdvisorCommented:
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.
0
Mark WillsTopic AdvisorCommented:
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 ?
0
emi_sastraAuthor Commented:
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.
0
emi_sastraAuthor Commented:
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.
0
Mark WillsTopic AdvisorCommented:
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

0
emi_sastraAuthor Commented:
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.  
      


 
0
emi_sastraAuthor Commented:
Sorry, I miss something.

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

Thank you.
0
emi_sastraAuthor Commented:
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.
0
Mark WillsTopic AdvisorCommented:
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...



0
Mark WillsTopic AdvisorCommented:
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 ?
0
emi_sastraAuthor Commented:
Sorry, please see my last previous post.

Thank you.
0
Mark WillsTopic AdvisorCommented:
yep, me too....
0
emi_sastraAuthor Commented:
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.
0
Mark WillsTopic AdvisorCommented:
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

0
SQLSharkCommented:
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.
0
SQLSharkCommented:
Well, you certainly have a lot of expert input to help you solve your problem !  :-)
0
emi_sastraAuthor Commented:
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.
0
Mark WillsTopic AdvisorCommented:
and in fact... because there really is no "deleted" impact, your trigger could even be :

and forget that "recomputed" statement - it was a red herring...

and I am now out of here... Good luck, best wishes...


ALTER TRIGGER trg_update_stock ON Detail_Stock
FOR INSERT, UPDATE, DELETE
AS
 
  INSERT INTO Total_Stock ( ItemCode, itemqty )
  SELECT ItemCode,0 from inserted
  WHERE NOT EXISTS (SELECT NULL FROM Total_Stock TS where TS.ItemCode = inserted.Itemcode ) 
 
  UPDATE Total_Stock
  SET ItemQty = i.ItemQty-i.ItemUsed
  FROM Total_Stock
  INNER JOIN INSERTED i on Total_Stock.itemcode = i.itemcode
 
GO

Open in new window

0
emi_sastraAuthor Commented:
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.
0
SQLSharkCommented:
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 !  :-)
0
emi_sastraAuthor Commented:
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.
0
emi_sastraAuthor Commented:
Hi SQLShark,

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

Thank you.
0
SQLSharkCommented:
I've updated my profile.  I make that mistake all the time.
0
Mark WillsTopic AdvisorCommented:
@SQLShark: your bio tag lines look familiar - is that an assist ?
0
SQLSharkCommented:
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.
0
Mark WillsTopic AdvisorCommented:
Not at all, flattered in fact. Cheers... Mark Wills
0
emi_sastraAuthor Commented:
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.


0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
emi_sastraAuthor Commented:
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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

0
emi_sastraAuthor Commented:
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.



0
SQLSharkCommented:
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!)

:-)
0
emi_sastraAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.