Databases
StackOverflow2013
Postsdbo
POT-SQL triggers firing a "Column name or number of supplied values does not match table definition" error
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL triggers firing a "Column name or number of supplied values does not match table definition" error
    primarykey
    data
    text
    <p>Here's something I haven't been able to fix, and I've looked <strong>everywhere</strong>. Perhaps someone here will know!</p> <p>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:</p> <pre><code>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) &lt;&gt; 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 &lt;&gt; '' 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 </code></pre> <p>The trigger causes inserts to fail:</p> <pre><code>insert into [dandb_raw](dunsId, name) select 3442355, 'harper' union all select 34425355, 'har 466per' update [dandb_raw] set name ='grap6767e' </code></pre> <p>With this error:</p> <pre><code>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. </code></pre> <p>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.</p> <p>So what causes the trigger to fail? How can it be stopped?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USIlya Luzyanin
    1. USChris
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POT-SQL triggers firing a "Column name or number of supplied values does not match table definition" error
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POT-SQL triggers firing a "Column name or number of supplied values does not match table definition" error
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POT-SQL triggers firing a "Column name or number of supplied values does not match table definition" error
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload