Skip to content

Instantly share code, notes, and snippets.

@BrentOzar
Created January 11, 2024 18:44
Show Gist options
  • Save BrentOzar/63de8cea53daafd45bf46188f32391e0 to your computer and use it in GitHub Desktop.
Save BrentOzar/63de8cea53daafd45bf46188f32391e0 to your computer and use it in GitHub Desktop.
Triggers to keep Posts.CommentCount in sync
/* What sent to ChatGPT 4: */
You are a T-SQL database developer working with Microsoft SQL Server 2019. Given these two tables:
CREATE TABLE [dbo].[Comments](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CreationDate] [datetime] NOT NULL,
[PostId] [int] NOT NULL,
[Score] [int] NULL,
[Text] [nvarchar](700) NOT NULL,
[UserId] [int] NULL,
CONSTRAINT [PK_Comments__Id] PRIMARY KEY CLUSTERED
([Id] ASC)
)
GO
CREATE TABLE [dbo].[Posts](
[Id] [int] IDENTITY(1,1) NOT NULL,
[AcceptedAnswerId] [int] NULL,
[AnswerCount] [int] NULL,
[Body] [nvarchar](max) NOT NULL,
[ClosedDate] [datetime] NULL,
[CommentCount] [int] NULL,
[CommunityOwnedDate] [datetime] NULL,
[CreationDate] [datetime] NOT NULL,
[FavoriteCount] [int] NULL,
[LastActivityDate] [datetime] NOT NULL,
[LastEditDate] [datetime] NULL,
[LastEditorDisplayName] [nvarchar](40) NULL,
[LastEditorUserId] [int] NULL,
[OwnerUserId] [int] NULL,
[ParentId] [int] NULL,
[PostTypeId] [int] NOT NULL,
[Score] [int] NOT NULL,
[Tags] [nvarchar](150) NULL,
[Title] [nvarchar](250) NULL,
[ViewCount] [int] NOT NULL,
CONSTRAINT [PK_Posts__Id] PRIMARY KEY CLUSTERED
([Id] ASC)
)
GO
Write a trigger for the Comments table that will keep the Posts table's CommentCount field up to date as comments are inserted, updated, and deleted.
/* What it answered: */
To create a trigger that keeps the `CommentCount` field in the `Posts` table updated based on inserts, updates, and deletes in the `Comments` table, we need to consider three scenarios:
1. **Inserts**: When a new comment is added, increment the `CommentCount` for the respective post.
2. **Updates**: If the `PostId` of a comment is changed, decrement the count from the old post and increment it for the new post.
3. **Deletes**: When a comment is deleted, decrement the `CommentCount` for the respective post.
Here is an example of how you can create such a trigger:
```sql
CREATE TRIGGER trg_UpdateCommentCount ON dbo.Comments
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
-- Insert scenario
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
BEGIN
UPDATE p
SET CommentCount = ISNULL(CommentCount, 0) + 1
FROM dbo.Posts p
INNER JOIN inserted i ON p.Id = i.PostId;
END
-- Delete scenario
IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
BEGIN
UPDATE p
SET CommentCount = ISNULL(CommentCount, 0) - 1
FROM dbo.Posts p
INNER JOIN deleted d ON p.Id = d.PostId;
END
-- Update scenario
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
-- Decrement count for the old PostId
UPDATE p
SET CommentCount = ISNULL(CommentCount, 0) - 1
FROM dbo.Posts p
INNER JOIN deleted d ON p.Id = d.PostId;
-- Increment count for the new PostId
UPDATE p
SET CommentCount = ISNULL(CommentCount, 0) + 1
FROM dbo.Posts p
INNER JOIN inserted i ON p.Id = i.PostId;
END
END
GO
```
This trigger handles each of the three cases:
- When a comment is inserted, it increments the count in the `Posts` table.
- When a comment is deleted, it decrements the count.
- When a comment is updated (specifically when the `PostId` changes), it adjusts the counts appropriately.
Make sure to test this trigger thoroughly in a development environment before deploying it in production, as triggers can sometimes have unintended side effects, especially in complex database systems.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment