Consider a table:
id int PK
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]
-- 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)