How to handle multiple rows in this Sql Server Trigger?

Question

Hi folks,

I have the following trigger, but because a trigger needs to handle multiple records, I'm not sure how to correctly handle this, in my trigger code.

Can someone please suggest how I can change the TSql below to correctly handle multiple records, instead of just a single record (as is listed, below).

Table Schema and defaults.

CREATE TABLE [dbo].[tblArticle](
 [IdArticle] [int] IDENTITY(1,1) NOT NULL,
 [IdArticleStatus] [tinyint] NOT NULL,
 [Title] [nvarchar](200) NOT NULL,
 [CleanTitle] [nvarchar](300) NOT NULL,
 [UniqueTitle] [nvarchar](300) NOT NULL,
 [Content] [nvarchar](max) NOT NULL
GO

ALTER TABLE [dbo].[tblArticle] ADD  CONSTRAINT [DF_tblArticle_CleanTitle]  
    DEFAULT (newid()) FOR [CleanTitle]
GO

ALTER TABLE [dbo].[tblArticle] ADD  CONSTRAINT [DF_tblArticle_UniqueTitle]
    DEFAULT (newid()) FOR [UniqueTitle]
GO

Trigger, which only handles a single record ... not multiple.

ALTER TRIGGER [dbo].[ArticlesAfterInsertOrUpdate]
   ON  [dbo].[tblArticle] 
   AFTER INSERT,UPDATE
AS 
BEGIN
 SET NOCOUNT ON

    DECLARE @IdArticle INTEGER,
        @Title NVARCHAR(300),
        @CleanTitle NVARCHAR(300),
        @UniqueTitle NVARCHAR(300),
        @NewCleanTitle NVARCHAR(300),
        @CleanTitleCount INTEGER

    -- Only Update the CleanTitle and UniqueTitle if *required*
    -- This means, create a unique subject of the title, then check if this clean value
    -- is different to the current clean value. If so, then update both clean and unique.
    -- Otherwise, don't do anything (because it will include this row in the count check, below).
    IF UPDATE(Title) BEGIN

        -- TODO: How will this handle multiple records???
        SELECT @IdArticle = IdArticle, @Title = Title, @CleanTitle = CleanTitle
        FROM INSERTED

        -- Create the 'Slugs'.
        SET @NewCleanTitle = dbo.CreateUniqueSubject(@Title) 
        SET @UniqueTitle = @NewCleanTitle

        IF @NewCleanTitle != @CleanTitle BEGIN

            -- We need to update the clean and unique, so lets get started...

            -- Grab the count :: eg. how many other _clean_ titles already exist?
            -- Note: this is the _only_ reason why we have this
            -- column - because it has an index on it.
            SELECT @CleanTitleCount = COUNT(IdArticle)
            FROM [dbo].[tblArticle]
            WHERE CleanTitle = @NewCleanTitle

            -- If we have some previous titles, then we need to append a number
            -- to the end of the current slug.
            IF @CleanTitleCount > 0
                SET @UniqueTitle = @NewCleanTitle + CAST((@CleanTitleCount + 1) AS VARCHAR(10))

            -- Now update the unique subject field.
            UPDATE [dbo].[tblArticle]
            SET CleanTitle = @NewCleanTitle,
                UniqueTitle = @UniqueTitle
            WHERE IdArticle = @IdArticle
        END
    END
END
GO

Please help!

Answer 1

Don't really need to know what the custom function does, just that it returns the same value for each given input (i.e. the Title). It gets a bit complicated to perform this type of logic in a trigger, but you can certainly make it happen. There are definitely other ways of making it work as well, best approach would depend entirely on your environment, however the following logic will get you what you're looking for as a starting point:

ALTER TRIGGER [dbo].[ArticlesAfterInsertOrUpdate]
   ON  [dbo].[tblArticle] 
   AFTER INSERT,UPDATE
AS 
BEGIN
 SET NOCOUNT ON


    -- Only Update the CleanTitle and UniqueTitle if *required*
    -- This means, create a unique subject of the title, then check if this clean value
    -- is different to the current clean value. If so, then update both clean and unique.
    -- Otherwise, don't do anything (because it will include this row in the count check, below).
    IF UPDATE(Title) BEGIN
        -- Materialize with the newCleanTitle value for simplicity sake, could
        -- do this inline below, not sure which would work better in your environment
        if object_id('tempdb..#tempIData') > 0
            drop table #tempIData;
        select  *, 
                dbo.CreateUniqueSubject(i.Title) as newCleanTitle
        into    #tempIData
        from    inserted i
        where   i.CleanTitle <> dbo.CreateUniqueSubject(i.Title);

        with iData as
        (   -- Get the data inserted along with a running tally of any duplicate
            -- newCleanTitle values
            select  i.IdArticle as IdArticle, 
                    i.CleanTitle, i.newCleanTitle,
                    -- Need to get the count here as well to account for cases where
                    -- we insert multiple records with the same resulting cleanTitle
                    cast(row_number() over(partition by i.newCleanTitle order by i.IdArticle) as bigint) as cntCleanTitle
            from    #tempIData i
        ),
        srcData as
        (   -- Get the existing count of data by CleanTitle value for each
            -- newCleanTitle included in the inserted data
            select  t.CleanTitle as CleanTitle,
                    cast(coalesce(count(*),0) as bigint) as cntCleanTitle
            from    dbo.tblArticle t
            join    
                    (   -- Need a distinct list of newCleanTitle values
                        select  a.newCleanTitle
                        from    iData a
                        group by a.newCleanTitle
                    ) i
                    -- Join on CleanTitle as we need to get the existing running
                    -- count for each distinct CleanTitle values
            on      t.CleanTitle = i.newCleanTitle
            group by t.CleanTitle
        )
        -- Do the update...
        update  a
        set     a.CleanTitle = i.newCleanTitle,
                a.UniqueTitle =
                    case 
                        when i.cntCleanTitle + coalesce(s.cntCleanTitle,0) > 1 
                            then i.newCleanTitle + cast((cast(i.cntCleanTitle as bigint) + cast(coalesce(s.cntCleanTitle,0) as bigint)) as nvarchar(10)) 
                        else 
                            i.newCleanTitle 
                    end
        from    dbo.tblArticle a
        join    iData i
        on      a.IdArticle = i.IdArticle
        left join srcData s
        on      i.newCleanTitle = s.CleanTitle;

        if object_id('tempdb..#tempIData') > 0
            drop table #tempIData;

    END
END