• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1309
  • Last Modified:

Im trying to add a Computed Column Specification Formula on a table to COUNT number of entries in other table.

Im trying to add a Computed Column Specification Formula on a table to COUNT number of entries in other table.  How do i do this?
0
henjohn1520
Asked:
henjohn1520
  • 6
  • 5
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot, unless you are using sql2008, and create function that returns the computed data for the columnn.

prior to that, you have to use a trigger method with a "regular" column instead
0
 
henjohn1520Author Commented:
If i were to create a new column in the same table to count the number of entries of another column is this possible. Pretty much i am trying to count how many textbooks are in inventory. I have ISBNs and Barcodes Tables. Barcodes table includes (ID, Barcode, Description, and Out). I am trying to keep track of number of entries with same ID as well as number of entries with same ID when Out=0. I have query to update table but this has to be done for each ID one at a time. Apperently i cannot update table with multiple different values. Any help is appreciated.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
a trigger on the "other" table is what you need
do you need help with the trigger code?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
henjohn1520Author Commented:
Yes. I have never worked with triggers before.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
something like this (just fill in the correct table and column names)
CREATE TRIGGRE trg_update_counts
   ON related_table_name
 FOR INSERT, DELETE 
  AS
  UPDATE t
     SET row_count_column = (SELECT COUNT(*) 
                               FROM your_related_table r 
                              WHERE r.foreign_key_field = t.primary_key_field 
                             )
    FROM your_main_table t
   WHERE t.primary_key_field IN ( SELECT primary_key_field FROM INSERTED )
      OR t.primary_key_field IN ( SELECT primary_key_field FROM DELETED )

Open in new window

0
 
henjohn1520Author Commented:
This is what i have come up with and tried. I executed it and I got a "Commad(s) completed succesfully" Result. However the values in the table have not changed. Is there something else i have to do to run this to update the values in the fields? Do you see any problems with my statement? Thanks.
CREATE TRIGGER trg_update_counts 
   ON  Barcodes 
   FOR  INSERT, DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for trigger here
UPDATE ISNBs SET Total= (SELECT     COUNT(Barcodes.[In]) 
FROM         Barcodes INNER JOIN
                      ISBNs ON Barcodes.ID = ISBNs.ID
WHERE  Barcodes.[ID]= ISBNs.[ID]
GROUP BY Barcodes.ID)
WHERE ISBNs.[ID] IN (SELECT Barcodes.[ID])
 
END
GO

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
now you have a trigger (there are some corrections, see below), any future insert/deleted on the child table should update the relevant rows from the main table:
CREATE TRIGGER trg_update_counts 
   ON  Barcodes 
   FOR  INSERT, DELETE
AS 
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
 
    -- Insert statements for trigger here
UPDATE ISNBs SET Total= (SELECT COUNT(Barcodes.[In]) 
                           FROM Barcodes 
                          WHERE Barcodes.ID = ISBNs.ID
                        )
  WHERE ISBNs.[ID] IN (SELECT [ID] FROM INSERTED )
     OR ISBNs.[ID] IN (SELECT [ID] FROM DELETED )
 
END
GO

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and to update the current rows, one-shot:
UPDATE ISNBs SET Total= (SELECT COUNT(Barcodes.[In]) 
                           FROM Barcodes 
                          WHERE Barcodes.ID = ISBNs.ID
                        )

Open in new window

0
 
henjohn1520Author Commented:
I really appreciate your help. I executed the trigger with the corrections you made. I am still unclear about how this trigger is supposed to work. I have a front-end application and i have tested it by entering new barcodes in new records but there are no changes made on the field that has the value i want. Is there something that needs to be added to the application (im using visual basic) to execute this trigger?

Regarding the UPDATE statement. I have tried this before and have had no success. If i run that query as stated then i will get all records updated to the total value of ALL different entries. If have tried running another query with a subquery but SQLServer 2005 states that i cannot perform that statement with subquery because  subquery has multiple values. That is why i started looking into the Computed Column alternative.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>If i run that query as stated then i will get all records updated to the total value of ALL different entries.
I can't believe that, unless you did not run it like that, or the field ID is not the linking field...

>I executed the trigger with the corrections you made.
as from then, it should "work" without any additional code change in your application.
I presume, the problem is the same as for the global update "problem", somehow.
0
 
henjohn1520Author Commented:
I have tried the UPDATE statement you provided and it worked!! I really appreciate your help with this. The querys i was trying to run previously were similar but not exactly like you stated and yours did work. Again thanks for your help.
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now