T-SQL triggers firing a "Column name or number of supplied values does not match table definition" error

Question

Here's something I haven't been able to fix, and I've looked everywhere. Perhaps someone here will know!

I have a table called dandb_raw, with three columns in particular: dunsId (PK), name, and searchName. I also have a trigger that acts on this table:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dandb_raw_searchNames]
    ON [dandb_raw]
    FOR INSERT, UPDATE
    AS

SET NOCOUNT ON

  select dunsId, name into #magic from inserted

        UPDATE dandb
            SET dandb.searchName = company_generateSearchName(dandb.name)
            FROM (select dunsId, name from #magic) i
            INNER JOIN dandb_raw dandb
                on i.dunsId = dandb.dunsId


        --Add new search matches
        SELECT c.companyId, dandb.dunsId
            INTO #newMatches
            FROM dandb_raw dandb
            INNER JOIN (select dunsId, name from #magic) a
                on a.dunsId = dandb.dunsId
            INNER JOIN companies c
                ON dandb.searchName = c.searchBrand
                --avoid url matches that are potentially wrong
                AND (lower(dandb.url) = lower(c.url)
                    OR dandb.url = ''
                    OR c.url = ''
                    OR c.url is null)


        INSERT INTO #newMatches (companyId, dunsId)
        SELECT c.companyId, max(dandb.dunsId) dunsId
            FROM dandb_raw dandb
            INNER JOIN
                (
                    select
                    case when charindex('/',url) <> 0 then left(url, charindex('/',url)-1)
                    else url
                    end urlMatch, * from companies
                ) c
                ON dandb.url = c.urlMatch
            where subsidiaryOf = 1 and isReported = 1 and dandb.url <> ''
                and c.companyId not in (select companyId from #newMatches)
            group by companyId
            having count(dandb.dunsId) = 1

        UPDATE cd
            SET cd.dunsId = nm.dunsId
            FROM companies_dandb cd
            INNER JOIN #newMatches nm
                ON cd.companyId = nm.companyId
GO

The trigger causes inserts to fail:

insert into  [dandb_raw](dunsId, name)
    select 3442355, 'harper'
    union all
    select 34425355, 'har 466per'
update [dandb_raw] set name ='grap6767e'

With this error:

Msg 213, Level 16, State 1, Procedure companies_contactInfo_updateTerritories, Line 20
Insert Error: Column name or number of supplied values does not match table definition.

The most curious thing about this is that each of the individual statements in the trigger works on its own. It's almost as though inserted is a one-off table that infects temporary tables if you try to move inserted into one of them.

So what causes the trigger to fail? How can it be stopped?

Answer 1

I think David and Cervo combined have hit on the problem here.

I'm pretty sure part of what was happening was that we were using #newMatches in multiple triggers. When one trigger changed some rows, it would fire another trigger, which would attempt to use the connection scoped #newMatches.

As a result, it would try to, find the table already existed with a different schema, die, and produce the message above. One piece of evidence that would be in favor: Does inserted use a stack style scope (nested triggers have their own inserteds?)

Still speculating though - at least things seem to be working now!

Answer 2

What is companies_contactInfo_updateTerritories? The actual reference mentions procedure "companies_contactInfo_updateTerritories" but I do not see it in the code given. Also I do not see where it is being called. Unless it is from your application that is calling the SQL and hence irrelevant....

If you tested everything and it worked but now it doesn't work, then something must be different. One thing to consider is security. I noticed that you just call the table [dandb_raw] and not [dbo].[dandb_raw]. So if the user had a table of the same name [user].[dandb_raw], that table would be used to check the definitions instead of your table. Also, the trigger creates temp tables. But if some of the temp tables already existed for whatever reason but with different definitions, this may also be a problem.