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

Откуда: Гондурас
Сообщений: 1034
Уважаемый форум!

Преамбула: с СКЛ2008 сервера отправляется емыло при удалении строк в таблице (триггер).
Амбула: необходимо включить с сообщение результат запроса. Следующий код триггера:
USE [DB]
GO
/****** Object:  Trigger [dbo].[Watch_Dog]    Script Date: 11/21/2011 14:09:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Watch_Dog]	 
   ON  [dbo].[tbl_SRA] 
   AFTER DELETE
AS
	DECLARE @msg varchar(100), @cnt varchar(5), @cnt_check varchar(5), @mpno varchar(11), @stg varchar(5)
BEGIN
	SET NOCOUNT ON;
	Set @cnt=(SELECT COUNT(cpa_MOPI_No) FROM Deleted)
	Set @cnt_check=(SELECT COUNT(cpa_MOPI_No) FROM Deleted WHERE(cpa_Status='OL'))

	IF @cnt>0
	BEGIN
		IF @cnt>1 and @cnt_check=0
			Set @msg='Deleted ' + @cnt + ' lines from SRA table: '
			
		IF @cnt=1
			IF @cnt_check=1
				BEGIN
					Set @mpno=(SELECT cpa_MOPI_No FROM Deleted)
					Set @msg='Deleted ' + @cnt + ' VI OI-line from SRA table for MOPI No. ' + @mpno + '.'
				END
			ELSE
				BEGIN
					Set @stg=((SELECT cpa_Stage FROM Deleted))
					Set @mpno=(SELECT cpa_MOPI_No FROM Deleted)
					Set @msg='Deleted ' + @cnt + ' ' + @stg + '-line from SRA table for MOPI No. ' + @mpno + '.'
				END
				
		BEGIN
			EXEC msdb.dbo.sp_send_dbmail
					@profile_name = 'DB',
					@recipients = 'blabla@test.com',
					@body_format='HTML',
                                        @execute_query_database='DB',
					@query='SELECT cpa_MOPI_No, cpa_Stage, cpa_Case_From, cpa_Case_To FROM Deleted',
					@append_query_error=1,
					@body = @msg,
					@importance ='High',
					@subject = 'Lines deleted!';
		END
	END
END

В сообщении об ошибке пишет, что не может найти объект Deleted. Подскажите, как обойти этот момент? Спасибо заранее!
21 ноя 11, 18:00    [11633973]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
IFK, во время выполнения запроса, заданного в параметре @query, виртуальную таблицу deleted уже не видно. вставляйте данные куда-то ещё, откуда можно будет прочитать
21 ноя 11, 18:13    [11634034]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1034
Shakill
IFK, во время выполнения запроса, заданного в параметре @query, виртуальную таблицу deleted уже не видно. вставляйте данные куда-то ещё, откуда можно будет прочитать


Через временную таблицу? А не будет медленно?
Может есть еще идеи?
21 ноя 11, 18:14    [11634048]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
IFK
Shakill
IFK, во время выполнения запроса, заданного в параметре @query, виртуальную таблицу deleted уже не видно. вставляйте данные куда-то ещё, откуда можно будет прочитать


Через временную таблицу? А не будет медленно?
Может есть еще идеи?


попробуйте и узнаете

ещё идеи - можно генерировать параметр @query не как обращение к deleted, а как select .. from (values ..), где указывать собственно содержимое deleted, но тогда появляются ограничения по количеству записей в values и по объему запроса
21 ноя 11, 18:22    [11634090]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
да чего уж там, можно вообще не использовать @query, а в триггере генерить тело письма целиком
21 ноя 11, 18:23    [11634095]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1034
Shakill
да чего уж там, можно вообще не использовать @query, а в триггере генерить тело письма целиком


Попробовал так:
BEGIN
	SET NOCOUNT ON;
	Set @cnt=(SELECT COUNT(cpa_MOPI_No) FROM Deleted)
	Set @cnt_check=(SELECT COUNT(cpa_MOPI_No) FROM Deleted WHERE(cpa_Status='OL'))
	SELECT * INTO CPA_DELETED FROM Deleted

	IF @cnt>0
	BEGIN
		IF @cnt>1 and @cnt_check=0
			Set @msg='Deleted ' + @cnt + ' lines from SRA table: '
			
		IF @cnt=1
			IF @cnt_check=1
				BEGIN
					Set @mpno=(SELECT cpa_MOPI_No FROM Deleted)
					Set @msg='Deleted ' + @cnt + ' VI OI-line from SRA table for MOPI No. ' + @mpno + '.'
				END
			ELSE
				BEGIN
					Set @stg=((SELECT cpa_Stage FROM Deleted))
					Set @mpno=(SELECT cpa_MOPI_No FROM Deleted)
					Set @msg='Deleted ' + @cnt + ' ' + @stg + '-line from SRA table for MOPI No. ' + @mpno + '.'
				END
				
		BEGIN
			EXEC msdb.dbo.sp_send_dbmail
					@profile_name = 'MOMA',
					@recipients = 'email1@domain.com; email2@domain.com',
					@copy_recipients='email3@domain.com; email4@domain.com',
					@body_format='HTML',
					@body = @msg,
					@query='SELECT cpa_MOPI_No, cpa_Stage, cpa_Case_From, cpa_Case_To FROM CPA_DELETED',
					@append_query_error=1,
					@importance ='High',
					@subject = 'Lines deleted!';
		END
	END
END


Результат - те же матюки, только теперь не видно таблицы CPA_DELETED. В чем теперь-то трабла?

Сообщение было отредактировано: 21 авг 12, 12:40
21 ноя 11, 18:40    [11634148]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
IFK
Результат - те же матюки, только теперь не видно таблицы CPA_DELETED. В чем теперь-то трабла?
А чего это у вас @execute_query_database не задано???

Сервер должен искать вашу таблицу по всем базам?

Кстати, триггер у вас одноразовый. После первого срабатывания будет генерить ошибку.
21 ноя 11, 18:45    [11634165]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1034
alexeyvg,

По-моему, ему пофиг, задана ли база или нет, в руководстве сказано, что если этот параметр не задан, то используется текущая база, а она задана вверху: USE DB. Я просто этот кусок во второй раз здесь не привел.

А что значит "одноразовый"? Что Вы имели в виду?
23 ноя 11, 13:30    [11644043]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
IFK
А что значит "одноразовый"? Что Вы имели в виду?


SELECT * INTO CPA_DELETED FROM Deleted
эта конструкция работает только если таблицы CPA_DELETED не существует
23 ноя 11, 13:33    [11644071]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1034
Shakill
IFK
А что значит "одноразовый"? Что Вы имели в виду?


SELECT * INTO CPA_DELETED FROM Deleted
эта конструкция работает только если таблицы CPA_DELETED не существует


Ах, да, пардон, забыл. Я дополнил код еще предложением в конце
 DROP TABLE CPA_DELETED

Проблема в том, что после такой конструкции сервер просто завис. :(

Еще вопрос: можно как-то отладить триггер не в реальном режиме работы базы?
23 ноя 11, 13:39    [11644124]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
IFK
По-моему, ему пофиг, задана ли база или нет, в руководстве сказано, что если этот параметр не задан, то используется текущая база, а она задана вверху: USE DB. Я просто этот кусок во второй раз здесь не привел.
Не нашёл в документации ссылки на использование текущей базы.

Ну и в любом случае USE DB "наверху" не устанавливает текущую базу при выполнении триггера. Зачем полагаться на случайности, какая там база окажется текущей при срабатывании триггера? Укажите полное имя либо используйте @execute_query_database

Ну и владельца всегда указывайте. Мало-ли, какой контекст используется системными процедурами (хотя думаю, текущий, но опять-же, зачем тратить время на эксперименты?)
23 ноя 11, 13:54    [11644302]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
IFK
Ах, да, пардон, забыл. Я дополнил код еще предложением в конце
 DROP TABLE CPA_DELETED
Так посмотрите, чего это он завис.
IFK
Еще вопрос: можно как-то отладить триггер не в реальном режиме работы базы?
В смысле, эмулировать работу сиквела на телефоне? :-)

Как это - не в реальном? Скажите точно, что именно требуется.
23 ноя 11, 13:56    [11644326]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
IFK
Ах, да, пардон, забыл. Я дополнил код еще предложением в конце
 DROP TABLE CPA_DELETED
И в любом случае ничего хорошего. При срабатывании триггера в 2-х экземплярах будет ошибка. Сенрвер то выполняет запросы не строго по одному из очереди. Конечно, наверняка у вас эта таблица меняется редко, но всё таки нехорошо.

И вообще, создание и удаление не-временных объектов в транзакции и в триггере - плохая идея.

Лучьше использовать временную таблицу.
23 ноя 11, 14:00    [11644381]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1034
alexeyvg
IFK
По-моему, ему пофиг, задана ли база или нет, в руководстве сказано, что если этот параметр не задан, то используется текущая база, а она задана вверху: USE DB. Я просто этот кусок во второй раз здесь не привел.
Не нашёл в документации ссылки на использование текущей базы.

Ну и в любом случае USE DB "наверху" не устанавливает текущую базу при выполнении триггера. Зачем полагаться на случайности, какая там база окажется текущей при срабатывании триггера? Укажите полное имя либо используйте @execute_query_database

Ну и владельца всегда указывайте. Мало-ли, какой контекст используется системными процедурами (хотя думаю, текущий, но опять-же, зачем тратить время на эксперименты?)


Конечный итог - триггер на тестовую таблицу вешает сервер почти наглухо:
USE [DB]
GO
/****** Object:  Trigger [dbo].[test_trigger]    Script Date: 11/23/2011 11:01:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[test_trigger] 
   ON  [dbo].[CPA_UPDATE]
   AFTER DELETE
AS 
	DECLARE @msg varchar(500), @cnt varchar(5), @cnt_check varchar(5), @mpno varchar(11), @stg varchar(5), @case_f varchar(6), @case_t varchar(6)
BEGIN
	SET NOCOUNT ON;
	Set @cnt=(SELECT COUNT(cpa_MOPINo) FROM Deleted)
	SELECT * INTO CPA_TEST FROM Deleted

	IF @cnt>0
	BEGIN
		Set @msg='Deleted ' + @cnt + ' lines from SRA table '
						
		EXEC msdb.dbo.sp_send_dbmail
				@profile_name = 'DB',
				@recipients = test@test.com',
				@body = @msg,
				@execute_query_database='DB',
				@query='SELECT cpa_MOPI_No, cpa_Stage, cpa_Case_From, cpa_Case_To FROM CPA_TEST',
				@append_query_error=1,
				@importance ='High',
				@subject = 'Lines deleted!';
	END
	
	DROP TABLE CPA_TEST

END

В чем трабла-то??? Я удаляю всего две строки...
23 ноя 11, 14:05    [11644439]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
Glory
Member

Откуда:
Сообщений: 104751
IFK
В чем трабла-то??? Я удаляю всего две строки...

Трабла в том, что вы почему считаете, что запрос sp_send_dbmail выполняется в том же коннекте, что и ваш триггер
А триггер ваш между прочим выполняется в транзакции. В которой и блокирует CPA_TEST.
23 ноя 11, 14:09    [11644487]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1034
Glory
IFK
В чем трабла-то??? Я удаляю всего две строки...

Трабла в том, что вы почему считаете, что запрос sp_send_dbmail выполняется в том же коннекте, что и ваш триггер
А триггер ваш между прочим выполняется в транзакции. В которой и блокирует CPA_TEST.


Хм. Т.е. очевидное решение - использовать два триггера? Один на заполнение временной таблицы, а второй на отсылку мыла? Так, что ли?
23 ноя 11, 15:15    [11645236]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
Glory
Member

Откуда:
Сообщений: 104751
IFK
Т.е. очевидное решение - использовать два триггера? Один на заполнение временной таблицы, а второй на отсылку мыла? Так, что ли?

Очевидно, что нужно сначала понять, что триггера есть на события INSERT/UPDATE/DELETE а не на "заполнение временной таблицы" или "отсылку мыла"
23 ноя 11, 16:09    [11645947]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Вам явно делать нечего.
DECLARE @Data NVarChar(max)
SET @Data = (SELECT * FROM Deleted FOR XML Path('Del')) -- Или любым другим способом
EXEC msdb.dbo.sp_send_dbmail
	 @profile_name	= 'DB'
	,@recipients	= 'test@test.com'
	,@subject	= @msg
	,@body		= @Data;
23 ноя 11, 16:11    [11645979]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
А ещё можно логирование вкючить на таблу и на соостветствующий event отсылку письма повесить.
23 ноя 11, 16:13    [11646028]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1034
Mnior
А ещё можно логирование вкючить на таблу и на соостветствующий event отсылку письма повесить.


А про это где почитать по-подробнее? Про логирование, я имею в виду. Спасибо!
23 ноя 11, 16:37    [11646394]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
IFK
В чем трабла-то??? Я удаляю всего две строки...
Ну сделайте FROM CPA_TEST (nolock)

Но код очень плохой даже для не-специалиста про сиквелу.
23 ноя 11, 17:06    [11646735]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1034
alexeyvg
IFK
В чем трабла-то??? Я удаляю всего две строки...
Ну сделайте FROM CPA_TEST (nolock)

Но код очень плохой даже для не-специалиста про сиквелу.


Да я уже понял свою ошибку. Учиться приходится на ходу. :(
Вот только не совсем понимаю, как побороть свою изначальную проблему. :(
23 ноя 11, 17:14    [11646809]     Ответить | Цитировать Сообщить модератору
 Re: Емыло с результатами запроса в сообщении  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
IFK,
Отслеживание изменений данных
23 ноя 11, 17:45    [11647073]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить