StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you do <strong>need</strong> to store the count, for whatever reason, have you considered getting SQL Server to do the work for you? If you remove <code>TotalPages</code> from your first table, and define this view:</p> <pre><code>CREATE VIEW dbo.MagzineWithPages WITH SCHEMABINDING AS select m.ID,Name,MagzineType,COUNT_BIG(*) as TotalPages,m.DateCreated,m.Active from dbo.Magzine m inner join dbo.MagzinePage mp on m.ID = mp.MagzineID group by m.ID,Name,MagzineType,m.DateCreated,m.Active GO CREATE UNIQUE CLUSTERED INDEX IX_MagzineWithPages on MagzineWithPages (ID) </code></pre> <p>Then the <code>COUNT()</code> will be automatically updated as page rows are added and removed. This also has the benefit that you might wish to update this definition, to e.g. ignore pages where <code>Active</code> is 0.</p> <hr> <p>Why your trigger <em>is</em> broken - run an INSERT like this:</p> <pre><code>INSERT INTO [dbo].[MagzinePage]([MagzineID],[DateCreated],[CreatedBy],[Active]) VALUES (1,CURRENT_TIMESTAMP,'Me',1), (2,CURRENT_TIMESTAMP,'Me also',1) </code></pre> <p>The <code>TOP 1 MagzineID</code> from that table will now be either <code>1</code> or <code>2</code>. You'll run the update for one of those, and miss the other one.</p> <hr> <p>If you really want to keep it in the original table (I'd strongly advise against it, by if your insistent):</p> <pre><code>CREATE TRIGGER T_MagzinePage on dbo.MagzinePage AFTER INSERT,UPDATE,DELETE AS SET NOCOUNT ON ;with Deltas as ( select MagzineID,COUNT(*) as Cnt,0 as Del from inserted group by MagzineID union all select MagzineID,COUNT(*),1 from deleted group by MagzineID ), Merged as ( select MagzineID,SUM(CASE WHEN Del=0 then Cnt ELSE -Cnt END) as Net ) update m set TotalPages = TotalPages + Net from Magzine m inner join Merged mm on m.MagzineID = mm.MagzineID </code></pre> <p>Should do it as a single trigger (scrap your existing one), and properly copes with multiple rows in inserts, and with updates changing <code>MagzineID</code> and a whole host of other issues.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload