Weird trigger problem when I do an INSERT into a table

Question

Hi folks,

I've got a trigger attached to a table.

ALTER TRIGGER [dbo].[UpdateUniqueSubjectAfterInsertUpdate]
   ON  [dbo].[Contents]
   AFTER INSERT,UPDATE
AS
BEGIN

-- Grab the Id of the row just inserted/updated
DECLARE @Id INT

SELECT @Id = Id
FROM INSERTED

END

Every time a new entry is inserted or modified, I wish to update a single field (in this table). For the sake of this question, imagine i'm updating a LastModifiedOn (datetime) field.

Ok, so what i've got is a batch insert thingy..

INSERT INTO [dbo].[Contents]
SELECT Id, a, b, c, d, YouDontKnowMe
FROM [dbo].[CrapTable]

Now all the rows are correctly inserted. The LastModifiedOn field defaults to null. So all the entries for this are null -- EXCEPT the first row.

Does this mean that the trigger is NOT called for each row that is inserted into the table, but once AFTER the insert query is finished, ie. ALL the rows are inserted? Which mean, the INSERTED table (in the trigger) has not one, but 'n' number of rows?!

If so .. er.. :( Would that mean i would need a cursor in this trigger? (if i need to do some unique logic to each single row, which i do currently).

?

UPDATE

I'll add the full trigger code, to see if it's possible to do it without a cursor.

BEGIN
    SET NOCOUNT ON

    DECLARE @ContentId INTEGER,
        @ContentTypeId TINYINT,
        @UniqueSubject NVARCHAR(200),
        @NumberFound INTEGER

    -- Grab the Id. Also, convert the subject to a (first pass, untested)
    -- unique subject.
    -- NOTE: ToUriCleanText just replaces bad uri chars with a ''. 
    --   eg. an '#' -> ''
    SELECT @ContentId = ContentId, @ContentTypeId = ContentTypeId, 
        @UniqueSubject = [dbo].[ToUriCleanText]([Subject])
    FROM INSERTED

    -- Find out how many items we have, for these two keys.
    SELECT @NumberFound = COUNT(ContentId)
    FROM [dbo].[Contents]
    WHERE ContentId = @ContentId
        AND UniqueSubject = @UniqueSubject

    -- If we have at least one identical subject, then we need to make it 
    -- unique by appending the current found number.
    -- Eg. The first instance has no number. 
    --     Second instance has subject + '1',
    --     Third instance has subject + '2', etc...
    IF @NumberFound > 0
        SET @UniqueSubject = @UniqueSubject + CAST(@NumberFound AS NVARCHAR(10))

    -- Now save this change.
    UPDATE [dbo].[Contents]
    SET UniqueSubject = @UniqueSubject
    WHERE ContentId = @ContentId
END

Answer 1

The trigger will be run only once for an INSERT INTO query. The INSERTED table will contain multiple rows.

Answer 2

Why not change the trigger to deal with multiple rows? No cursor or loops needed: it's the whole point of SQL ...

UPDATE
    dbo.SomeTable
SET
    LastModifiedOn = GETDATE()
WHERE
    EXIST (SELECT * FROM INSERTED I WHERE I.[ID] = dbo.SomeTable.[ID]

Edit: Something like...

INSERT @ATableVariable
    (ContentId, ContentTypeId, UniqueSubject)
SELECT 
    ContentId, ContentTypeId, [dbo].[ToUriCleanText]([Subject])
FROM
    INSERTED

UPDATE
    [dbo].[Contents]
SET
    UniqueSubject + CAST(NumberFound AS NVARCHAR(10))
FROM
    --Your original COUNT feels wrong and/or trivial
    --Do you expect 0, 1 or many rows.
    --Edit2: I assume 0 or 1 because of original WHERE so COUNT(*) will suffice
    -- .. although, this implies an EXISTS could be used but let's keep it closer to OP post
    (
    SELECT ContentId, UniqueSubject, COUNT(*) AS NumberFound
    FROM @ATableVariable
    GROUP BY ContentId, UniqueSubject
    HAVING COUNT(*) > 0
    ) foo
    JOIN
    [dbo].[Contents] C ON C.ContentId = foo.ContentId AND C.UniqueSubject = foo.UniqueSubject

Edit 2: and again with RANKING

UPDATE
    C
SET
    UniqueSubject + CAST(foo.Ranking - 1 AS NVARCHAR(10))
FROM
    (
    SELECT
        ContentId, --not needed? UniqueSubject,
        ROW_NUMBER() OVER (PARTITION BY ContentId ORDER BY UniqueSubject) AS Ranking
    FROM
        @ATableVariable
    ) foo
JOIN
    dbo.Contents C ON C.ContentId = foo.ContentId 
    /* not needed? AND C.UniqueSubject = foo.UniqueSubject */
WHERE
foo.Ranking > 1

Answer 3

Ok folks, I think I figure it out myself. Inspired by the previous answers and comments, I've done the following. (Can you folks have a quick look over to see if i've over-enginered this baby?)

.1. Created an Index'd View, representing the 'Subject' field, which needs to be cleaned. This is the field that has to be unique .. but before we can make it unique, we need to group by it.

-- Create the view.
CREATE VIEW ContentsCleanSubjectView with SCHEMABINDING AS
SELECT ContentId, ContentTypeId, 
    [dbo].[ToUriCleanText]([Subject]) AS CleanedSubject
FROM [dbo].[Contents]
GO

-- Index the view with three index's. Custered PK and a non-clustered, 
-- which is where most of the joins will be done against.
-- Last one is because the execution plan reakons i was missing statistics
-- against one of the fields, so i added that index and the stats got gen'd.
CREATE UNIQUE CLUSTERED INDEX PK_ContentsCleanSubjectView ON 
    ContentsCleanSubjectView(ContentId)
CREATE NONCLUSTERED INDEX IX_BlahBlahSnipSnip_A ON 
    ContentsCleanSubjectView(ContentTypeId, CleanedSubject)
CREATE INDEX IX_BlahBlahSnipSnip_B ON
    ContentsCleanSubjectView(CleanedSubject)

.2. Create the trigger code which now
a) grabs all the items 'changed' (nothing new/hard about that)
b) orders all the inserted rows, row numbered with partitioning by a clean subject
c) update the single row we're upto in the main update clause.

here's the code...

ALTER TRIGGER [dbo].[UpdateUniqueSubjectAfterInsertUpdate]
   ON  [dbo].[Contents]
   AFTER INSERT,UPDATE
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @InsertRows TABLE (ContentId INTEGER PRIMARY KEY,
        ContentTypeId TINYINT,
        CleanedSubject NVARCHAR(300))

    DECLARE @UniqueSubjectRows TABLE (ContentId INTEGER PRIMARY KEY,
    UniqueSubject NVARCHAR(350))

DECLARE @UniqueSubjectRows TABLE (ContentId INTEGER PRIMARY KEY,
        UniqueSubject NVARCHAR(350))

    -- Grab all the records that have been updated/inserted.
    INSERT INTO @InsertRows(ContentId, ContentTypeId, CleanedSubject)
    SELECT ContentId, ContentTypeId, [dbo].[ToUriCleanText]([Subject])
    FROM INSERTED


    -- Determine the correct unique subject by using ROW_NUMBER partitioning.
    INSERT INTO @UniqueSubjectRows
    SELECT SubResult.ContentId, UniqueSubject = CASE SubResult.RowNumber 
        WHEN 1 THEN SubResult.CleanedSubject 
        ELSE SubResult.CleanedSubject + CAST(SubResult.RowNumber - 1 AS NVARCHAR(5)) END
    FROM (
        -- Order all the cleaned subjects, partitioned by the cleaned subject.
        SELECT a.ContentId, a.CleanedSubject, ROW_NUMBER() OVER (PARTITION BY a.CleanedSubject ORDER BY a.ContentId) AS RowNumber
        FROM ContentsCleanSubjectView a 
            INNER JOIN @InsertRows b ON a.ContentTypeId = b.ContentTypeId AND a.CleanedSubject = b.CleanedSubject
        GROUP BY a.contentId, a.cleanedSubject
    ) SubResult
    INNER JOIN [dbo].[Contents] c ON c.ContentId = SubResult.ContentId
    INNER JOIN @InsertRows d ON c.ContentId = d.ContentId

    -- Now update all the effected rows.
    UPDATE a
    SET a.UniqueSubject = b.UniqueSubject
    FROM [dbo].[Contents] a INNER JOIN @UniqueSubjectRows b ON a.ContentId = b.ContentId
END

Now, the subquery correctly returns all the cleaned subjects, partitioned correctly and numbered correctly. I never new about the 'PARTITION' command, so that trick was the big answer here :)

Then i just join'd the subquery with the row that is being updated in the parent query. The row number is correct, so now i just do a case. if this is the first time the cleaned subject exists (eg. row_number = 1), don't modify it. otherwise, append the row_number minus one. This means the 2nd instance of the same subject, the unique subject will be => cleansubject + '1'.

The reason why i believe i need to have an index'd view is because if i have two very similar subjects, that when you have stripped out (ie. cleaned) all the bad chars (which i've determined are bad) .. it's possible that the two clean subjects are the same. As such, I need to do all my joins on a cleanedSubject, instead of a subject. Now, for the massive amount of rows I have, this is crap for performance when i don't have the view. :)

So .. is this over engineered?

Edit 1:

Refactored trigger code so it's waay more performant.