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

Откуда:
Сообщений: 114
Добрый день.
Возможно мой вопрос уже задавался на этом форуме, но ответ я на него не нашел.

Имею:

1.Таблица Users
ID int,
Name Varchar(70)
2. Таблица Departments
ID int,
Dep Varchar(50)
3. Таблица Users_Departments
User_ID int,
Dep_ID

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

Мне нужно отслеживать изменения в двух таблицах: Users и Users_Departments.

Из приложения у меня редактируется следующим образом:
1. Открывается транзакция
2. Обновляю данные в таблице Users
3. Обновляю данные в таблице Users_Departments
4. В зависимости от ситуации или Commit или Rollback

Сделал триггер на таблице Users :

При срабатывании триггера я могу получить данные из таблицы Users_Departments которые еще не успели обновиться.

Могу ли я как-нибудь получить изменившиеся данные из таблицы Users_Departments?

Или тут правильнее не триггер использовать, а писать полноценную процедуру?
11 ноя 13, 17:03    [15110453]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Tauzer
2. Обновляю данные в таблице Users
3. Обновляю данные в таблице Users_Departments

Непонятна логика работы. "Обновляю" — это UPDATE? Какие именно поля обновляются?
11 ноя 13, 17:11    [15110511]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Tauzer
Member

Откуда:
Сообщений: 114
например,
1.Открываю транзакцию
2. Если в приложении на форме были изменения в имени, то делаю
UPDATE Users SET Name='" & MyValue1 & "'

3.Если на форме были изменения в отделах, то сначала
DELETE FROM Users_Departments WHERE USER_ID=Users.id

После
INSERT INTO Users_Departments (User_ID,Dep_ID) VALUES (Users.id,My.Value2)


Переменные Users.id,MyValue1 и MyValue2 соответственно в приложении подставляются.
11 ноя 13, 17:21    [15110596]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Tauzer
Member

Откуда:
Сообщений: 114
Да, забыл про завершение транзакции
11 ноя 13, 17:22    [15110600]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Tauzer
UPDATE Users SET Name='" & MyValue1 & "'

DELETE FROM Users_Departments WHERE USER_ID=Users.id

INSERT INTO Users_Departments (User_ID,Dep_ID) VALUES (Users.id,My.Value2)

Не очень понятно, для какого "отслеживания изменений" вам здесь понадобился триггер.

Чем не подошёл следующий сценарий?
В одном и том же коннекте:
— открыть транзакцию;
— выполнить эти команды;
— завершить транзакцию.
11 ноя 13, 17:31    [15110672]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Tauzer
Member

Откуда:
Сообщений: 114
для какого "отслеживания изменений" - для любого.
Если Вася изменился на Диму, если у Васи изменился отдел.
Мне на почту автоматом приходит оповещение.
11 ноя 13, 17:37    [15110726]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Tauzer
Сделал триггер на таблице Users :
Где триггер-то?
Может, оформите вопрос как положено?


P.S. Полное впечатление, что вся тема на птичьем языке.
11 ноя 13, 17:40    [15110749]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Tauzer
Member

Откуда:
Сообщений: 114
Хорошо, я пытался упростить вопрос, не хотел много и нудно писать.

Вот триггер:

ALTER TRIGGER [dbo].[Stone_Users_Update] ON [dbo].[Stone_Users]
FOR UPDATE
AS

SET NOCOUNT ON

BEGIN
	INSERT INTO Stone_Users_Log (Event_Date,Event_Creator,Event_Action,[User_ID],Name,Phone,Email,Mobile,Room,Department_ID,Job,BDay,Dismiss,Status_ID,Status_End,DomainUserName)
	SELECT getdate(), SYSTEM_USER, 'U',ID,Name,Phone,Email,Mobile,Room,Department_ID,Job,BDay,Dismiss,Status_ID,Status_End,DomainUserName
	FROM INSERTED

 	
DECLARE @tableHTML  NVARCHAR(MAX) ;

declare @UID Int;
declare @Path NVARCHAR(MAX) ;

SET @UID=(SELECT ID FROM INSERTED);

set @Path='http://www/modules/Stone/vCard.php?uid='+CAST(@UID AS NVARCHAR(MAX));

SET @tableHTML =
    N'<H1>Обновление информации .</H1><br>' +
	N'<H1>Была выполнена инструкция UPDATE.</H1>' +
    N'<table border="1" cellpadding="4" cellspacing="1">' +
	N'<tr><th>Состояние</th><th>Дата</th><th>SYSTEM_USER</th>' +
	N'<th>User_ID</th><th>Имя</th><th>Телефон</th>' +
	N'<th>Почта</th><th>Мобильный</th><th>Комната</th>' +
	N'<th>Подразделение</th><th>Организации</th><th>Направление</th><th>Job</th><th>День рождения</th>' +
	N'<th>Статус</th><th>Окончание статуса</th><th>DomainUserName</th></tr>' +
	CAST((   SELECT td = 'Было',       '',
					td = getdate(),       '',
					td = SYSTEM_USER,       '',
					td = ID,       '',
					td = Name,       '',
					td = IsNULL(Phone,'NULL'),'',
					td = IsNULL(Email,'NULL'),       '',
					td = IsNULL(Mobile,'NULL'),       '',
					td = IsNULL(Room,'NULL'),       '',
					td = IsNULL((select department from stone_departments where id=Department_ID),'NULL'),       '',
					td = (SELECT Organization  FROM Stone_Organizations WHERE ID IN(SELECT Organization_ID FROM Stone_Users_Organizations WHERE [USER_ID]=@UID) FOR XML PATH('table'), TYPE),       '',
					td = (SELECT Direction  FROM Stone_Directions WHERE ID IN(SELECT Direction_ID FROM Stone_Users_Directions WHERE [USER_ID]=@UID) FOR XML PATH('table'), TYPE),       '',
					td = IsNULL(Job,'NULL'),       '',
					td = IsNULL(BDay,convert(smalldatetime,'',111)),       '',
					td = IsNULL((SELECT [Status] FROM Stone_Users_Status WHERE ID=Status_ID),'NULL'),       '',
					td = IsNULL(Status_End,convert(smalldatetime,'',111)),       '',
					td = IsNULL(DomainUserName,'NULL'),       ''
				FROM DELETED					
								FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) +
	
	CAST((   SELECT td = 'Стало',       '',
					td = getdate(),       '',
					td = SYSTEM_USER,       '',
					td = ID,       '',
					td = Name,       '',
					td = IsNULL(Phone,'NULL'),'',
					td = IsNULL(Email,'NULL'),       '',
					td = IsNULL(Mobile,'NULL'),       '',
					td = IsNULL(Room,'NULL'),       '',
					td = IsNULL((select department from stone_departments where id=Department_ID),'NULL'),       '',
					td = 'Смотрим на сайте',       '',
					td = 'Смотрим на сайте',       '',
					td = IsNULL(Job,'NULL'),       '',
					td = IsNULL(BDay,convert(smalldatetime,'',111)),       '',
					td = IsNULL((SELECT [Status] FROM Stone_Users_Status WHERE ID=Status_ID),'NULL'),       '',
					td = IsNULL(Status_End,convert(smalldatetime,'',111)),       '',
					td = IsNULL(DomainUserName,'NULL'),       ''
				FROM INSERTED					

				FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) +
	
	 N'</table><br><a href="' + @Path + '" target="blank">' + @Path + '</a>';


	EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'profile1',
	@recipients = 'me@me.ru',
	@copy_recipients='me@me.ru',
	@body = @tableHTML ,
	@body_format = 'HTML' ,
    @subject = 'Обновление информации .' ;
	

END

SET NOCOUNT ON


Меня интересует, могу ли я получить измененные данные из таблицы Stone_Organizations из данного триггера и заменить строки 'Смотрим на сайте' на измененные данные, при условии , что
UDATE таблицы Stone_Organizations идет после завершения UPDATE Stone_Users, и что оба UPDATE идут внутри одной транзакции?
11 ноя 13, 17:48    [15110832]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Tauzer
для какого "отслеживания изменений" - для любого.
Если Вася изменился на Диму, если у Васи изменился отдел.
Мне на почту автоматом приходит оповещение.

Сделайте три триггера:
— на апдейт таблицы Users
— на удаление из таблицы Users_Departments
— на вставку в таблицу Users_Departments

Последние два триггера можно объединить в один.
11 ноя 13, 17:48    [15110838]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Tauzer
Member

Откуда:
Сообщений: 114
Гость333
Сделайте три триггера:
— на апдейт таблицы Users
— на удаление из таблицы Users_Departments
— на вставку в таблицу Users_Departments

Последние два триггера можно объединить в один.


Тогда нужно будет генерить три разных письма.Неудобно.Я пока склоняюсь к тому, что вместо триггера нужно писать хранимку, которая будет читать существующие данные до обновления, потом обновлять данные, потом читать изменившиеся данные, а потом уже слать письмо.
11 ноя 13, 17:51    [15110855]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Tauzer
Хорошо, я пытался упростить вопрос, не хотел много и нудно писать.

Вот триггер:

ALTER TRIGGER [dbo].[Stone_Users_Update] ON [dbo].[Stone_Users]
FOR UPDATE
AS

SET NOCOUNT ON

BEGIN
	INSERT INTO Stone_Users_Log (Event_Date,Event_Creator,Event_Action,[User_ID],Name,Phone,Email,Mobile,Room,Department_ID,Job,BDay,Dismiss,Status_ID,Status_End,DomainUserName)
	SELECT getdate(), SYSTEM_USER, 'U',ID,Name,Phone,Email,Mobile,Room,Department_ID,Job,BDay,Dismiss,Status_ID,Status_End,DomainUserName
	FROM INSERTED

 	
DECLARE @tableHTML  NVARCHAR(MAX) ;

declare @UID Int;
declare @Path NVARCHAR(MAX) ;

SET @UID=(SELECT ID FROM INSERTED);
С этого места можно уже не смотреть.
Поделитесь, как в скалярную переменную типа int Вы загоняете миллион значений из inserted?
11 ноя 13, 17:53    [15110880]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Tauzer
Member

Откуда:
Сообщений: 114
У меня "загоняется" одно единственное и интересующее меня значение.

К сообщению приложен файл. Размер - 6Kb
11 ноя 13, 18:01    [15110950]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Tauzer
У меня "загоняется" одно единственное и интересующее меня значение.
Это откуда следует?
Если одно из клиентских приложений проапдейтит-таки 2 записи одним UPDATEом, то что будет?
Если это случится уже после Вашего увольнения, то что будут делать оставшиеся?
Зачем закладывать такую замедленную говнобомбу в своих программах?

P.S. Я много времени сейчас трачу на чистку вот таких вот "авгиевых конюшен".
Так что знаю что говорю.
11 ноя 13, 18:10    [15111007]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Tauzer
Member

Откуда:
Сообщений: 114
iap
Это откуда следует?
Если одно из клиентских приложений проапдейтит-таки 2 записи одним UPDATEом, то что будет?
Если это случится уже после Вашего увольнения, то что будут делать оставшиеся?
Зачем закладывать такую замедленную говнобомбу в своих программах?

P.S. Я много времени сейчас трачу на чистку вот таких вот "авгиевых конюшен".
Так что знаю что говорю.


Если я уволюсь, то останутся довольно квалифицированные разработчики, которые , если будут менять текст моего приложения, и тестировать результаты, то разберутся что к чему.
Если Вы увидели ошибку в моем тексте, то подскажите как правильнее написать - буду благодарен.А просто так рассуждать на тему "говнобомбы" - данный форум не для этого.
11 ноя 13, 18:24    [15111086]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Tauzer
Если Вы увидели ошибку в моем тексте, то подскажите как правильнее написать - буду благодарен.

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

Но рассылка почты из триггера — это далеко не best practice. А в вашем случае ещё и не решает поставленную задачу — невозможно взять изменения из таблицы Users_Departments, т.к. этих изменений ещё нет в базе.

Можно сделать отдельный асинхронный процесс, который будет сканировать таблицы Users_Log и Users_Departments_Log, и высылать емейлы по изменённым данным. Этот процесс может запускаться, например, джобом по расписанию, или активироваться через Service Broker — как вам будет удобнее.
11 ноя 13, 18:44    [15111191]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
Tauzer
Сделал триггер на таблице Users :

При срабатывании триггера я могу получить данные из таблицы Users_Departments которые еще не успели обновиться.

Могу ли я как-нибудь получить изменившиеся данные из таблицы Users_Departments?
Естественно не можете. Как сервер угадает, какие записи вы захотите обновить в будущем?
Tauzer
Или тут правильнее не триггер использовать, а писать полноценную процедуру?
Какая задача? Послать письмо о результатах этой транзакции (что было - что стало)?

Её можно решить двумя способами:
1. В триггерах вести историю изменения данных, после завершения транзакции посылать письмо с данными, взятыми из этой истории.
2. Сделать процедуру, в которой менять данные, тогда она и будет заодно посылать письмо.
11 ноя 13, 19:19    [15111329]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Tauzer
Member

Откуда:
Сообщений: 114
Всем спасибо за конструктивные ответы.
Буду писать отдельную процедуру.
Насчет best practice - знаю. Можно вообще в теле приложения написать все за 10 минут, но это мне уже скучно.Хочется чего-нибудь иного, тем более что возможности позволяют.
11 ноя 13, 19:44    [15111440]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2415
Tauzer
Если Вы увидели ошибку в моем тексте.


вам почти прямым текстом сказали - вот это неправильно:
SET @UID=(SELECT ID FROM INSERTED)

таблица INSERTED может содержать больше одного значения
12 ноя 13, 10:00    [15113271]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Tauzer
Member

Откуда:
Сообщений: 114
StarikNavy
Tauzer
Если Вы увидели ошибку в моем тексте.


Вы не поверите, я понимаю с первого раза.Суть этого предложения после запятой, а не до.
12 ноя 13, 11:33    [15113797]     Ответить | Цитировать Сообщить модератору
 Re: Триггер  [new]
Glory
Member

Откуда:
Сообщений: 104751
Tauzer
Вы не поверите, я понимаю с первого раза.Суть этого предложения после запятой, а не до.

Ошибка в том, что с множеством надо всегда работать как с множеством. Даже если оно состоит из одного элемента.
12 ноя 13, 11:42    [15113861]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить