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

Откуда:
Сообщений: 98
Добрый день!

После выполнения следующего скрипта через MS SQL Server MS, наблюдаю незавершенную транзакцию.
При закрытии документа, студия пишет что есть транзакция и предлагает завершить её.
Если соглашаюсь, все помещается, если нет, не помещается.
Но в скрипте есть же коммит, что не так с добавлением триггера? (без добавления триггера транзакция закрывается нормально).
Скрипт триггера отдельно тоже исполняется без проблем.

BEGIN TRAN DBUPDATE WITH MARK

	SET ANSI_NULLS ON
		
	-- Создание таблицы [RequestServiceStatuses]
	CREATE TABLE [dbo].[RequestServiceStatuses](
		[request_service_status_id] [uniqueidentifier] NOT NULL,
		[name_localization_id] [uniqueidentifier] NOT NULL,
		[sort_index] [int] NOT NULL,
	 CONSTRAINT [PK_RequestServiceStatuses] PRIMARY KEY CLUSTERED 
	(
		[request_service_status_id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]		

	-- Создание таблицы [RequestsServiceStatusesHistory]
	CREATE TABLE [dbo].[RequestsServiceStatusesHistory](
		[request_id] [uniqueidentifier] NOT NULL,
		[request_service_status_id] [uniqueidentifier] NOT NULL,
		[set_datetime] [datetime] NOT NULL,
		[user_id] [uniqueidentifier] NOT NULL
	) ON [PRIMARY]

	ALTER TABLE [dbo].[RequestsServiceStatusesHistory]  WITH CHECK ADD  CONSTRAINT [FK_RequestsServiceStatusesHistory_Requests] FOREIGN KEY([request_id]) 
	REFERENCES [dbo].[Requests] ([request_id])

	ALTER TABLE [dbo].[RequestsServiceStatusesHistory] CHECK CONSTRAINT [FK_RequestsServiceStatusesHistory_Requests]

	ALTER TABLE [dbo].[RequestsServiceStatusesHistory]  WITH CHECK ADD  CONSTRAINT [FK_RequestsServiceStatusesHistory_RequestServiceStatuses] FOREIGN KEY([request_service_status_id])
	REFERENCES [dbo].[RequestServiceStatuses] ([request_service_status_id])

	ALTER TABLE [dbo].[RequestsServiceStatusesHistory] CHECK CONSTRAINT [FK_RequestsServiceStatusesHistory_RequestServiceStatuses]

	ALTER TABLE [dbo].[RequestsServiceStatusesHistory]  WITH CHECK ADD  CONSTRAINT [FK_RequestsServiceStatusesHistory_Users] FOREIGN KEY([user_id])
	REFERENCES [dbo].[Users] ([user_id])

	ALTER TABLE [dbo].[RequestsServiceStatusesHistory] CHECK CONSTRAINT [FK_RequestsServiceStatusesHistory_Users]

	GO

	-- Если триггер уже существует - удаляем его
	IF OBJECT_ID ('RequestDataVersionIncrease_RequestsServiceStatusesHistory', 'TR') IS NOT NULL
		DROP TRIGGER RequestDataVersionIncrease_RequestsServiceStatusesHistory;

	GO

	-- Увеличивает версию данных обращения при добавлении, изменении или удалении записей этой таблицы
	CREATE TRIGGER [dbo].[RequestDataVersionIncrease_RequestsServiceStatusesHistory]
		ON [dbo].[RequestsServiceStatusesHistory]
		AFTER INSERT,DELETE,UPDATE
		NOT FOR REPLICATION -- Если таблица изменяется средствами репликации, триггер не будет исполняться
	AS 
	BEGIN
		-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
		SET NOCOUNT ON;

		-- Определяем операцию триггера
		declare @TriggerOperation nvarchar(6);
		declare @InsertPresent bit;
		declare @DeletePresent bit;

		SET @InsertPresent = 
			CASE
				WHEN EXISTS(SELECT * FROM [inserted]) THEN 1
				ELSE 0
			END

		SET @DeletePresent = 
			CASE
				WHEN EXISTS(SELECT * FROM [deleted]) THEN 1
				ELSE 0
			END

		SET @TriggerOperation = 
			CASE
				WHEN @InsertPresent = 1 AND @DeletePresent = 1 THEN
					'UPDATE'
				WHEN @InsertPresent = 0 AND @DeletePresent = 1 THEN
					'DELETE'
				WHEN @InsertPresent = 1 AND @DeletePresent = 0 THEN
					'INSERT'
				ELSE NULL
			END

		-- Определяем идентификатор обращения, версию данных которого необходимо увеличить
		declare @RequestID uniqueidentifier;	
		IF @InsertPresent = 1
		BEGIN
			SET @RequestID = (SELECT TOP 1 [request_id] FROM [inserted])
		END
			ELSE
		BEGIN
			SET @RequestID = (SELECT TOP 1 [request_id] FROM [deleted])
		END
	
		IF @RequestID IS NOT NULL
		BEGIN
			UPDATE [Requests]
			SET [data_version] = [data_version] + 1
			WHERE
				[request_id] = @RequestID
		END
	END

COMMIT TRAN DBUPDATE
28 мар 16, 18:18    [18987861]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
SolidSnake
Но в скрипте есть же коммит, что не так с добавлением триггера?
END
GO

COMMIT TRAN DBUPDATE
28 мар 16, 18:23    [18987881]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
SolidSnake
Member

Откуда:
Сообщений: 98
Спасибо :)
28 мар 16, 18:27    [18987897]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
iap
Member

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

работать-то будет правильно только для одной записи.
А количество записей не контролируется.
28 мар 16, 20:22    [18988289]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
SolidSnake
Member

Откуда:
Сообщений: 98
iap
SolidSnake,
работать-то будет правильно только для одной записи.
А количество записей не контролируется.
Это о скрипте триггера?
28 мар 16, 21:25    [18988525]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31778
SolidSnake
iap
SolidSnake,
работать-то будет правильно только для одной записи.
А количество записей не контролируется.
Это о скрипте триггера?
Ага.
SET @RequestID = (SELECT TOP 1 [request_id] FROM [inserted])

А остальные записи как, будут игнорироваться, которые кроме "TOP 1"?
И для них "[data_version] = [data_version] + 1" не произойдёт?
28 мар 16, 21:43    [18988570]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
SolidSnake
Member

Откуда:
Сообщений: 98
alexeyvg
SolidSnake
пропущено...
Это о скрипте триггера?
Ага.
SET @RequestID = (SELECT TOP 1 [request_id] FROM [inserted])

А остальные записи как, будут игнорироваться, которые кроме "TOP 1"?
И для них "[data_version] = [data_version] + 1" не произойдёт?
Триггер предусматривался для событий, где гарантированно меняется 1 запись с одним ИД.
Нужно было выцепить ИД и обновить счетчик модифицированности.
По логике приложения пользователь не может одной операцией изменить сразу 2 записи, будет 2 независимые операции и на обе сработает триггер.

Но для универсальности, стоит потестировать и другие сценарии, подретушировать скрипт.
Спасибо за наводку)
28 мар 16, 23:36    [18988857]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
iljy
Member

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

крайне порочная логика. Первое и главное правило написания надежного кода: никогда не делайте неявных предположений о входных данных. Если триггер рассчитан на обработку одной записи, то в нем должна стоять проверка количества и откат транзакции, если записей больше одной. Но в данном случае это будет просто глупо, потому что универсальность триггера обеспечивается очень легко:

CREATE TRIGGER [dbo].[RequestDataVersionIncrease_RequestsServiceStatusesHistory]
		ON [dbo].[RequestsServiceStatusesHistory]
		AFTER INSERT,DELETE,UPDATE
		NOT FOR REPLICATION -- Если таблица изменяется средствами репликации, триггер не будет исполняться
AS 
BEGIN
	SET NOCOUNT ON;

        UPDATE r
        SET [data_version] = [data_version] + 1
        FROM [Requests] r JOIN inserted i on r.[request_id] = i.[request_id];
END
29 мар 16, 00:16    [18988940]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31778
SolidSnake
Триггер предусматривался для событий, где гарантированно меняется 1 запись с одним ИД.
Нужно было выцепить ИД и обновить счетчик модифицированности.
По логике приложения пользователь не может одной операцией изменить сразу 2 записи, будет 2 независимые операции и на обе сработает триггер.
Отлично, так тоже нормально.
Но в этом случае нужно было, как намекнул iap, генерить ошибку, если в inserted или deleted больше одной записи.

Ну или писать триггеры нормально, как это делается для языков работы с множествами, а не пытаться переложить идеологию VisualBasic, C или Java на SQL
Получится втрое короче, вдесятеро понятнее, и концептуально правильнее. Вот как у iljy постом выше.
29 мар 16, 09:52    [18989483]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
SolidSnake
Member

Откуда:
Сообщений: 98
Все верно, нужно стараться писать универсальный код и это относится не только к TSQL.
Но бывает, что-то упускаешь, работая одновременно в целом зоопарке инструментов, языков и т.п. и все в рамках одного проекта.

Триггер длинный не просто так, в нем присутствует мой уровень абстракции (@TriggerOperation), который задействован в других триггерах.
Возможно есть более правильный способ получить вид операции, думаю это не критично.
Удобно иметь общую структуру в однотипных запросах, так проще не терять контекст в голове.
С точки зрения оптимизации, лишний код можно закомментировать или удалить в тех триггерах, где он не нужен, правда выигрыш в производительности будет чисто формальный, такова специфика таблиц, на которые повешен триггер.
Уже добавил задачу рефакторинга и корректировки триггеров :)
Кстати на удобство чтения, общий блок не сильно влияет, там написано что делает этот кусок, можно сразу переходить к основной части запроса внизу.

+ В общем по коду TSQL
Еще не люблю сокращать имена таблиц.
Слишком заумные и сокращенные скрипты хоть и визуально короче, но через пол года вспоминать что там намудрил гораздо тяжелее.
Если короткая или заумная версия запроса обеспечивает на порядок более высокую скорость исполнения, то игра стоит свеч,
но надо грамотно тратить время на разработку, иначе можно остаться с жутко оптимизированными запросами, которые будет не для чего исполнять :)
29 мар 16, 14:40    [18991442]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
iap
Member

Откуда: Москва
Сообщений: 47045
SolidSnake
Еще не люблю сокращать имена таблиц.
Это про алиасы что ли?
29 мар 16, 14:46    [18991478]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
Glory
Member

Откуда:
Сообщений: 104760
SolidSnake
Все верно, нужно стараться писать универсальный код и это относится не только к TSQL.

Не нужно. Особенно триггера.
29 мар 16, 14:49    [18991499]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
iap
Member

Откуда: Москва
Сообщений: 47045
Glory
SolidSnake
Все верно, нужно стараться писать универсальный код и это относится не только к TSQL.

Не нужно. Особенно триггера.
Под универсальностью он понимает обработку в триггере более одной строки.
29 мар 16, 14:50    [18991509]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Glory
SolidSnake
Все верно, нужно стараться писать универсальный код и это относится не только к TSQL.

Не нужно. Особенно триггера.

угу, потом это всё выливается в страдания при тестировании :)

автор
Это про алиасы что ли?

наверное... про:
[FK_RequestsServiceStatusesHistory_RequestServiceStatuses]

странно что FK сократил... а так да индусы такое любят :)
29 мар 16, 14:52    [18991519]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
Glory
Member

Откуда:
Сообщений: 104760
iap
Под универсальностью он понимает обработку в триггере более одной строки.

А по-моему - один триггер на все операции
Сначала пишет триггер на все команды, а в тексте триггера тратит время на выяснение, на какую команду сработал триггер. И назыввает это универсальностью.
29 мар 16, 14:53    [18991529]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31778
SolidSnake
Триггер длинный не просто так, в нем присутствует мой уровень абстракции (@TriggerOperation), который задействован в других триггерах.
Возможно есть более правильный способ получить вид операции, думаю это не критично.
Удобно иметь общую структуру в однотипных запросах, так проще не терять контекст в голове.

Это не противоречит необходимости думать категориями множеств, работая с РСУБД.
Этот заголовок, "общий блок" - нормальный, он и при множестве будет работать правильно.
А вот сама обработка с ошибкой.
SolidSnake
Но бывает, что-то упускаешь, работая одновременно в целом зоопарке инструментов, языков и т.п. и все в рамках одного проекта.
Универсальный специалист неминуемо знает предмет хуже узкого специалиста :-(
SolidSnake
Еще не люблю сокращать имена таблиц.
Имена таблиц могут быть длинные, но нужно использовать алиасы.
SolidSnake
Если короткая или заумная версия запроса обеспечивает на порядок более высокую скорость исполнения, то игра стоит свеч,
но надо грамотно тратить время на разработку, иначе можно остаться с жутко оптимизированными запросами, которые будет не для чего исполнять :)
Специалист по SQL напишет такие примитивные запросы без раздумий, оптимально, понятно. Это ваше "тратить время" - следствие универсальности, и как следствие незнания, а не сложности предмета. Ну или просто опыта нужно поднабраться. Потому что реально всё, что вам писали в этом топике, очень примитивно, самый начальный уровень для программиста БД.
29 мар 16, 15:02    [18991602]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
SolidSnake
Member

Откуда:
Сообщений: 98
Имена таблиц могут быть длинные, но нужно использовать алиасы.
Имел в виду алиасы, в запросах пишу алиасы с целью сделать наименование более понятным, а не коротким.

автор
Ну или просто опыта нужно поднабраться. Потому что реально всё, что вам писали в этом топике, очень примитивно, самый начальный уровень для программиста БД.
Опыт набирать можно бесконечно в любой области.
В спойлере написаны мысли "в общем" про запросы, а не конкретно о листинге из шапки.
29 мар 16, 16:04    [18991970]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
SolidSnake
Имена таблиц могут быть длинные, но нужно использовать алиасы.
Имел в виду алиасы, в запросах пишу алиасы с целью сделать наименование более понятным, а не коротким.

автор
Ну или просто опыта нужно поднабраться. Потому что реально всё, что вам писали в этом топике, очень примитивно, самый начальный уровень для программиста БД.
Опыт набирать можно бесконечно в любой области.
В спойлере написаны мысли "в общем" про запросы, а не конкретно о листинге из шапки.

так может набраться опыта, а потом нести свои умные мысли в массы
особенно
автор
Слишком заумные и сокращенные скрипты хоть и визуально короче, но через пол года вспоминать что там намудрил гораздо тяжелее.
в sql написать, что-то заумное и не читаемое можно только сильно задавшись именно такой целью
29 мар 16, 16:11    [18992027]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
iap
Member

Откуда: Москва
Сообщений: 47045
SolidSnake
в запросах пишу алиасы с целью сделать наименование более понятным, а не коротким.
Разве для этого нужны алиасы?
В основном для того, чтобы к одной и той же таблице можно было обратиться более одного раза как к разным таблицам.
В том числе и в коррелированных подзапросах.
Или для того, чтобы в запросе было сразу ясно, в какой таблице находится то или иное поле.
29 мар 16, 16:13    [18992032]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
SolidSnake
Member

Откуда:
Сообщений: 98
iap
SolidSnake
в запросах пишу алиасы с целью сделать наименование более понятным, а не коротким.
Разве для этого нужны алиасы?
В основном для того, чтобы к одной и той же таблице можно было обратиться более одного раза как к разным таблицам.
В том числе и в коррелированных подзапросах.
Или для того, чтобы в запросе было сразу ясно, в какой таблице находится то или иное поле.

Ну так можно написать алиас t1, а можно длинно и понятно)
29 мар 16, 16:53    [18992330]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
Glory
Member

Откуда:
Сообщений: 104760
SolidSnake
Ну так можно написать алиас t1, а можно длинно и понятно)

Разве для "длинно и понятно" предназначено не имя таблицы ?
Например [RequestsServiceStatusesHistory]

Сообщение было отредактировано: 29 мар 16, 16:55
29 мар 16, 16:54    [18992337]     Ответить | Цитировать Сообщить модератору
 Re: Установка триггера внутри транзакции, незавершенная транзакция.  [new]
iljy
Member

Откуда:
Сообщений: 8711
SolidSnake
Ну так можно написать алиас t1, а можно длинно и понятно)


Можно t1, а можно i для таблицы inserted, r для Requests, rr для вхождения Requests в корелированный подзапрос и т.д. Будет коротко и понятно, и не надо будет при написании запроса постоянно подсматривать, как же в текущем запросе называется третье вхождение таблицы StokovyeOstakiPoProvodkamFormyStoPjatsotTridcatChetyre.
29 мар 16, 17:16    [18992462]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить