?
Solved

Query question - Update by calculated count

Posted on 2009-02-23
12
Medium Priority
?
156 Views
Last Modified: 2012-05-06
Consider a table:
[layout_elements]
id int PK
path nvarchar(1024)
group_name nvarchar(255)
grid_cols int
grid_rows int
ready bit


There is a process that inserts records into this table. The process is "dumb" in that it does not know how many records it will insert, it just inserts them as it is told to.

There are no set based inserts, only single records at a time. I put a trigger on INSERT,UPDATE on this table.  What I wish to happen is for the ready bit to be set to 1 if upon insert or update, the number of records that share a group_name value equals grid_rows * grid_cols

I put a log in my trigger, and I have verified there are no set based insertions happening, it triggers once per insert. The trigger I wrote s in the attached snippet.

Once everything is done running, layout_elements has the expected number of records,  but ready has been set to only some of them. The rest remain at zero, even though there exist the rows*cols number of records for their given group name.  

If I then take the query I wrote in the trigger and run it manually the ready flag is still not updated as it should,  so I guess my query is wrong.

What am I doing wrong?


CREATE TRIGGER [dbo].[t_CheckLayoutElements]
	ON 
		[dbo].[layout_elements]
	FOR INSERT,UPDATE
AS
	-- update the ready flag if the total elements in a given group is equal to the  grid_cols*grid_rows
 
	update layout_elements set ready=1 where grid_cols*grid_rows = ( select count(*) from layout_elements where group_name=group_name)
GO

Open in new window

0
Comment
Question by:PMH4514
  • 6
  • 5
12 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23713572
You had no correlation between your subselect and your table.  And you weren't using the INSERTED table so it would update EVERY column.

Just update your PRIMARYKEYCOLUMN.
CREATE TRIGGER [dbo].[t_CheckLayoutElements]
        ON 
                [dbo].[layout_elements]
        FOR INSERT,UPDATE
AS
        -- update the ready flag if the total elements in a given group is equal to the  grid_cols*grid_rows
 
        update le
        set ready=1 
        from layout_elements le 
          join inserted i
            on le.PRIMARYKEYCOLUMN = i.PRIMARYKEYCOLUMN
        where grid_cols*grid_rows = ( select count(*) from layout_elements where group_name=i.group_name)
GO

Open in new window

0
 
LVL 2

Expert Comment

by:souvik2008
ID: 23714010
You can try with this query to create the trigger . It would be a simpler solution I think.Your Where clause in the select sub query is checking every single group_name with its own and hence returning the total count. Try this :-


CREATE TRIGGER [dbo].[t_CheckLayoutElements]
        ON 
                [dbo].[layout_elements]
        FOR INSERT,UPDATE
AS
        -- update the ready flag if the total elements in a given group is equal to the  grid_cols*grid_rows
 
        update layout_elements set ready=1 where grid_cols*grid_rows = ( select count(*) from layout_elements AS A,layout_elements AS B where A.group_name=B.group_name)
GO

Open in new window

0
 

Author Comment

by:PMH4514
ID: 23714825
Brandon - with your version the ready field of only the last record in each set was marked as 1 rather than every record in the set. souvik - with your version in the trigger only a couple of records got ready=1 and then nothing else did.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23714882
Ok.  Then use this.
CREATE TRIGGER [dbo].[t_CheckLayoutElements]
        ON 
                [dbo].[layout_elements]
        FOR INSERT,UPDATE
AS
        -- update the ready flag if the total elements in a given group is equal to the  grid_cols*grid_rows
 
        update le
        set ready=1 
        from layout_elements le 
        where grid_cols*grid_rows = ( select count(*) from layout_elements where group_name=le.group_name)
          and exists (select 1 from inserted i where i.PRIMARYKEYCOLUMN = le.PRIMARYKEYCOLUMN )
 
GO

Open in new window

0
 

Author Comment

by:PMH4514
ID: 23714889
Actually Brandon your version does what I need if instead of replacing PRIMARYKEYCOLUMN in your code with the primary key of the table (ID) I replace it with group_name
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 23714908
Oops....
CREATE TRIGGER [dbo].[t_CheckLayoutElements]
        ON 
                [dbo].[layout_elements]
        FOR INSERT,UPDATE
AS
        -- update the ready flag if the total elements in a given group is equal to the  grid_cols*grid_rows
 
        update le
        set ready=1 
        from layout_elements le 
        where grid_cols*grid_rows = ( select count(*) from layout_elements where group_name=le.group_name)
          and exists (select 1 from inserted i where i.group_name = le.group_name )
 
GO

Open in new window

0
 

Author Comment

by:PMH4514
ID: 23714922
Sorry was typing during your followup.. What is the purpose of this in your followup:

 and exists (select 1 from inserted i where i.PRIMARYKEYCOLUMN = le.PRIMARYKEYCOLUMN )


is that just to make sure there was something inserted?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23714978
Yes.  to ensure that if you insert for group_name "HELLO" it doesn't try to recalculate for group_name "BILL".
0
 

Author Comment

by:PMH4514
ID: 23715003
Ok I see. Not that it would be problematic to recalculate for the other group names, rather, unnecessary.
thanks!
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23715020
VERY unnecessary.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23715024
Although ALL of this should be included in your insert logic, not in a trigger.
0
 

Author Comment

by:PMH4514
ID: 23715109
I agree actually.. The more I learn about stored procedures and UDF's the less I"m liking triggers, in fact I've removed most of them from my project.  This particular bit is part of an interface to a 3rd party module. The content of the table is filled and erased during runtime and the trigger has seemed to work best..  
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question