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

Откуда:
Сообщений: 29
Уважаемые Коллеги, не подскажете ли вот такой вопрос.
Создаем табличку
create table test
(
id int not null
,fild1 varchar (15) not null
,[date] datetime not null default getdate()
)

накидываем туда всякого хлама
insert into test (id,fild1) values (1,'test1')
insert into test (id,fild1) values (2,'test2')
insert into test (id,fild1) values (3,'test3')

селектом получаем вот такую выборку

id fild1 date
1 test1 2011-07-04 11:32:50.687
2 test2 2011-07-04 11:32:09.290
3 test3 2011-07-04 11:50:01.057

на сию табличку вешаем вот такой триггер, суть которого перед вставкой преврить есть ли уже запись с таким id и если есть, то удалить ее и вставить свежее значение, а если нет, то просто вставить.

ALTER TRIGGER [dbo].[InsteadInsertTest] 
   ON  [dbo].[test] 
   INSTEAD OF INSERT
AS 
DECLARE @ID int
SELECT @ID=ID FROM INSERTED
BEGIN
	IF EXISTS (SELECT ID FROM TEST WHERE ID=@ID)
		BEGIN 
			BEGIN TRY
				PRINT 'ЕСТЬ ТАКОЙ УЖЕ, УДАЛЯЮ'
				DELETE FROM TEST WHERE ID=@ID
				PRINT 'УДАЛИЛИ И ТЕПЕРЬ ВСТАВЛЯЕМ'
				INSERT INTO TEST SELECT * FROM INSERTED WHERE ID=@ID
			END TRY
			BEGIN CATCH
			IF @@TRANCOUNT>0
				ROLLBACK TRANSACTION
			END CATCH
		END	
	ELSE
		BEGIN	
			BEGIN TRY
			PRINT 'А ВОТ ТАКОГО ЕЩЕ НЕТ, ДОБАВЛЯЮ'
				INSERT INTO TEST SELECT * FROM INSERTED
			END TRY
			BEGIN CATCH
				IF @@TRANCOUNT>0
					ROLLBACK TRANSACTION
			END CATCH
		END	

END

В чем проблема, если вставлять записи по одной,
insert into test (id,fild1) values (1,'test1')
то все прекрасно работает
А если делать вот так
insert into test select ID,FILD1,GETDATE() from test
то триггер почему то срабатывает только для одной записи из набора.
Ничего внятного в BOL или MSDN не нашел, написано что триггеры instead of должны работать с конструкциями select into. кто нибудь сталкивался с подобной штукой? Может где то что то нужно сконфигурить как то особенно?
4 июл 11, 12:04    [10917460]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
step_ks
Member

Откуда:
Сообщений: 936
поставьте в триггере
SELECT * FROM INSERTED
и посмотрите сколько раз вызывается ваш триггер при инсерте и с какими данными.
4 июл 11, 12:08    [10917498]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
ё
Guest
Paklich
то триггер почему то срабатывает только для одной записи из набора.

а сколько по вашему вы проверяете записей ?
SELECT @ID=ID FROM INSERTED
...
IF EXISTS (SELECT ID FROM TEST WHERE ID=@ID)
...
триггер вызывается/выполняется 1-ин раз, для всех записей
4 июл 11, 12:09    [10917502]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Paklich,

как в скаляhye. переменную поместить одновременно несколко значений?
Paklich
SELECT @ID=ID FROM INSERTED
4 июл 11, 12:11    [10917510]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Paklich,

версия сервера какая?
4 июл 11, 12:11    [10917516]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
Paklich
Member

Откуда:
Сообщений: 29
iap,

MSSQL 2008 R2 Developer без всяких паков и хотфиксов.
что касается скалярной переменной и нескольких записей, когда я писал этот код я предполагал, что триггер будет отрабатывать отдельно для каждой записи из набора, а ни брать всю кучу и применятся один раз. Я был не прав?
4 июл 11, 12:14    [10917533]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
Paklich
Member

Откуда:
Сообщений: 29
step_ks,

спасибо, сейчас попробую.
4 июл 11, 12:14    [10917539]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Paklich
iap,

что касается скалярной переменной и нескольких записей, когда я писал этот код я предполагал, что триггер будет отрабатывать отдельно для каждой записи из набора, а ни брать всю кучу и применятся один раз. Я был не прав?
Не прав!
Paklich
MSSQL 2008 R2 Developer без всяких паков и хотфиксов.
MERGE (Transact-SQL)
4 июл 11, 12:19    [10917573]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
Paklich
Member

Откуда:
Сообщений: 29
iap,

да я тоже поглядел на этот MERGE, но хтел уже до конца отработать идею с триггером.
Кстати а Вы не знаете как этот самый MERGE будет отрабатывать с большими таблицами (около милллиона записей) в плане производительности и блокировок?
4 июл 11, 12:24    [10917608]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
Paklich
Member

Откуда:
Сообщений: 29
step_ks,

один раз он вызывается и дает набор из трех строк.
1 test1 2011-07-04 12:17:47.250
2 test2 2011-07-04 12:17:47.250
3 test3 2011-07-04 12:17:47.250
Похоже iap прав :(
4 июл 11, 12:26    [10917624]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Paklich
step_ks,

один раз он вызывается и дает набор из трех строк.
1 test1 2011-07-04 12:17:47.250
2 test2 2011-07-04 12:17:47.250
3 test3 2011-07-04 12:17:47.250
Похоже iap прав :(
step_ks на это и намекал...
4 июл 11, 12:27    [10917631]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
Paklich
Member

Откуда:
Сообщений: 29
iap,

ну что ж, делать нечего пойду обнюхивать MERGE
4 июл 11, 12:31    [10917661]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Paklich
iap,

да я тоже поглядел на этот MERGE, но хтел уже до конца отработать идею с триггером.
Типа, в триггере MERGE нельзя написать?

Работает так же, как и последовательные раздельные UPDATE, INSERT, DELETE
(если в нём, конечно, прописать все возможные действия).
При этом используются индексы и запускаются ВСЕ enabled триггеры.
Внимание! Во всех триггерах - @@ROWCOUNT будет равно количеству записей, затронутых всем MERGE!

Ну, пишите по старинке. В любом случае обращайтесь с inserted и deleted как с обычными таблицами (JOINы, предикаты и т.д.)
с неизвестным количеством записей.
В принципе, я давно считаю, что в триггерах не должно быть переменных вообще (за редким исключением)
4 июл 11, 12:36    [10917690]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
baracs
Member

Откуда: Москва
Сообщений: 7217
Paklich
step_ks,

один раз он вызывается и дает набор из трех строк.
1 test1 2011-07-04 12:17:47.250
2 test2 2011-07-04 12:17:47.250
3 test3 2011-07-04 12:17:47.250
Похоже iap прав :(
Это особенно умиляет после фразы
Paklich
Ничего внятного в BOL или MSDN не нашел
4 июл 11, 12:38    [10917699]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
Paklich
Member

Откуда:
Сообщений: 29
baracs,

ну если вы считаете что вот этот абзац из BOL
"Замечания по работе с несколькими строками в триггерах DML
При написании кода для триггера DML следует учитывать, что инициирующая триггер отдельная инструкция может влиять на несколько строк, а не на одну строку данных. Такой режим работы является стандартным для триггеров UPDATE и DELETE, поскольку эти инструкции зачастую касаются нескольких строк, и в меньшей степени распространен для триггеров INSERT, поскольку базовая инструкция INSERT добавляет только одну строку. Тем не менее, так как допускается запуск триггера инструкцией INSERT INTO (table_name) SELECT, вставка множества строк может привести к вызову одного триггера.
Учет особенностей работы с несколькими строками особенно важен в случаях, когда функция триггера DML автоматически пересчитывает сводные значения из одной таблицы и помещает результаты в другую таблицу для временных итогов."

полностью раскрывает тему вставки нескольких записей и в том числе и мой случай, то и конечно должно умилять, но где тут написано как ведет себя триггер в случае вставки нескольких записей не применительно к агрегатным функциям?
4 июл 11, 14:01    [10918325]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
Paklich
Member

Откуда:
Сообщений: 29
iap,

мне кажется в варианте с MERGE лучше будет написать хранимку и выполнять по шедулеру, чем запускать ее из триггера.
4 июл 11, 14:02    [10918347]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Paklich
baracs,

ну если вы считаете что вот этот абзац из BOL
"Замечания по работе с несколькими строками в триггерах DML
При написании кода для триггера DML следует учитывать, что инициирующая триггер отдельная инструкция может влиять на несколько строк, а не на одну строку данных. Такой режим работы является стандартным для триггеров UPDATE и DELETE, поскольку эти инструкции зачастую касаются нескольких строк, и в меньшей степени распространен для триггеров INSERT, поскольку базовая инструкция INSERT добавляет только одну строку. Тем не менее, так как допускается запуск триггера инструкцией INSERT INTO (table_name) SELECT, вставка множества строк может привести к вызову одного триггера.
Учет особенностей работы с несколькими строками особенно важен в случаях, когда функция триггера DML автоматически пересчитывает сводные значения из одной таблицы и помещает результаты в другую таблицу для временных итогов."

полностью раскрывает тему вставки нескольких записей и в том числе и мой случай, то и конечно должно умилять, но где тут написано как ведет себя триггер в случае вставки нескольких записей не применительно к агрегатным функциям?
А где тут про агрегатные функции сказано? Кстати, ссылки на этот текст почему-то нет.
У Вас SQL2008. В нём "базовый" (это VALUES() что ли?) синтаксис допускает вставку до 1000 строк в одной инструкции INSERT
INSERT [Table](<список полей>) VALUES(<список значений полей>),(<список значений полей>)...(<список значений полей>)
4 июл 11, 14:10    [10918400]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Paklich
iap,

лучше будет написать хранимку
Чем лучше?
Триггер сработает каждый раз, когда какой-нибудь клиент (неважно какой) запросит INSERT
(даже из какой-то своей хранимой процедуры).

А в случае с процедурой вместо триггера надо знать, что именно её надо вызывать для вставки/обновления/удаления.
Если кто-то вызовет INSERT напрямую, - проблемы обеспечены.
Для массовой же обработки записей таблицы придётся устраивать ещё и пляски с бубном,
чтобы передать в процедуру список обрабатываемых строк и новых значений для них.
4 июл 11, 14:18    [10918453]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
baracs
Member

Откуда: Москва
Сообщений: 7217
Paklich
baracs,

ну если вы считаете что вот этот абзац из BOL
"Замечания по работе с несколькими строками в триггерах DML
При написании кода для триггера DML следует учитывать, что инициирующая триггер отдельная инструкция может влиять на несколько строк, а не на одну строку данных. Такой режим работы является стандартным для триггеров UPDATE и DELETE, поскольку эти инструкции зачастую касаются нескольких строк, и в меньшей степени распространен для триггеров INSERT, поскольку базовая инструкция INSERT добавляет только одну строку. Тем не менее, так как допускается запуск триггера инструкцией INSERT INTO (table_name) SELECT, вставка множества строк может привести к вызову одного триггера.
Учет особенностей работы с несколькими строками особенно важен в случаях, когда функция триггера DML автоматически пересчитывает сводные значения из одной таблицы и помещает результаты в другую таблицу для временных итогов."

полностью раскрывает тему вставки нескольких записей и в том числе и мой случай, то и конечно должно умилять, но где тут написано как ведет себя триггер в случае вставки нескольких записей не применительно к агрегатным функциям?
По-моему, выделенная болдом фраза очень прозрачно намекает, что DML триггер, по-умолчанию, надо писать так, чтобы он корректно обрабатывал множество строк. Да и дальнейшие замечания - тоже.
Вообще, при написании sql-запросов, в триггерах или еще где, предполагается обработка множества строк в одном запросе.

Ну и напоследок, цитата из первого абзаца описания команды CREATE TRIGGER:
BOL
Триггер — это особая разновидность хранимой процедуры, выполняемая автоматически при возникновении события на сервере базы данных. Триггеры языка обработки данных выполняются по событиям, вызванным попыткой пользователя изменить данные с помощью языка обработки данных. Событиями DML являются процедуры INSERT, UPDATE или DELETE, применяемые к таблице или представлению. Эти триггеры срабатывают при запуске любого допустимого события независимо от того, влияет ли оно на какие-либо строки таблицы.
То есть, вам с самого начала говорят: одна инструкция DML - это одно событие. Независимо от того, сколько строк эта инструкция затрагивает.
4 июл 11, 14:39    [10918584]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
Paklich
Member

Откуда:
Сообщений: 29
iap
Кстати, ссылки на этот текст почему-то нет.

Надеюсь Вы не подозреваете что я это все руками написал да еще и из головы? :)
4 июл 11, 14:57    [10918750]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Paklich
iap
Кстати, ссылки на этот текст почему-то нет.

Надеюсь Вы не подозреваете что я это все руками написал да еще и из головы? :)
Нет. Просто, может быть, старый BOL.
Не к Вашей новейшей версии.
Хотя, как правильно заметили выше, в Вашей цитате тоже всё, в общем-то, правильно написано.
4 июл 11, 15:00    [10918777]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
Paklich
Member

Откуда:
Сообщений: 29
iap
Paklich
пропущено...

Надеюсь Вы не подозреваете что я это все руками написал да еще и из головы? :)
Нет. Просто, может быть, старый BOL.
Не к Вашей новейшей версии.
Хотя, как правильно заметили выше, в Вашей цитате тоже всё, в общем-то, правильно написано.

Может кстати быть и так, хотя у меня стоит сначала искать инфу онлайн а потом уже в локальной версии.
Да, пожалуй, я немного недочитал, но в любом случае истину мы выяснили за что собственно и спасибо всем участникам. :)
4 июл 11, 15:03    [10918807]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для вставки нескольких записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Paklich
iap
пропущено...
Нет. Просто, может быть, старый BOL.
Не к Вашей новейшей версии.
Хотя, как правильно заметили выше, в Вашей цитате тоже всё, в общем-то, правильно написано.

Может кстати быть и так, хотя у меня стоит сначала искать инфу онлайн а потом уже в локальной версии.
Да, пожалуй, я немного недочитал, но в любом случае истину мы выяснили за что собственно и спасибо всем участникам. :)
Вот истина: http://msdn.microsoft.com/ru-ru/library/ms189799(v=SQL.105).aspx
4 июл 11, 15:06    [10918838]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить