Triggers to keep Posts.CommentCount in sync

Conversation.tsql

/* 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.