Триггеры T-SQL вызывают ошибку «Имя столбца или количество предоставленных значений не соответствует определению таблицы»
Вот кое-что, что я не смог исправить, и я посмотрел повсюду. Возможно, здесь кто-то узнает!
У меня есть таблица с именем dandb_raw, в частности, с тремя столбцами: dunsId (PK), name и searchName. У меня также есть триггер, который действует на эту таблицу:
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
Триггер вызывает сбой вставки:
insert into [dandb_raw](dunsId, name)
select 3442355, 'harper'
union all
select 34425355, 'har 466per'
update [dandb_raw] set name ='grap6767e'
С этой ошибкой:
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.
Самое любопытное в этом то, что каждое из отдельных операторов в триггере работает само по себе. Это почти как если бы вставленная таблица была одноразовой, которая заражает временные таблицы, если вы пытаетесь переместить вставленную в одну из них.
Так что же приводит к сбою триггера? Как это можно остановить?
Ответы 4
Что такое companies_contactInfo_updateTerritories? Фактическая ссылка упоминает процедуру «companies_contactInfo_updateTerritories», но я не вижу ее в приведенном коде. Также я не вижу, где это называется. Если это не из вашего приложения, которое вызывает SQL и, следовательно, не имеет отношения ...
Если вы все протестировали, и это сработало, а теперь не работает, значит, должно быть что-то другое. Одна вещь, которую следует учитывать, - это безопасность. Я заметил, что вы просто вызываете таблицу [dandb_raw], а не [dbo]. [Dandb_raw]. Так что, если бы у пользователя была таблица с тем же именем [user]. [Dandb_raw], эта таблица использовалась бы для проверки определений вместо вашей таблицы. Также триггер создает временные таблицы. Но если некоторые временные таблицы уже существуют по какой-либо причине, но с другими определениями, это также может быть проблемой.
Я не вижу очевидной проблемы в коде.
«ВЫБРАТЬ .. В» - это слабое кунг-фу. Попробуйте явно создать определение временной таблицы:
CREATE TABLE #newMatches
(
CompanyID int PRIMARY KEY,
DunsID int
)
Когда вы закончите с #newMatches, вы должны избавиться от него, чтобы вы могли создать его позже (временные таблицы привязаны к области подключения !!)
DROP TABLE #newMatches
Также рассмотрите возможность использования таблиц переменных, поскольку они имеют область видимости как переменные. ОБЪЯВЛЕНИЕ ТАБЛИЦЫ @newMatches (CompanyId int PRIMARY KEY, DunsId int)
Я думаю, что Дэвид и Черво вместе взяли на себя эту проблему.
Я почти уверен, что отчасти происходило то, что мы использовали #newMatches в нескольких триггерах. Когда один триггер изменяет несколько строк, он запускает другой триггер, который пытается использовать область соединения #newMatches.
В результате он попытается найти таблицу, уже существующую с другой схемой, умереть и выдать сообщение, указанное выше. Одно свидетельство в пользу: используется ли для вставки область видимости стека (вложенные триггеры имеют свои собственные вставки?)
Тем не менее, все еще размышляем - по крайней мере, сейчас все работает!
Код триггера (поскольку он должен запускаться каждый раз при обновлении данных) должен быть эффективным и учитывать несколько вставок записей. Вы преуспели во втором, но не в первом. Вы сделали это слишком сложным и использовали такие вещи, как Not in, которые обычно менее эффективны, чем использование левого соединения. Временные таблицы здесь не нужны (я бы никогда не подумал об их использовании в триггере), поскольку они увеличивают неэффективность триггера. Нет причин не писать Из вставленного я вместо ОТ (выберите dunsId, имя из #magic) i
Первый, вероятно, будет быстрее, его будет проще читать и поддерживать.
Здесь: ПРИСОЕДИНЯЙТЕСЬ (выберите случай, когда charindex ('/', url) <> 0, затем оставил (url, charindex ('/', url) -1) иначе url end urlMatch, * от компаний) c ON dandb.url = c.urlMatch
Вы выбираете все поля в таблице, хотя кажется, что используете только одно. Почему? Вы также ведете это дело во всех записях компании, даже если после присоединения они вам могут не понадобиться.
Также в целом я бы избегал использования select *, но особенно в триггере. Предположим, вы выполняете вставку в другую таблицу и использовали select * из некоторой таблицы, присоединенной к вставленной или удаленной. Добавление столбца в эту таблицу приведет к сбою триггера и остановке всех изменений данных до тех пор, пока оно не будет исправлено.
Вы также использовали функцию в триггере. Это может быть очень медленным, если у вас большая вставка. Я предлагаю вам проверить это, обновив большую группу записей, и посмотреть, что произойдет. Все изменения данных не происходят только из пользовательского интерфейса, по одной записи за раз. Бывают случаи, когда одно поле обновляется из специального запроса в студии управления (когда все цены нужно откорректировать на 10%, как простейший пример, который приходит на ум). Ваш триггер должен уметь обрабатывать эти типы, если обновления, а также те, которые вы ожидаете. Я бы запустил тестовый пример, обновив 100000 строк, и посмотрел, насколько этот триггер замедляет работу.
Возможно, это на самом деле не решает вашу проблему, но триггер настолько далек от оптимального, что я должен был это сказать.
Фактически временных таблиц больше нет. Я не знаю, для чего были нужны лишние столбцы, так что они тоже исчезли. В конце концов нам придется запускать функцию для каждой строки. У нас действительно хороший показатель производительности, потому что мы выполняем массовую загрузку. Я могу отключить триггер, если процедура для массового использования выполняется быстрее ...
Будет ли выполнение UPDATE после массовой загрузки быстрее, чем эквивалентный триггер?
Это было очень полезно ... Я пропустил триггер companies_contactInfo_updateTerritories. Я собираюсь изучить это подробнее и посмотреть, смогу ли я решить проблему.