SQL Server INSERT statement's OUTPUT clause not returning identity values

Question

On a SQL Server 2008 R2 database server, I attempt to insert data into a table, but the INSERT statement's OUTPUT clause returns only zeros for the table's Identity column. The INSERT statement below is part of a larger script with many statements wrapped in a single transaction.

Here is the statement:

INSERT INTO CountryDivisions (
    [CountryID]
    , [ParentDivisionID]
    , [DivisionCodeISO]
    , [DivisionCodeShort]
    , [DivisionType]
)
    OUTPUT
        inserted.[CountryDivisionID] -- Identity column, returning only zeros!
        , inserted.[CountryID]
        , inserted.[ParentDivisionID]
        , inserted.[DivisionCodeISO]
        , inserted.[DivisionCodeShort]
        , inserted.[DivisionType]
        INTO @NewCountryDivisions
    SELECT
        dc.[CountryID]
        , null
        , COALESCE(mcd.DependentDivisionCodeISO, mcd.ParentCountryAlpha2Code + '-' + mcd.DependentCountryAlpha2Code)
        , mcd.DependentCountryAlpha2Code
        , mcd.DependentDivisionType
    FROM
        @MoreCountryData AS mcd
        JOIN Countries AS dc
            ON dc.Alpha2Code = mcd.DependentCountryAlpha2Code
    WHERE
        NOT EXISTS (
            SELECT *
            FROM CountryDivisions AS cd2
            WHERE
                cd2.[CountryID] = dc.[CountryID]
                AND cd2.[DivisionCodeShort] = mcd.DependentCountryAlpha2Code
        )

This is what I see when selecting from @NewCountryDivisions:

Here is the table's definition:

CREATE TABLE [dbo].[CountryDivisions](
    [CountryDivisionID] [int] IDENTITY(1,1) NOT NULL, -- identity column which is only returning zeros!
    [CountryID] [int] NOT NULL,
    [ParentDivisionID] [int] NULL,
    [DivisionCodeISO] [nvarchar](10) NULL,
    [DivisionCodeShort] [nvarchar](10) NULL,
    [DivisionType] [nvarchar](150) NULL,
    [LastUpdatedBy] [sysname] NOT NULL DEFAULT (user_name()),
    [LastUpdatedDateTime] [datetime] NOT NULL DEFAULT (getutcdate()),
 CONSTRAINT [PK_CountryDivisions_CountryDivisionID] PRIMARY KEY NONCLUSTERED 
(
    [CountryDivisionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

I am using INSTEAD OF triggers to update the two LastUpdated... columns. I am new to writing triggers, though I would not expect these to cause the problem, but just in case, here are their definitions:

CREATE TRIGGER [dbo].[CountryDivisions_InsertAudit]
    ON [dbo].[CountryDivisions]
    INSTEAD OF INSERT
    AS
    BEGIN
        SET NoCount ON

        INSERT INTO [dbo].[CountryDivisions] (
            -- I am assuming that SQL Server will apply the IDENTITY process to the [CountryDivisionID] column, so I am not inserting it myself.
            [CountryID]
            , [ParentDivisionID]
            , [DivisionCodeISO]
            , [DivisionCodeShort]
            , [DivisionType]
            , [LastUpdatedBy]
            , [LastUpdatedDateTime]
        )
            SELECT
                i.CountryID
                , i.ParentDivisionID
                , i.DivisionCodeISO
                , i.DivisionCodeShort
                , i.DivisionType
                , COALESCE(NULLIF(LTRIM(RTRIM(i.[LastUpdatedBy])), ''), USER_NAME())
                , COALESCE(i.[LastUpdatedDateTime], GETUTCDATE())
            FROM
                inserted AS i

    END

CREATE TRIGGER [dbo].[CountryDivisions_UpdatAudit]
    ON [dbo].[CountryDivisions]
    INSTEAD OF UPDATE
    AS
    BEGIN
        SET NoCount ON

        UPDATE [dbo].[CountryDivisions]
            SET
                CountryID = i.CountryID
                , ParentDivisionID = i.ParentDivisionID
                , DivisionCodeISO = i.DivisionCodeISO
                , DivisionCodeShort = i.DivisionCodeShort
                , DivisionType = i.DivisionType
                , LastUpdatedBy = COALESCE(NULLIF(LTRIM(RTRIM(i.[LastUpdatedBy])), ''), USER_NAME())
                , LastUpdatedDateTime = COALESCE(i.[LastUpdatedDateTime], GETUTCDATE())
            FROM
                [dbo].[CountryDivisions] AS cd
                JOIN inserted AS i
                    JOIN deleted AS d
                        ON d.CountryDivisionID = i.CountryDivisionID
                    ON cd.CountryDivisionID = i.CountryDivisionID
            WHERE
                -- only update records that have actually been updated.
                i.CountryID <> d.CountryID
                OR ISNULL(i.ParentDivisionID, -1) <> ISNULL(d.ParentDivisionID, -1)
                OR ISNULL(i.DivisionCodeISO, '') <> ISNULL(d.DivisionCodeISO, '')
                OR ISNULL(i.DivisionCodeShort, '') <> ISNULL(d.DivisionCodeShort, '')
                OR (
                    ISNULL(i.LastUpdatedBy, '') <> ISNULL(d.LastUpdatedBy, '')
                    AND UPDATE(LastUpdatedBy)
                )
                OR (
                    ISNULL(i.LastUpdatedDateTime, '1900-01-01') <> ISNULL(d.LastUpdatedDateTime, '1900-01-01')
                    AND UPDATE(LastUpdatedDateTime)
                )
    END

Answer 1

It seems that my triggers were the problem!

From a comment someone made in another Stack Overflow thread, OUTPUT clauses on INSERT statements where the underlying table contains an INSTEAD OF trigger will return only zeros for any column defined with the IDENTITY(seed, increment) function.

To get around this problem I rewrote my triggers as AFTER an trigger. This also let me consolidate two triggers into one trigger.

CREATE TRIGGER [dbo].[CountryDivisions_Audit]
    ON [dbo].[CountryDivisions]
    AFTER INSERT, UPDATE
    AS
    BEGIN
        SET NoCount ON

        -- prevent recursion
        If TRIGGER_NESTLEVEL() > 1 RETURN

        UPDATE cdan
            SET
                [LastUpdatedBy] = USER_NAME()
                , [LastUpdatedDateTime] = GETUTCDATE()
            FROM
                [dbo].[CountryDivisions] AS cdan
                JOIN inserted AS i
                    LEFT JOIN deleted AS d
                        ON d.[CountryDivisionID] = i.[CountryDivisionID]
                    ON i.[CountryDivisionID] = cdan.[CountryDivisionID]
            WHERE
                -- only update records that have actually been updated.
                ISNULL(i.[CountryDivisionID], -1) <> ISNULL(d.[CountryDivisionID], -1)
                OR ISNULL(i.[ParentDivisionID], -1) <> ISNULL(d.[ParentDivisionID], -1)
                OR ISNULL(i.[DivisionCodeISO], '') <> ISNULL(d.[DivisionCodeISO], '')
                OR ISNULL(i.[DivisionCodeShort], '') <> ISNULL(d.[DivisionCodeShort], '')
                OR ISNULL(i.[DivisionType], '') <> ISNULL(d.[DivisionType], '')
                OR (
                    ISNULL(i.LastUpdatedBy, '') <> ISNULL(d.LastUpdatedBy, '')
                    AND UPDATE(LastUpdatedBy)
                )
                OR (
                    ISNULL(i.LastUpdatedDateTime, '1900-01-01') <> ISNULL(d.LastUpdatedDateTime, '1900-01-01')
                    AND UPDATE(LastUpdatedDateTime)
                )
    END