Montro1981/Add_trigger_for_trigram_maintenance.sql Secret

## Add_trigger_for_trigram_maintenance.sql
CREATE OR ALTER TRIGGER dbo.tr_ProcessTagsTrigrams ON dbo.Posts
AFTER INSERT, UPDATE, DELETE AS
BEGIN /* Start trigger body */
IF (ROWCOUNT_BIG() = 0) RETURN;

SET NOCOUNT ON;
IF NOT UPDATE(Tags) AND EXISTS (SELECT NULL FROM inserted) RETURN;

RAISERROR ('Running Trigger tr_ProcessTagsTrigrams', 0, 0) WITH NOWAIT;

IF EXISTS (SELECT NULL FROM inserted)
BEGIN
    DELETE tt
    FROM dbo.TagsTrigrams AS tt
    INNER JOIN inserted AS ins
    ON tt.PostId = ins.Id
    WHERE NOT EXISTS
    (
        SELECT NULL FROM Inserted AS p
        CROSS APPLY GenerateTagTrigrams(p.Tags) AS gtt
        WHERE tt.PostId = p.id
        AND tt.Trigram = gtt.trigram
    );

    INSERT INTO dbo.TagsTrigrams (PostId, Trigram)
    SELECT PostId = p.Id, Trigram = gtt.trigram
    FROM inserted AS p
    CROSS APPLY GenerateTagTrigrams(p.Tags) AS gtt
    WHERE p.Tags IS NOT NULL
    AND NOT EXISTS
    (
        SELECT NULL
        FROM dbo.TagsTrigrams AS tt
        WHERE tt.PostId = p.id
        AND tt.Trigram = gtt.trigram
    );
END;

IF EXISTS (SELECT NULL FROM deleted) AND NOT EXISTS (SELECT NULL FROM inserted)
BEGIN
    DELETE tt
    FROM dbo.TagsTrigrams AS tt
    INNER JOIN deleted AS del
    ON tt.PostId = del.Id
END;
END; /* End trigger body */
GO

/***
Test scripts
***/

UPDATE dbo.Posts
SET Tags = CASE Id
    WHEN 4 THEN '<c#><floating-point><type-conversion><double><decimal>'
    WHEN 6 THEN '<html><css><css3><internet-explorer-7>'
END
WHERE Id IN (4, 6);

SELECT Tags
FROM dbo.Posts
WHERE Id IN (4, 6)
ORDER BY Id;

SELECT *
FROM dbo.TagsTrigrams
WHERE PostId IN (4, 6)
ORDER BY PostId;

UPDATE dbo.Posts
SET Tags = CASE Id
    WHEN 4 THEN '<c++><floating-point><type-conversion><double><decimal>' --c# -> c++
    WHEN 6 THEN '<html><css><css3><internet-explorer-7><bootstrap>'       --Added <bootstrap>
END
WHERE Id IN (4, 6);

SELECT Tags
FROM dbo.Posts
WHERE Id IN (4, 6)
ORDER BY Id;

SELECT *
FROM dbo.TagsTrigrams
WHERE PostId IN (4, 6)
ORDER BY PostId;

INSERT INTO dbo.Posts (Body, CreationDate, LastActivityDate, PostTypeId, Score, Tags, ViewCount)
VALUES (N'Test message', GETDATE(), GETDATE(), 1, 1, NULL, 1);

SELECT *
FROM dbo.Posts
WHERE Id = SCOPE_IDENTITY();

SELECT *
FROM dbo.TagsTrigrams
WHERE PostId = SCOPE_IDENTITY();

UPDATE dbo.Posts
SET Body = N'New message'
WHERE Id = SCOPE_IDENTITY();

SELECT *
FROM dbo.Posts
WHERE Id = SCOPE_IDENTITY();

SELECT *
FROM dbo.TagsTrigrams
WHERE PostId = SCOPE_IDENTITY();

INSERT INTO dbo.Posts (Body, CreationDate, LastActivityDate, PostTypeId, Score, Tags, ViewCount)
VALUES (N'Test message with tags', GETDATE(), GETDATE(), 1, 1, N'<BrentOzar><Query><Sql>', 1);

SELECT *
FROM dbo.Posts
WHERE Id = SCOPE_IDENTITY();

SELECT *
FROM dbo.TagsTrigrams
WHERE PostId = SCOPE_IDENTITY();

DELETE dbo.Posts
WHERE Id = SCOPE_IDENTITY();

SELECT *
FROM dbo.TagsTrigrams
WHERE PostId = SCOPE_IDENTITY();
	CREATE OR ALTER TRIGGER dbo.tr_ProcessTagsTrigrams ON dbo.Posts
	AFTER INSERT, UPDATE, DELETE AS
	BEGIN /* Start trigger body */
	IF (ROWCOUNT_BIG() = 0) RETURN;

	SET NOCOUNT ON;
	IF NOT UPDATE(Tags) AND EXISTS (SELECT NULL FROM inserted) RETURN;

	RAISERROR ('Running Trigger tr_ProcessTagsTrigrams', 0, 0) WITH NOWAIT;

	IF EXISTS (SELECT NULL FROM inserted)
	BEGIN
	DELETE tt
	FROM dbo.TagsTrigrams AS tt
	INNER JOIN inserted AS ins
	ON tt.PostId = ins.Id
	WHERE NOT EXISTS
	(
	SELECT NULL FROM Inserted AS p
	CROSS APPLY GenerateTagTrigrams(p.Tags) AS gtt
	WHERE tt.PostId = p.id
	AND tt.Trigram = gtt.trigram
	);

	INSERT INTO dbo.TagsTrigrams (PostId, Trigram)
	SELECT PostId = p.Id, Trigram = gtt.trigram
	FROM inserted AS p
	CROSS APPLY GenerateTagTrigrams(p.Tags) AS gtt
	WHERE p.Tags IS NOT NULL
	AND NOT EXISTS
	(
	SELECT NULL
	FROM dbo.TagsTrigrams AS tt
	WHERE tt.PostId = p.id
	AND tt.Trigram = gtt.trigram
	);
	END;

	IF EXISTS (SELECT NULL FROM deleted) AND NOT EXISTS (SELECT NULL FROM inserted)
	BEGIN
	DELETE tt
	FROM dbo.TagsTrigrams AS tt
	INNER JOIN deleted AS del
	ON tt.PostId = del.Id
	END;
	END; /* End trigger body */
	GO

	/***
	Test scripts
	***/

	UPDATE dbo.Posts
	SET Tags = CASE Id
	WHEN 4 THEN '<c#><floating-point><type-conversion><double><decimal>'
	WHEN 6 THEN '<html><css><css3><internet-explorer-7>'
	END
	WHERE Id IN (4, 6);

	SELECT Tags
	FROM dbo.Posts
	WHERE Id IN (4, 6)
	ORDER BY Id;

	SELECT *
	FROM dbo.TagsTrigrams
	WHERE PostId IN (4, 6)
	ORDER BY PostId;

	UPDATE dbo.Posts
	SET Tags = CASE Id
	WHEN 4 THEN '<c++><floating-point><type-conversion><double><decimal>' --c# -> c++
	WHEN 6 THEN '<html><css><css3><internet-explorer-7><bootstrap>' --Added <bootstrap>
	END
	WHERE Id IN (4, 6);

	SELECT Tags
	FROM dbo.Posts
	WHERE Id IN (4, 6)
	ORDER BY Id;

	SELECT *
	FROM dbo.TagsTrigrams
	WHERE PostId IN (4, 6)
	ORDER BY PostId;

	INSERT INTO dbo.Posts (Body, CreationDate, LastActivityDate, PostTypeId, Score, Tags, ViewCount)
	VALUES (N'Test message', GETDATE(), GETDATE(), 1, 1, NULL, 1);

	SELECT *
	FROM dbo.Posts
	WHERE Id = SCOPE_IDENTITY();

	SELECT *
	FROM dbo.TagsTrigrams
	WHERE PostId = SCOPE_IDENTITY();

	UPDATE dbo.Posts
	SET Body = N'New message'
	WHERE Id = SCOPE_IDENTITY();

	SELECT *
	FROM dbo.Posts
	WHERE Id = SCOPE_IDENTITY();

	SELECT *
	FROM dbo.TagsTrigrams
	WHERE PostId = SCOPE_IDENTITY();

	INSERT INTO dbo.Posts (Body, CreationDate, LastActivityDate, PostTypeId, Score, Tags, ViewCount)
	VALUES (N'Test message with tags', GETDATE(), GETDATE(), 1, 1, N'<BrentOzar><Query><Sql>', 1);

	SELECT *
	FROM dbo.Posts
	WHERE Id = SCOPE_IDENTITY();

	SELECT *
	FROM dbo.TagsTrigrams
	WHERE PostId = SCOPE_IDENTITY();

	DELETE dbo.Posts
	WHERE Id = SCOPE_IDENTITY();

	SELECT *
	FROM dbo.TagsTrigrams
	WHERE PostId = SCOPE_IDENTITY();