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

Откуда: СПб
Сообщений: 270
Всем добрый день!

подскажите, можно ли в Default value указать функцию с входным параметром, при чем чтобы значение параметра бралось из поля этой же таблицы?
27 мар 14, 23:06    [15797760]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
MihaZ
Member

Откуда: СПб
Сообщений: 17
Веткин Сергей, нет, но для таких целей можно использовать триггер.
27 мар 14, 23:12    [15797786]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
vso
Member

Откуда: СПб
Сообщений: 270
MihaZ,

у меня сделан триггер

CREATE TRIGGER trListVolume_Insert
ON dbo.[LISTVOLUME]
INSTEAD OF INSERT
AS
BEGIN
  DECLARE @MaxSort  int;

  SET NOCOUNT ON;

  SELECT @MaxSort = ISNULL(MAX(l.PAGE),1) + 1
  FROM LISTVOLUME l, INSERTED i
  WHERE i.REF_REV = l.REF_REV

INSERT INTO LISTVOLUME (NAME, PAGE, TYPEPAGE, NOTICE, REF_REV)
  SELECT NAME,
         @MaxSort,
         TYPEPAGE,
         NOTICE,
         REF_REV
  FROM INSERTED;
END
GO


но в этом случае scope_identity() возвращает NULL
28 мар 14, 08:42    [15798697]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
MihaZ
Member

Откуда: СПб
Сообщений: 17
Веткин Сергей, можно по подробней, что вы хотите сделать?
В чем проблема с ваши триггером?
28 мар 14, 09:04    [15798755]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
MihaZ
Member

Откуда: СПб
Сообщений: 17
Веткин Сергей, почитайте эту статью из msdn

К сообщению приложен файл. Размер - 8Kb
28 мар 14, 09:36    [15798847]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
vso
Member

Откуда: СПб
Сообщений: 270
MihaZ, я эту статью читал.

Значение в счетчик вставляется корректно.

А вот в такой конструкции будет косяк:

INSERT INTO LISTVOLUME (NAME, NOTICE, REF_REV)
VALUES ('Имя', NULL, 117)
SELECT @ID = scope_identity()


Так вот переменная @ID будет равна NULL
и при таком подходе:

DECLARE @OUT table(id int not null);

INSERT INTO LISTVOLUME (NAME, NOTICE, REF_REV)
  OUTPUT INSERTED.ID_LISTVOLUME
VALUES ('Имя', NULL, 117);

SELECT @ID = ID FROM @OUT;


@ID будет равна 0

Хотя если воспользоваться

такой конструкцией:
SET @ID = IDENT_CURRENT('LISTVOLUME');


то я получу реальное значение ID вставленной записи, но эта же функция просто берет значение последнего ID из таблице и если почти одновременно кто-то вставит две строки будет получено значение не соответсвующее действительности.
28 мар 14, 10:02    [15798955]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
Если позволит версия сервера, то лучше отказаться от identity в пользу sequence.
Либо делать примерно такой трюк:
+
use tempdb;
go

create table dbo.t (id int identity primary key, v int);
go

create trigger tr_t
on dbo.t
instead of insert
as
begin
 set nocount on;

 declare @ids table (id int primary key);

 insert into dbo.t (v)
 output
  inserted.id into @ids
 select
  v
 from
  inserted;

 if object_id('tempdb..#ids', 'U') is not null
  insert into #ids
  select id from @ids;
end;
go

insert into dbo.t values (1);
go

create table #ids (id int);
go

insert into dbo.t values (2);
select * from #ids;
go

drop table dbo.t, #ids;
go

ЗЫ: Ваш триггер некорректен, т.к. не учитывает наличия в inserted множества записей.
28 мар 14, 10:46    [15799222]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
MihaZ
Member

Откуда: СПб
Сообщений: 17
Веткин Сергей, я правильно понял, что проблема в том, что при использовании триггера INSTEAD OF INSERT, после добавления записи нет возможности узнать идентификатор добавленной записи?
28 мар 14, 10:55    [15799287]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8826
автор
но в этом случае scope_identity() возвращает NULL

Используйте последовательности.
28 мар 14, 11:28    [15799553]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
vso
Member

Откуда: СПб
Сообщений: 270
MihaZ, да
28 мар 14, 12:03    [15799791]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
vso
Member

Откуда: СПб
Сообщений: 270
invm
ЗЫ: Ваш триггер некорректен, т.к. не учитывает наличия в inserted множества записей.


Да, попробовал несколько записий, этот триггер не работает.

А На сколько предложенный вами трюк накладен для сервера с временной таблицей?

Ну и потом никто кроме мяне не будет знать что надо сделать, чтобы получить вставленный идентификатор.

А вообще я хочу чтобы на стороне сервера автоматом присваивался номер в поле PAGE в пределах. Может кто подскажет как это сделать? При этом чтобы это поле оставалось not null.
28 мар 14, 12:26    [15799972]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
MihaZ
Member

Откуда: СПб
Сообщений: 17
Веткин Сергей
А вообще я хочу чтобы на стороне сервера автоматом присваивался номер в поле PAGE в пределах. Может кто подскажет как это сделать? При этом чтобы это поле оставалось not null.


1) Добавлять записи с помощью хранимой процедуры и там вычислять PAGE.
2) Изменять поле PAGE на обычном триггере, но без ограничения NOT NULL.
29 мар 14, 18:54    [15805553]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
vso
Member

Откуда: СПб
Сообщений: 270
Пока сделал триггер instead of с учетом замечания invm, о том что при множественной вставке будет не правильная работа, т.ч. с учетом этого триггер получился такой:

CREATE TRIGGER trListVolume_Insert
ON dbo.[LISTVOLUME]
INSTEAD OF INSERT
AS
BEGIN
  DECLARE @MPage int;

  SET NOCOUNT ON;
  
  SELECT @MPage = ISNULL(MAX(l.PAGE), 1)
  FROM LISTVOLUME l, INSERTED i
  WHERE i.REF_REV = l.REF_REV
  
  INSERT INTO LISTVOLUME (NAME, PAGE, TYPEPAGE, NOTICE, REF_REV)
    SELECT i.NAME,
           @MPage + ROW_NUMBER() OVER (ORDER BY i.PAGE),
           i.TYPEPAGE,
           i.NOTICE,
           i.REF_REV
    FROM INSERTED i;
END


При вставке использую такую конструкцию:

INSERT INTO LISTVOLUME (NAME, NOTICE, REF_REV)
VALUES ('Имя', NULL, 117)
SELECT @ID = @@identity


@@Identity возвращает значения идентификатора, правда в документации прочитал следующее:

автор
Функции @@IDENTITY и SCOPE_IDENTITY возвращают последнее значение идентификатора, сформированное в любой таблице в текущем сеансе. Однако функция SCOPE_IDENTITY возвращает значение только в пределах текущей области, в то время как функция @@IDENTITY не ограничена определенной областью.


Под текущем сеансом, похоже подразумевается текущий запрос, процедура и т.д., а вот может кто подскажет что имеется ввиду под фразой определенная область????
31 мар 14, 18:04    [15812895]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
o-o
Guest
Веткин Сергей
Функции @@IDENTITY и SCOPE_IDENTITY возвращают последнее значение идентификатора, сформированное в любой таблице в текущем сеансе. Однако функция SCOPE_IDENTITY возвращает значение только в пределах текущей области, в то время как функция @@IDENTITY не ограничена определенной областью.

Под текущем сеансом, похоже подразумевается текущий запрос, процедура и т.д., а вот может кто подскажет что имеется ввиду под фразой определенная область????


вот потому надо читать на языке оригинала

сеанс = session:

>>>SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session.

определенная область = scope:

>>>For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.


Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.
31 мар 14, 18:26    [15813036]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
vso
Member

Откуда: СПб
Сообщений: 270
я все же пытаюсь не отступить от триггера INSTEAD OF и как то изловчиться возвращать значения. Тем более, что появляется представление через которое нужно обновлять и изменять данные. А тут без триггера INSTEAD OF не обойтись. И нужно вернуть на клиент Id записи с которой работал, что бы обновить информацию.

и на просторах нашел такие советы

[url=]http://stackoverflow.com/questions/1434088/scope-identity-and-instead-of-insert-trigger-work-around[/url]
или
[url=]http://wiki.alphasoftware.com/Scope_Identity in SQL Server with nested and INSTEAD OF triggers[/url]

я попробовал вставить в конец триггера такой код
-- Added select scope_identity(); to explicitly return the 
-- inner scope identity to the outer scope
SELECT scope_identity();


Согласен, во внешнюю область значение передается, но в виде результата запроса.

А как дальше с ним можно работать? использовать это значение в процедуре или отправить в датасет на клиент?
5 апр 14, 22:23    [15838125]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Веткин Сергей
я попробовал вставить в конец триггера такой код
-- Added select scope_identity(); to explicitly return the 
-- inner scope identity to the outer scope
SELECT scope_identity();
Угу, чем дальше в лес тем страшнее партизаны.
Итак решение корявое, но такое уже ни к в какие ворота.
Только если вам начать объяснять почему - вы наверно вообще убежите.

Почему вы отказались от @@Identity?
Потому что испугались сложного ответа o-o про Scope_Identity?
Логика эмоций, страшна и беспощадна.

Послушайте совета гуру invm.
1. Лучше пользуйтесь процедурами при разработке. Особенно когда логика сложная.
2. В данной задаче INSTEAD OF триггер не даёт вам совершенно ничего полезного, кроме проблем.
Вы всё равно не сможете увидеть значение ни идентификаторов ни значения этого злочастного поля.
NOT NULL и что? Поставьте Default на поле как 0. И далее в обычном триггере заполняйте это поле.
Это значение (0) всё равно никто не сможет увидеть (гусары молчать), а только конечное правильное значение.

И научитесь вдаваться в детали.
1. JOIN так не пишут (через запятую и WHERE)
Тем более что ваш IsNull не работает, в таблице может не быть записей (данные ещё не попали в таблу). И значение у вас с 2х пойдёт.
2. Не опускайте имена схем у объектов - это плохо и даже чревато.
3. Не надо прививать себе привычку отражать маленькие шаги вашего мышления в отдельные команды.
REF_REV - в Inserted может быть несколько разных, и в одну скалярную переменную все эти варианты не засунешь.
Да и не нужно вообще засовывать "промежуточные варианты" в переменные.
Вообще старайтесь не писать переменные.
ALTER TABLE [dbo].[ListVolume] ADD CONSTRAINT [DF_Page] DEFAULT (0) FOR [Page];
GO
CREATE TRIGGER [dbo].[trListVolume_Insert] ON [dbo].[ListVolume]
AFTER INSERT AS BEGIN
	SET NOCOUNT ON;

	UPDATE	T
	SET	Page	= M.MaxPage + 1
	FROM	Inserted		I -- LEFT
	JOIN (	SELECT	T.Ref_Rev
		,	Max(T.Page)	AS MaxPage
		FROM	dbo.ListVolume	T
		GROUP BY T.Ref_Rev)	M ON M.Ref_Rev = I.Ref_Rev
	JOIN	dbo.ListVolume		T ON T.ID = I.ID
--	WHERE	I.Page = 0
END
GO
И вообще лучше Max Page для Ref_Rev, хранить прямо в таблице-сущности этого Ref_Rev (и непонятно что это, название ничего не отражает). Чтобы не бегать лишний раз по таблице, а главное у вас не учитывается что записи могут и параллельно вставляться - и получится что с идентичными Page.
ALTER TABLE [dbo].[ListRev] ADD [MaxPage] Int NOT NULL CONSTRAINT [DF_MaxPage] DEFAULT (0);
GO
CREATE TRIGGER [dbo].[trListVolume_Insert] ON [dbo].[ListVolume]
AFTER INSERT AS BEGIN
	SET NOCOUNT ON;

	UPDATE	R
	SET	MaxPage	+= 1
	FROM (	SELECT	DISTINCT Ref_Rev
		FROM	Inserted	I
	--	WHERE	I.Page = 0
				)	I
	JOIN	dbo.ListRev		R ON R.ID = I.Ref_Rev

	UPDATE	T
	SET	Page	= R.MaxPage
	FROM	Inserted	I
	JOIN	dbo.ListRev	R ON R.ID = I.Ref_Rev
	JOIN	dbo.ListVolume	T ON T.ID = I.ID
--	WHERE	I.Page = 0
END
GO

"WHERE I.Page = 0" - Написано, если вы хотите оставить фишку насильно вписывать нужный Page.
Название dbo.ListRev взято с потолка, т.к. я не знаю на что ссылается Ref_Rev.
6 апр 14, 00:42    [15838493]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
vso
Member

Откуда: СПб
Сообщений: 270
Mnior
Только если вам начать объяснять почему - вы наверно вообще убежите.


На самом деле не плохо было бы, вообщем-то и приходишь на форум чтобы разобраться.

Mnior
Тем более что ваш IsNull не работает, в таблице может не быть записей (данные ещё не попали в таблу). И значение у вас с 2х пойдёт.


на этот случай и написано, что если записей нет еще в этой ревизии (REF_REV), начинаем нумерацию со 2-ой страницы

Mnior
Почему вы отказались от @@Identity?


потому что подразумеваются еще триггеры на логирование и @@Identity будет выводить не верные значения.

Применительно к этой таблице, похоже, придется отказаться от этого триггера, но вот на представление, которое состоит из нескольких таблиц как сделать вставку? только instead of и мне надо обновить данные на клиенте, значит надо получить значение нужного мне ID.
6 апр 14, 09:42    [15838805]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Ситуация с @@IDENTITY, помогите отловить
Получить список новых ID после insert
6 апр 14, 11:45    [15838992]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Веткин Сергей
На самом деле не плохо было бы, вообщем-то и приходишь на форум чтобы разобраться.
Почему-то видно именно обратное. Что-то показалось сложным - всё бежите к чему-то другому.

Вообще SELECT в триггере это моветон. По многим причинам, все сразу просто лень набирать.
1. Если стоит Disallow Results From Triggers в настройках сервера то будет сразу ошибка.
В новых версия сервера это будет уже по умолчанию и нельзя будет отключить.
2. Не будет работать некоторые виды запросов к этой таблице/view, к примеру OUTPUT.
Т.е. вы уже тем самым запрещаете получать идентификаторы строк нормальным способом.
3. Такой способ всё равно не даёт нормально получать данные на том же клиенте.
Особенно если вы внутри какого-то триггера делаете вставку в иную таблу у которой в триггере тоже этот код.
Вы просто не разберёте что и откуда возвращается.
Не осознавать сразу это - это не видеть дальше собственного носа, не говоря о встроенном в каждом нормальном программесте чувство ... линейности/целостности (плять, долго описывать и подбирать слова). От таких решений просто выворачивает наизнанку.
Веткин Сергей
Mnior
Тем более что ваш IsNull не работает, в таблице может не быть записей (данные ещё не попали в таблу). И значение у вас с 2х пойдёт.
на этот случай и написано, что если записей нет еще в этой ревизии (REF_REV), начинаем нумерацию со 2-ой страницы
Картинка с другого сайта.
Так потому и написано что не работает ваш IsNull. И с чего я по вашему мнению я написал про ваш IsNull?
Ибо если нет записаей в табле, то запрос вернёт нуль строк и ваша переменная останется NULL, а не 2 как вы думаете.
Веткин Сергей
Mnior
Почему вы отказались от @@Identity?
потому что подразумеваются еще триггеры на логирование и @@Identity будет выводить не верные значения.
Ок. Вытянули из вас инфу по вашим мыслям.
Но это не сколько не оправдывает их правильность.

Для "логирования" есть иные встроенные механизмы типа Change Data Capture. И не логирование это, ибо оно подразумевает записи даже попыток действий, независимо отвалилось оно или нет.
Веткин Сергей
Применительно к этой таблице, похоже, придется отказаться от этого триггера, но вот на представление, которое состоит из нескольких таблиц как сделать вставку?
1. Всё тоже самое. хоть одна хоть две - ЭТА задача решается AFTER триггерами и такое решение ничего не ломает.
Думаю вы вряткле поёметё сейчас почему вообще многое должно решаться именно после действия - для этого надо понять природу транзакционности.
2. К сожалению у скуля много чего недорешённого и использовать полноценно логические сущности (VIEW), имеющие размазанное физическое представление (несколько таблиц), бывает проблематично.
Только решение отказаться от Identity вообще и поставить SEQUENCE. Единственный недостаток - надо явно писать его при вставке (хотя бы для VIEW). Зато неважно VIEW у вас или таблица. Но да - это полумера и MS выбрала неправильный подход, но увы (и не думаю что вы поймёте почему и как лучше было бы).

Вы должны понимать главное, не вы первооткрыватель, люди многие (сотни тысяч) уже прошли это (и довольно давно), в поисках лучшего решения, тем более с более глубоким пониманием и более ответственным подходом чем у вас.
6 апр 14, 17:48    [15839810]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
vso
Member

Откуда: СПб
Сообщений: 270
Mnior,
Классно Вы по мне прошлись, конечно, читая Ваш ответ чувствовал себя ниже плинтуса.

Mnior
Так потому и написано что не работает ваш IsNull. И с чего я по вашему мнению я написал про ваш IsNull?
Ибо если нет записей в табле, то запрос вернёт нуль строк и ваша переменная останется NULL, а не 2 как вы думаете.


Как раз будет два, специально проверил, правда ваша правда в этом

Mnior
Да и не нужно вообще засовывать "промежуточные варианты" в переменные.
Вообще старайтесь не писать переменные.


Пока проверял Ваше утверждение про IsNull, наткнулся на то, что если будет множественная вставка и REF_REV будут разных значений, то страницу все же посчитает неправильно.

Mnior
Для "логирования" есть иные встроенные механизмы типа Change Data Capture.


У меня SQL2008 R2 Express, когда этот механизм работает только в Enterprise. :(

Mnior
Только решение отказаться от Identity вообще и поставить SEQUENCE.


на сколько я знаю это возможность появилась только в MSSQL2012
6 апр 14, 21:58    [15840464]     Ответить | Цитировать Сообщить модератору
 Re: Default value в поле таблице  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Веткин Сергей
Как раз будет два, специально проверил
Да, я тут ступил капитально, это же безусловная агрегация (без GROUP BY). Она всегда возвращает одну строку независимо от данных.
И про ROW_NUMBER только сейчас заметил. Название Page вообще сбивает с толку. Может вы имели ввиду другое - это нумерация строк в рамках каждого REF_REV.
Тогда это совсем другое.
Тут встаёт много вопросов, а вам точно нужно эту нумерацию хранить в табле? Можно же её при SELECT добавлять.
А как вы обеспечиваете перерасчёт нумерации если данные меняются (удаляются, изменяется REF_REV)?
+
ALTER TABLE [dbo].[ListVolume] ADD CONSTRAINT [DF_Page] DEFAULT (0) FOR [Page]
CREATE UNIQUE INDEX [UX_ListVolume_Page] UNIQUE (Ref_Rev,Page) WHERE (Page > 0)
-- Можно вместо этой колоки использовать индексированное представление:
ALTER TABLE [dbo].[ListRev] ADD [LastPage] Int NOT NULL CONSTRAINT [DF_LastPage] DEFAULT (0);
GO
CREATE TRIGGER [dbo].[trListVolume_Insert] ON [dbo].[ListVolume]
AFTER INSERT AS BEGIN
	SET NOCOUNT ON;

	UPDATE	R
	SET	LastPage += G.[Count]
	FROM (	SELECT	I.Ref_Rev
		,	Count(*)	AS [Count]
		FROM	Inserted	I
		GROUP BY I.Ref_Rev)	G
	JOIN	dbo.ListRev		R ON R.ID = I.Ref_Rev

;WITH [Inserted] AS (
	SELECT	*,1-RowNumber()OVER(PARTITION BY I.Ref_Rev ORDER BY I.ID DESC) AS [Number]
	FROM	Inserted	I
)	UPDATE	T
	SET	Page	= R.LastPage + [Number]
	FROM	[Inserted]	I
	JOIN	dbo.ListRev	R ON R.ID = I.Ref_Rev
	JOIN	dbo.ListVolume	T ON T.ID = I.ID
END
GO

Веткин Сергей
У меня SQL2008 R2 Express, когда этот механизм работает только в Enterprise. :(
Да, к сожалению.
Но основной подход при этом не отменяет (логирование на уровне клиентской части и/или/как специализированных средств). И да - это не аргумент против того что @@Identity может быть "перебито", это лишь к тому что упоминание про логирование отвлекающий аргумент.
Веткин Сергей
на сколько я знаю SEQUENCE появилась только в MSSQL2012
Угу. Т.е. в версиях ниже нормальных решений нет, только полумеры. Поэтому процедуры, как интерфейс взаимодействия и используют намного чаще.
И @@Identity можно юзать и заполнение временных таблиц и самописных аналогов SEQUENCE и NewID и т.п.
У всех будут недостатки и преимущества.
7 апр 14, 00:20    [15840902]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить