Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
hamanu
Member

Откуда:
Сообщений: 31
Все дело в известной уже ее "особенности". Предположим, мы делаем вставку записи в таблицу table. Потом проверяем SCOPE_IDENTITY(). Если на таблице висит хоть один insert-триггер, который в свою очередь делает вставку в другую таблицу, в которой поля identity нет, то SCOPE_IDENTITY() возвращает NULL. Причем по моим наблюдениям insert-триггер может делать не вставку а например create index или alter table - результат будет тем же.

То есть триггеры вставляющие в другие таблицы с identity они конечно победили и SCOPE_IDENTITY() работает не как @@IDENTITY. Однако, вышеуказанный факт препятствует широкому использованию SCOPE_IDENTITY() по его прямому назначению. Особенно учитывая тот факт что когда-нибудь на таблице table может появиться "неправильный" триггер и все SCOPE_IDENTITY() по таблице table в процедурах накроются медным тазом. Но тогда получается что SCOPE_IDENTITY() не рекомендуется использовать вообще (чтобы получить последнее вставленное значение в пределах current session, current scope) ???

p.s. Все написанное относится к mssql 2005, как было раньше уже не помню.

p.s.2. BOL: A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.

Похоже, это противоречит работе SCOPE_IDENTITY() в пределах current scope
1 сен 05, 16:01    [1837440]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
hamanu
Предположим, мы делаем вставку записи в таблицу table. Потом проверяем SCOPE_IDENTITY(). Если на таблице висит хоть один insert-триггер, который в свою очередь делает вставку в другую таблицу, в которой поля identity нет, то SCOPE_IDENTITY() возвращает NULL

Это у кого как... Вот пример:
create table t1(
    id int identity(1, 1),
    f  varchar(10)
)
go
create table t2(
    f  varchar(10)
)
go
create trigger trg_t1_ins
on t1
for insert
as
begin
    insert into t2 (f)
    select f from inserted
end
go

insert into t1 (f) values ('a')
select scope_identity()
вот результат его работы:
                                         
---------------------------------------- 
1
Где NULL?

hamanu
Причем по моим наблюдениям insert-триггер может делать не вставку а например create index или alter table - результат будет тем же.

Пожалуйста, еще один пример:
create table t1(
    id int identity(1, 1),
    f  varchar(10)
)
go
create table t2(
    f  varchar(10)
)
go
create trigger trg_t1_ins
on t1
for insert
as
begin
    create index ind1 on t2 (f)
end
go

insert into t1 (f) values ('a')
select scope_identity()
Результат его работы:
                                         
---------------------------------------- 
1

Так что мне, например, (и моему сиквелу) ваша "известная уже особенность" неизвестна.

hamanu
p.s. Все написанное относится к mssql 2005, как было раньше уже не помню.

Мои тесты выполнены на версии:
Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
	May  3 2005 23:18:38 
	Copyright (c) 1988-2003 Microsoft Corporation
	Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
1 сен 05, 16:10    [1837512]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
Брюлик
Member

Откуда:
Сообщений: 690
А вот и NULL (INSTEAD OF INSERT)
create table t1(
id int identity(1, 1),
f varchar(10)
)
go
create table t2(
f varchar(10)
)
go
create trigger trg_t1_ins
on t1
instead of insert
as
begin
insert into t2 (f)
select f from inserted
end
go

insert into t1 (f) values ('a')
select scope_identity()
1 сен 05, 16:18    [1837586]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
Все дело в известной уже ее "особенности". Предположим, мы делаем вставку записи в таблицу table. Потом проверяем SCOPE_IDENTITY(). Если на таблице висит хоть один insert-триггер, который в свою очередь делает вставку в другую таблицу, в которой поля identity нет, то SCOPE_IDENTITY() возвращает NULL.


Эта особенность касается @@identity. Вы перепутали.
1 сен 05, 16:19    [1837593]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
hamanu
Member

Откуда:
Сообщений: 31
Хм, я кажется нашел решающее отличие (признаюсь, упустил).
Если его выражать понятиями Вашего примера, то вот оно:

create trigger trg_t1_ins1
on t1
INSTEAD OF INSERT
as
begin
insert into t1 (f) select f from inserted
end

И все же этот триггер - другой scope ?
1 сен 05, 16:25    [1837639]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
hamanu
Member

Откуда:
Сообщений: 31
pkarklin
автор
Все дело в известной уже ее "особенности". Предположим, мы делаем вставку записи в таблицу table. Потом проверяем SCOPE_IDENTITY(). Если на таблице висит хоть один insert-триггер, который в свою очередь делает вставку в другую таблицу, в которой поля identity нет, то SCOPE_IDENTITY() возвращает NULL.


Эта особенность касается @@identity. Вы перепутали.


@@identity в Вашем примере работает четко, без NULL.
1 сен 05, 16:26    [1837654]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
hamanu
Member

Откуда:
Сообщений: 31
Брюлик
А вот и NULL (INSTEAD OF INSERT)
create table t1(
id int identity(1, 1),
f varchar(10)
)
go
create table t2(
f varchar(10)
)
go
create trigger trg_t1_ins
on t1
instead of insert
as
begin
insert into t2 (f)
select f from inserted
end
go

insert into t1 (f) values ('a')
select scope_identity()


Ого, Вы меня опередили. Я только что обнаружил у себя в скриптах этот хитрый instead of
1 сен 05, 16:28    [1837669]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Брюлик
А вот и NULL (INSTEAD OF INSERT)


И где у Вас тут вставка в таблицу с identity?! Посмотрите внимательно, что у Вас в inserted...
1 сен 05, 16:29    [1837675]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
hamanu
Member

Откуда:
Сообщений: 31
pkarklin
Брюлик
А вот и NULL (INSTEAD OF INSERT)


И где у Вас тут вставка в таблицу с identity?! Посмотрите внимательно, что у Вас в inserted...


В моем примере вставка в t1, результат тот же.
Просто scope у триггера другой, и мне казалось все должно работать. Но судя по всему - такая вот feature.
1 сен 05, 16:35    [1837713]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
@@identity в Вашем примере работает четко, без NULL.


1. Пример приводила GreenSunrise.
2. Я действительно не очень внимательно прочитал первый пост. @@identity выдавал NULL на 7 версии не помню до какого сервис-пака, когда вставка в таблицу с identity приводила к срабатыванию триггера, в котором производилась вставка так же в таблицу с identity.
1 сен 05, 16:36    [1837724]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
В моем примере вставка в t1, результат тот же.
Просто scope у триггера другой, и мне казалось все должно работать. Но судя по всему - такая вот feature.


Вот так вот триггер поменяйте, да еще и вставку массовую сделайте.


create table t1(
id int identity(1, 1),
f sysname
)
go
create table t2(
f varchar(10)
)
go
create trigger trg_t1_ins
on t1
instead of insert
as
begin
--insert into t2 (f)
select * from inserted
end
go

insert into t1 (f) --values ('a')
select name from sysobjects
select scope_identity(), @@identity

GO

DROP TABLE t1
DROP TABLE t2
1 сен 05, 16:42    [1837762]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31863
hamanu
pkarklin
Брюлик
А вот и NULL (INSTEAD OF INSERT)


И где у Вас тут вставка в таблицу с identity?! Посмотрите внимательно, что у Вас в inserted...


В моем примере вставка в t1, результат тот же.
Просто scope у триггера другой, и мне казалось все должно работать. Но судя по всему - такая вот feature.

В данном случае всё работает как надо.

@@identity, как ему и положено, возвращает последнее идентити-значение, которое хоть где-то вставилось в этом коннекте.

А scope_identity() возвращает идентити-значение, которое вставилось в этом scope.

В этом примере:
create table t1(
id int identity(1, 1),
f varchar(10)
)
go
create table t2(
id int identity(20, 11),
f varchar(10)
)
go
create trigger trg_t1_ins
on t1
instead of insert
as
begin

select * from inserted

insert into t1 (f)
select f from inserted

insert into t1 (f)
select f from inserted

insert into t2 (f)
select f from inserted

delete t1 where f in (select f from inserted)

end
go

insert into t1 (f) values ('a')

select @@identity
select scope_identity()

go
drop table t1
drop table t2
что должно вернуться? Последнее значение для таблицы[/ u] t1? А почему для неё? Заметьте - в таблице t1 вообще данных нет. Потому-что имена совпадают, хотя вставка может происходить совершенно в другие таблицы, а может, даже не вставка, а удаление?

Объяснение такого поведения простое - instead of insert триггеры заменяют операции с таблицами просто вызовами процедур, и логика их работы может быть сильно разная.

Занятно будет видеть в коде текст вида:
delete from T1 where ...
select @T1_id = SCOPE_IDENTITY()
не зная, что на таблице висит instead ofтриггер, а операция delete используется для добавления записей. :-)
1 сен 05, 16:48    [1837811]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
А это почему так:

The values contained in the inserted table for computed, identity, and timestamp columns are different for INSTEAD OF INSERT triggers specified on tables compared to an INSTEAD OF triggers specified on views.


Base table columnValue in inserted table in any INSERT trigger on a tableValue in inserted table in an INSTEAD OF INSERT trigger on a view
Is a computed columnComputed expressionUser-specified value or NULL
Has an IDENTITY property0 if IDENTITY_INSERT is OFF specified value if IDENTITY_INSERT is ONUser-specified value or NULL
Has a timestamp data typeBinary zeros if the column does not allow nulls NULL if column allows nullsUser-specified value or NULL
1 сен 05, 16:49    [1837821]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
2pkarklin: то ли я что-то недопонимаю в вашем примере (https://www.sql.ru/forum/actualpost.aspx?bid=1&tid=213289&mid=0&p=1#1837762), то ли одно из двух.

Вы создаете instead of триггер. В нем НИФИГА не вставляете в реальную таблицу (тело триггера - select * from inserted). И после этого хотите иметь какой-то identity, отличный от NULL?
1 сен 05, 16:50    [1837825]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
hamanu
Member

Откуда:
Сообщений: 31
pkarklin
автор
В моем примере вставка в t1, результат тот же.
Просто scope у триггера другой, и мне казалось все должно работать. Но судя по всему - такая вот feature.


Вот так вот триггер поменяйте, да еще и вставку массовую сделайте.


create table t1(
id int identity(1, 1),
f sysname
)
go
create table t2(
f varchar(10)
)
go
create trigger trg_t1_ins
on t1
instead of insert
as
begin
--insert into t2 (f)
select * from inserted
end
go

insert into t1 (f) --values ('a')
select name from sysobjects
select scope_identity(), @@identity

GO

DROP TABLE t1
DROP TABLE t2


да, интересная идея с селектом.
правда есть одно "но": scope_identity() таки null, а вот в таблице t1 все id-шники получаются null !
1 сен 05, 16:57    [1837869]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
правда есть одно "но": scope_identity() таки null, а вот в таблице t1 все id-шники получаются null !


Хм... Там вообще нет ниодной записи! Откуда там id с nullами возьмуться?!
1 сен 05, 17:03    [1837910]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
hamanu
Member

Откуда:
Сообщений: 31
alexeyvg
hamanu
pkarklin
Брюлик
А вот и NULL (INSTEAD OF INSERT)


И где у Вас тут вставка в таблицу с identity?! Посмотрите внимательно, что у Вас в inserted...


В моем примере вставка в t1, результат тот же.
Просто scope у триггера другой, и мне казалось все должно работать. Но судя по всему - такая вот feature.

В данном случае всё работает как надо.

@@identity, как ему и положено, возвращает последнее идентити-значение, которое хоть где-то вставилось в этом коннекте.

А scope_identity() возвращает идентити-значение, которое вставилось в этом scope.

В этом примере:
create table t1(
id int identity(1, 1),
f varchar(10)
)
go
create table t2(
id int identity(20, 11),
f varchar(10)
)
go
create trigger trg_t1_ins
on t1
instead of insert
as
begin

select * from inserted

insert into t1 (f)
select f from inserted

insert into t1 (f)
select f from inserted

insert into t2 (f)
select f from inserted

delete t1 where f in (select f from inserted)

end
go

insert into t1 (f) values ('a')

select @@identity
select scope_identity()

go
drop table t1
drop table t2
что должно вернуться? Последнее значение для таблицы[/ u] t1? А почему для неё? Заметьте - в таблице t1 вообще данных нет. Потому-что имена совпадают, хотя вставка может происходить совершенно в другие таблицы, а может, даже не вставка, а удаление?

Объяснение такого поведения простое - instead of insert триггеры заменяют операции с таблицами просто вызовами процедур, и логика их работы может быть сильно разная.


Ваша мысль в принципе ясна. Просто надо как-то в BOL про instead-триггеры в данном контексте упомянуть (а может уже и упомянуто, а я особо и не искал).

p.s.

Все же этот Ваш кусок:

go
insert into t1 (f) values ('a')
select @@identity
select scope_identity()
go

- это тот самый scope. А "scope is a module -- a stored procedure, trigger, function, or batch". А SCOPE_IDENTITY() "Returns the last IDENTITY value inserted into an IDENTITY column in the same scope".
1 сен 05, 17:07    [1837938]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
hamanu
Member

Откуда:
Сообщений: 31
pkarklin
автор
правда есть одно "но": scope_identity() таки null, а вот в таблице t1 все id-шники получаются null !


Хм... Там вообще нет ниодной записи! Откуда там id с nullами возьмуться?!


Пардон, я уже пример Ваш так и сяк покурочил и выходит - сам с собой говорю.
1 сен 05, 17:11    [1837966]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31863
hamanu
Ваша мысль в принципе ясна. Просто надо как-то в BOL про instead-триггеры в данном контексте упомянуть (а может уже и упомянуто, а я особо и не искал).
Да, упомянуть не мешало-бы.

hamanu
p.s.

Все же этот Ваш кусок:

go
insert into t1 (f) values ('a')
select @@identity
select scope_identity()
go

- это тот самый scope. А "scope is a module -- a stored procedure, trigger, function, or batch". А SCOPE_IDENTITY() "Returns the last IDENTITY value inserted into an IDENTITY column in the same scope".
Возможно, если на scope есть instead-триггер, он должен возвращать идентити последнего scope в этом триггере... Может, это было-бы правильнее.
1 сен 05, 17:12    [1837976]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
GreenSunrise
2pkarklin: то ли я что-то недопонимаю в вашем примере (https://www.sql.ru/forum/actualpost.aspx?bid=1&tid=213289&mid=0&p=1#1837762), то ли одно из двух.

Вы создаете instead of триггер. В нем НИФИГА не вставляете в реальную таблицу (тело триггера - select * from inserted). И после этого хотите иметь какой-то identity, отличный от NULL?


Дык это ж не я, а Брюлик пример привел SCOPE_IDENTITY() - функция с ограниченной областью применения с отсутствуюещей вставкой в таблицу с identity.

Я просто показал, что в inserted будут нули, а в базовой таблице Ваше ничего не будет, т.е. небыло в нее реальной вставки. И, мне не понятно, почему hamanu и Брюлик удивляются, что NULL возвращается.
1 сен 05, 17:37    [1838115]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
По-моему, вот этот пример все объясняет:
create table t1(
    id int identity(1, 1),
    f  varchar(10)
)
go
create trigger trg_t1_ins
on t1
instead of insert
as
begin
    insert into t1 (f)
    select f from inserted

    select scope_identity()
end
go

insert into t1 (f) values ('a')
select scope_identity()

                                         
---------------------------------------- 
1

                                         
---------------------------------------- 
NULL
Вставка в реальную таблицу есть. Разница в областях видимости налицо.
1 сен 05, 17:46    [1838185]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
Crimean
Member

Откуда:
Сообщений: 13147
> Вставка в реальную таблицу есть. Разница в областях видимости налицо.

Бла-бла-бла

В общем совершенно непонятно, зачем конкретному разработчику @@identity / scope_identity() и какая от них практическая польза. Забило MS - да, именно конкретно за-би-ло - на триггера, точнее на их применение, вот мы и получили "красоту". Вместо того, чтобы сделать нормальный способ выйти из триггера и установить @@error / @@rowcount / @@identity и т.д., чтобы приложение, написанное на t-sql и работающее с таблицей НЕ ЗАМЕТИЛО триггера (а разве не в этом смысл триггеров?!) удовлетворили свои нужды (Merge реплика и т.д.) и бросили. Как и большинство новых фич, кстати.
1 сен 05, 18:14    [1838344]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 671
Здравствуйте! Простите, что поднимаю старую тему - не разобраться никак в этом вопросе.

SQL Server 2012. Пытаюсь найти способ получения id только что вставленной записи в текущем scope на таблице с INSTEAD OF INSERT триггером.

Нашел 4 варианта, но правильное id вернул только один вариант с ident_current(). Правильно ли понимаю, что в любом случае нужно обязательно оборачивать вставку и селект в одну транзакцию, чтобы гарантировано возвращалось id из текущего scope? Или есть еще варианты? Без транзакции?

Спасибо!

+ варианты получения id вставленной записи
CREATE TABLE t1 (id int IDENTITY(10,1), txt nvarchar(10))
CREATE TABLE t2 (id int IDENTITY(666,1), txt nvarchar(10))
GO

CREATE TRIGGER t1_insert ON t1 INSTEAD OF INSERT
AS
BEGIN
 INSERT t2 (txt) SELECT txt FROM inserted
 
 /* 
 --можно в конце тригера подменять текущее @@IDENTITY
 CREATE TABLE  #fix_identity (id int IDENTITY) 
 SET IDENTITY_INSERT #fix_identity ON 
 INSERT #fix_identity (id) VALUES (IDENT_CURRENT('t1'))
 */
END 

GO

DECLARE @id TABLE (id int)

--BEGIN TRAN
INSERT t1 (txt)
OUTPUT inserted.id INTO @id (id)
VALUES ('test')
SELECT @@IDENTITY _identity, SCOPE_IDENTITY() _scope_identity, (SELECT id FROM @id) _output_inserted, IDENT_CURRENT('t1') _ident_current
--COMMIT TRAN

IF OBJECT_ID('t1','U')>0 DROP TABLE t1
IF OBJECT_ID('t2','U')>0 DROP TABLE t2
3 сен 12, 01:30    [13102978]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
0. Некромант
1. Зачем тебе INSTEAD OF? Ситуация?
2. @@Identity но это уже старьё
3. А если ты вставляешь N строк то уже хамба, OUTPUT тут не катит
4. И вот как раз против всего этого и появляется в 2012 SEQUENCE, где через NEXT VALUE FOR уже побоку шо там, есть INSTEAD OF или нет, вставляешь одну строку или много, в нужном порядке или нет.
3 сен 12, 03:05    [13103015]     Ответить | Цитировать Сообщить модератору
 Re: SCOPE_IDENTITY() - функция с ограниченной областью применения  [new]
iap
Member

Откуда: Москва
Сообщений: 47083
Mnior
4. И вот как раз против всего этого и появляется в 2012 SEQUENCE, где через NEXT VALUE FOR уже побоку шо там, есть INSTEAD OF или нет, вставляешь одну строку или много, в нужном порядке или нет.
Но ведь я могу взять, да и вставить что-нибудь злонамеренно без использования SEQUENCE! Правда?
В особо тяжёлых случаях я раньше заводил в таблице поле типа UNIQUEIDENTIFIER,
вставлял туда известное мне только что сгенерированное значение,
после вставки задача определения IDENTITY вставленных записей - тривиальная задача.
Использовать в качестве PK вот это самое UNIQUEIDENTIFIER-поле - мысль довольно спорная. Старался так не делать.
3 сен 12, 09:53    [13103340]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить