Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Ситуация с @@IDENTITY, помогите отловить  [new]
Sgy
Member

Откуда:
Сообщений: 9
Добрый день!
Есть две таблицы
1) таблица налоговых IR (InvoiceRecuisiteID,IRNumber), IRNumber - уникальные значения
2) таблица привязки к счетам IRI (InvoiceRecuisiteID, InvoiceID) - оба поля not null
Далее код, например для налоговой '123456', которую надо привязать к счету 10
DECLARE @irid int  
INSERT IR (IRNumber)  
VALUES ( '123456' )  
SET @irid = @@IDENTITY  
INSERT IRI (InvoiceRecuisiteID, InvoiceID)  
SELECT @irid, 10   
Получаем:
IR (InvoiceRecuisiteID,IRNumber)
1 '123456'
IRI (InvoiceRecuisiteID, InvoiceID)
1 10
Если после этого я захочу еще раз провести вставку '123456' , то в таблицу IR втавка из-за уникальности не пройдет, т.е. @irid = NULL, после чего не пройдет вставка и в IRI, так как поле InvoiceRecuisiteID не может быть NULL. Вставка в IRI производится только в этом месте, т.е. пользователь из-за ограничений не может нацепить две налоговые с одинаковым номером разным счетам. Однако попадаются (редко, но всё же) случаи такие:
IR (InvoiceRecuisiteID,IRNumber)
1 '123456'
IRI (InvoiceRecuisiteID, InvoiceID)
1 10
1 135

Т.е. хотя @irid был NULL, вставка во вторую таблицу прошла. Объясните мне механизм такого проскока. Предполагаю, что это выглядит примерно так (забыл уточнить, что с таблицами интенсивно работают несколько пользователей одновременно):
Два пользователя вводят налоговые, у первого она залетает нормально, второй же вводит налоговую(может быть с тем же номером) и по какой-то причине получает @@IDENTITY от первого пользователя. Возможно ли такое?
23 июл 09, 15:07    [7451580]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
Glory
Member

Откуда:
Сообщений: 104760
Sgy

Два пользователя вводят налоговые, у первого она залетает нормально, второй же вводит налоговую(может быть с тем же номером) и по какой-то причине получает @@IDENTITY от первого пользователя. Возможно ли такое?

Такое - невозможно. @@IDENTITY ограничена текущим коннектом
23 июл 09, 15:12    [7451620]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
vino
Member

Откуда:
Сообщений: 1191
Sgy, дабы избежать любых недоразумений, используйте SCOPE_IDENTITY() вместо @@IDENTITY
23 июл 09, 15:27    [7451784]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
vino
Sgy, дабы избежать любых недоразумений, используйте SCOPE_IDENTITY() вместо @@IDENTITY
Так уж и любых!?
23 июл 09, 15:34    [7451847]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
Sgy
Member

Откуда:
Сообщений: 9
автор
@@IDENTITY ограничена текущим коннектом

Да, заметил это: в таблице IRI есть идентити-поле и записи

1 10
1 135

в своих идентити отличаются на пару дней работы, так что одновременной вставки быть не могло, что еще хуже (
23 июл 09, 15:46    [7451953]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
Glory
Member

Откуда:
Сообщений: 104760
Sgy
автор
@@IDENTITY ограничена текущим коннектом

Да, заметил это: в таблице IRI есть идентити-поле и записи

1 10
1 135

в своих идентити отличаются на пару дней работы, так что одновременной вставки быть не могло, что еще хуже (

Если "пользователь из-за ограничений не может нацепить две налоговые с одинаковым номером разным счетам", т.е. IR и IRI связаны отношением 1-1, то почему в IRI на поле InvoiceRecuisiteID не задано ограничение уникальности ?
23 июл 09, 15:49    [7451989]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
Sgy
Member

Откуда:
Сообщений: 9
Glory
почему в IRI на поле InvoiceRecuisiteID не задано ограничение уникальности ?

Колюсь до конца: пользователь из-за ограничений не может нацепить две налоговые с одинаковым номером разным счетам для разных лицевых счетов. Т.е. внутри для одного лицевого несколько счетов к оплате могут быть объеденены под одной налоговой. Но у меня получилось, когда одна налоговая висит на разных лицевых. Принадлежность к лицевому отслеживается через поле InvoiceID (в таблице Invoices есть поле Account), "перепутать" между лицевыми InvoiceID вроде невозможно, потому и не написал про это.
23 июл 09, 16:18    [7452296]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
Glory
Member

Откуда:
Сообщений: 104760
Ну так наведите порядок в логике работы вашей программы. И в схеме данных.
23 июл 09, 16:20    [7452313]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
Sgy
Member

Откуда:
Сообщений: 9
я, конечно, уже явно поставил проверку
IF EXISTS (SELECT *FROM IR WHERE IRNumber = ...)
... RETURN
, т.е. не довожу до ситуации, когда сервер ругнётся, но как-то спокойствия это не прибавило.
23 июл 09, 16:24    [7452343]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
vino
Sgy, дабы избежать любых недоразумений, используйте SCOPE_IDENTITY() вместо @@IDENTITY
Так уж и любых!?
Вот, сочинил небольшой пример:
SQL2005/2008 (для демонстрации OUTPUT)
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID(N'T','U') IS NOT NULL DROP TABLE T;
IF OBJECT_ID(N'TT','U') IS NOT NULL DROP TABLE TT;
CREATE TABLE T(ID INT NOT NULL IDENTITY);
CREATE TABLE TT(ID INT NOT NULL IDENTITY);
GO
/*Для неправильного результата OUTPUT и SCOPE_IDENTITY()*/
CREATE TRIGGER InsteadOfInsertT ON T INSTEAD OF INSERT AS INSERT T DEFAULT VALUES;
GO
CREATE TRIGGER AfterInsertT ON T FOR INSERT AS
/*Для неправильного результата IDENT_CURRENT()*/
INSERT T DEFAULT VALUES;
DELETE T WHERE ID=SCOPE_IDENTITY();
/*Для неправильного результата @@IDENTITY*/
INSERT TT DEFAULT VALUES;
INSERT TT DEFAULT VALUES;
GO

/*Вставляем одну запись*/
DECLARE @T TABLE(ID INT NULL);
INSERT T OUTPUT inserted.ID INTO @T(ID) DEFAULT VALUES;

/*Пытаемся получить вставленное значение колонки IDENTITY...*/
SELECT ID[OUTPUT] FROM @T;
SELECT @@IDENTITY[@@IDENTITY];
SELECT SCOPE_IDENTITY()[SCOPE_IDENTITY()];
SELECT IDENT_CURRENT(N'T')[IDENT_CURRENT()];
/*...а на самом деле...*/
SELECT * FROM T;

IF OBJECT_ID(N'T','U') IS NOT NULL DROP TABLE T;
IF OBJECT_ID(N'TT','U') IS NOT NULL DROP TABLE TT;
Этот пример показывает, что возможна ситуация, когда ни один из перечисленных способов не даёт правильный результат.
Приходит в голову такой способ получить значение одного (последнего) вставленного IDENTITY без вспомогательных полей:
SET XACT_ABORT ON;
BEGIN TRANSACTION;
INSERT T WITH(XLOCK,HOLDLOCK) DEFAULT VALUES;
SELECT MAX(ID) FROM T;
IF @@TRANCOUNT>0 COMMIT TRANSACTION;
Но монопольная блокировка таблицы мало кому понравится.
23 июл 09, 16:57    [7452660]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
vino
Member

Откуда:
Сообщений: 1191
iap, насчет "любого недоразумений" я преувеличил, конечно. Но и триггер FOR INSERT, маскируемый через INSTEAD OF INSERT - это редкое извращение
23 июл 09, 17:38    [7453008]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
vino
Member

Откуда:
Сообщений: 1191
vino
Sgy, дабы избежать любых недоразумений, используйте SCOPE_IDENTITY() вместо @@IDENTITY
Видимо, стоит поправить. SCOPE_IDENTITY() хорош только для версий до 2005 и в некоторых особых случаях, в остальных случаях следует пользоваться предложением OUTPUT
23 июл 09, 17:45    [7453053]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
vino
iap, насчет "любого недоразумений" я преувеличил, конечно. Но и триггер FOR INSERT, маскируемый через INSTEAD OF INSERT - это редкое извращение
Ну это же только в специальном примере с комментариями, зачем так сделано...
А вообще, не понимаю, почему Microsoft и в ус не дует, чтобы дать, наконец,
надёжное средство для определения вставленных значений IDENTITY.
Это же просто удивительно!
23 июл 09, 17:52    [7453108]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36807
iap
А вообще, не понимаю, почему Microsoft и в ус не дует, чтобы дать, наконец,
надёжное средство для определения вставленных значений IDENTITY.
Это же просто удивительно!
output чем ненадежен?
23 июл 09, 17:55    [7453134]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Кстати, заметил в своём посте неточность в запросе MAX(ID):
перед SELECTом надо бы проверять @@ROWCOUNT на 0.
А то ведь у таблицы может быть пустой триггер INSTEAD OF INSERT, например.
Тогда получим не вставленный IDENTITY, а тот, который был до INSERTа.
23 июл 09, 17:57    [7453149]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Гавриленко Сергей Алексеевич
iap
А вообще, не понимаю, почему Microsoft и в ус не дует, чтобы дать, наконец,
надёжное средство для определения вставленных значений IDENTITY.
Это же просто удивительно!
output чем ненадежен?
Тем, что ему мешает триггер INSTEAD OF INSERT.
Если нам известно, что такого триггера нет (это же ещё проверять надо, да и он потом появиться может!), то надёжен, спору нет.

Хотя...

Вот смотрите. Имеем состояние таблицы в момент INSERT. Смотрим, что изменилось после INSERTа.
Как считаете, хорошо было бы иметь возможность получить все IDENTITY, появившиеся в результате именно нашего INSERTа?
Я считаю, что это было бы очень хорошо. Но OUTPUT вернёт только записи, вставленные в
текущем SCOPE, без учёта вставленных в триггерах, в процедурах, вызванных внутри триггеров и т.д.
Если рассматривать триггер как "чёрный ящик" и даже не заморачиваться на существовании
триггеров у таблицы (теоретически так и должно быть, не правда ли?), то простого способа
узнать, что же произошло в таблице в результате вставки, нет.
23 июл 09, 18:07    [7453212]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Вот я помню, что когда SQL2005 только появился, OUTPUT для таблицы с триггером INSTEAD OF INSERT возвращал NULL
А сегодня на сервере SQL2008 с удивлением обнаружил, что возвращается 0 (!).
Но это же возмутительный баг. Например, если немного дополнить мой пример, приведённый выше,
то в качестве вставленного IDENTITY получим ID существующей с самого начала (до INSERT) записи:
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID(N'T','U') IS NOT NULL DROP TABLE T;
IF OBJECT_ID(N'TT','U') IS NOT NULL DROP TABLE TT;
CREATE TABLE T(ID INT NOT NULL IDENTITY(0,1));
CREATE TABLE TT(ID INT NOT NULL IDENTITY);
INSERT T DEFAULT VALUES;
GO
/*Для неправильного результата OUTPUT и SCOPE_IDENTITY()*/
CREATE TRIGGER InsteadOfInsertT ON T INSTEAD OF INSERT AS INSERT T DEFAULT VALUES;
GO
CREATE TRIGGER AfterInsertT ON T FOR INSERT AS
/*Для неправильного результата IDENT_CURRENT()*/
INSERT T DEFAULT VALUES;
DELETE T WHERE ID=SCOPE_IDENTITY();
/*Для неправильного результата @@IDENTITY*/
INSERT TT DEFAULT VALUES;
INSERT TT DEFAULT VALUES;
GO

/*Вставляем одну запись*/
DECLARE @T TABLE(ID INT NULL);
INSERT T OUTPUT inserted.ID INTO @T(ID) DEFAULT VALUES;

/*Пытаемся получить вставленное значение колонки IDENTITY...*/
SELECT ID[OUTPUT] FROM @T;
SELECT @@IDENTITY[@@IDENTITY];
SELECT SCOPE_IDENTITY()[SCOPE_IDENTITY()];
SELECT IDENT_CURRENT(N'T')[IDENT_CURRENT()];
/*...а на самом деле...*/
SELECT * FROM T;

IF OBJECT_ID(N'T','U') IS NOT NULL DROP TABLE T;
IF OBJECT_ID(N'TT','U') IS NOT NULL DROP TABLE TT;
Может, память мне изменяет, и я заблуждаюсь...
23 июл 09, 19:59    [7453542]     Ответить | Цитировать Сообщить модератору
 Re: Ситуация с @@IDENTITY, помогите отловить  [new]
vino
Member

Откуда:
Сообщений: 1191
iap
...OUTPUT вернет только записи, вставленные в
текущем SCOPE
, без учёта вставленных в триггерах, в процедурах, вызванных внутри триггеров и т.д.
Если рассматривать триггер как "чёрный ящик" и даже не заморачиваться на существовании
триггеров у таблицы (теоретически так и должно быть, не правда ли?), то простого способа
узнать, что же произошло в таблице в результате вставки, нет.

В общем-то SCOPE_IDENTITY работает корректно даже в этом примере - ведь в триггере фактически отменяется вставка записи, которую хотели, а то, что триггер сам вставляет какие-то записи - это, ИМХО, и должно быть черным ящиком. А вот это действительно - безобразие, так как я всегда воспринимал OUTPUT как более универсальную замену SCOPE_IDENTITY (для множеств, например)
автор
... на сервере SQL2008 с удивлением обнаружил, что возвращается 0 (!).
Но это же возмутительный баг. Например, если немного дополнить мой пример, приведённый выше,
то в качестве вставленного IDENTITY получим ID существующей с самого начала (до INSERT) записи
И справедливости ради замечу, что это и в 2005 так же
24 июл 09, 10:31    [7454745]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить