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

Откуда:
Сообщений: 79
Здравствуйте, помогите исправить триггер. Есть таблица UploadsEmailSettings
[dbo].[UploadsEmailSettings](
[SupplierId] [bigint] NOT NULL,
[EmailSender] [varchar](50) NULL,
[EmailFrom] [varchar](50) NULL,
[Subject] [varchar](100) NULL,
[Body] [varchar](500) NULL,
[Encoding] [varchar](15) NULL,
[ArchiveName] [varchar](150) NULL,
[FileName] [varchar](150) NULL
)

в триггере нужны две проверки - первое, чтоб одновременно поля [EmailSender] и [EmailFrom] не были нулл, и эта проверка работает.
а второе - при обновлении и вставке нужно соблюсти уникальность трех полей: [SupplierId] ,[EmailSender] , [EmailFrom]... вот тут то и возникла загвоздка. так как каждое из полей в этой табл само по себе не уникально да еще и два из них могут быть нулевыми.
сейчас у меня не дает вставить вообще ничего!

ALTER TRIGGER [dbo].[tr_UploadsEmailSettings_Insert_Update]
ON [dbo].[UploadsEmailSettings]
INSTEAD OF Insert, Update
AS
BEGIN	

---проверяем чтобы во вставляемом значении не было одновременно NULL в EmailFrom и EmailSender
	IF exists (
			  select *
				from inserted i 
			   where (i.EmailFrom is null and i.EmailSender is null) 
			 ) 
	  BEGIN
		  RAISERROR ('EmailFrom и EmailSender не могут одновременно быть null.', 16, 1)
		  return
	  END
	  
	ELSE
	--проверка на уникальность
		BEGIN		   
			--- 1. обновление
			if exists(select * from deleted)
				BEGIN
				
					SELECT  i.*
					into updated
					from deleted d 
						 join inserted i on i.SupplierId = d.SupplierId AND 
											ISNULL(i.EmailFrom, 'null') = ISNULL(d.EmailFrom, 'null') AND
											ISNULL(i.EmailSender, 'null') = ISNULL(d.EmailSender, 'null')
						 
					  
					  IF exists (
							  select *
								from UploadsEmailSettings u
								inner join updated  on ( u.SupplierId = updated.SupplierId AND 
														ISNULL(u.EmailFrom, 'null') = updated.EmailFrom AND
														ISNULL(u.EmailSender, 'null') = updated.EmailSender)
							   
							 ) 
					  BEGIN
						  RAISERROR ('Попытка вставить значение с повторяющимися SupplierId, EmailFrom,EmailSender', 16, 1)
						  rollback tran
						  return
					  END 
					  
					  --тут бред? вдруг сравниваемые поля тоже обновились?
					 UPDATE UploadsEmailSettings
					 SET
						UploadsEmailSettings.[SupplierId] =updated.SupplierId,
						UploadsEmailSettings.[EmailSender] = updated.[EmailSender],
						UploadsEmailSettings.[EmailFrom] = updated.[EmailFrom],
						UploadsEmailSettings.[Subject] = updated.[Subject],
						UploadsEmailSettings.[Body] = updated.[Body],
						UploadsEmailSettings.[Encoding] = updated.[Encoding],
						UploadsEmailSettings.[ArchiveName] = updated.[ArchiveName],
						UploadsEmailSettings.[FileName] = updated.[FileName]
					from UploadsEmailSettings u
					inner join updated  on ( u.SupplierId = updated.SupplierId AND 
											ISNULL(u.EmailFrom, 'null') = updated.EmailFrom AND
											ISNULL(u.EmailSender, 'null') = updated.EmailSender) 
		  
				END	
			
			
			--2. вставка	
			else 
				BEGIN			
					IF exists (
							  select *
								from UploadsEmailSettings u
								inner join inserted i on ( u.SupplierId = i.SupplierId AND 
														ISNULL(u.EmailFrom, 'null') = ISNULL(i.EmailFrom, 'null') AND
														ISNULL(u.EmailSender, 'null') = ISNULL(i.EmailSender, 'null'))
							 ) 
					  BEGIN
						  RAISERROR ('Попытка вставить значение с повторяющимися SupplierId, EmailFrom,EmailSender', 16, 1)
						  return
					  END  
					  
					 insert into UploadsEmailSettings
					 select * from inserted
				END	  
		END		
	END	
24 янв 13, 11:33    [13820314]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
А зачем обязательно через триггер?
Констрейнт и уникальный ключ не подходят?
24 янв 13, 11:41    [13820372]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
Констрейнты CHECK и UNIQUE сделают всё, о чём Вы пишете.
Кроме, правда, Ваших сообщений.

Если уж так надо проверить уникальность в триггере INSTEAD IF INSERT, UPDATE,
то надо выбирать записи для проверки из (inserted UNION ALL [таблица за вычетом записей в inserted]).
И NULLы грамотно сравнивать.
24 янв 13, 11:50    [13820430]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление  [new]
Тэй
Member

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

как можно сделать юник? подскажите? ведь поля могут содержать нулевые значения
24 янв 13, 12:07    [13820563]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление  [new]
iap
Member

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

как можно сделать юник? подскажите? ведь поля могут содержать нулевые значения
UNIQUE нормально относится к NULL.
В отличие от PRIMARY KEY.
24 янв 13, 12:10    [13820580]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление  [new]
Тэй
Member

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

ALTER TABLE UploadsEmailSettings
ADD CONSTRAINT uc_UploadsEmailSettings UNIQUE (SupplierId, EmailFrom, EmailSender)

и
ALTER TRIGGER [dbo].[tr_UploadsEmailSettings_Insert_Update]
ON [dbo].[UploadsEmailSettings]
AFTER Insert, Update
AS
BEGIN

---проверяем чтобы во вставляемом значении не было одновременно NULL в EmailFrom и EmailSender
IF exists (
select *
from inserted i
where (i.EmailFrom is null and i.EmailSender is null)
)
BEGIN
RAISERROR ('EmailFrom и EmailSender не могут одновременно быть null.', 16, 1)
rollback tran
return
END
END

спасибо, нет, правда))
24 янв 13, 12:15    [13820609]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление  [new]
iap
Member

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

ALTER TABLE UploadsEmailSettings
ADD CONSTRAINT uc_UploadsEmailSettings UNIQUE (SupplierId, EmailFrom, EmailSender)

и
ALTER TRIGGER [dbo].[tr_UploadsEmailSettings_Insert_Update]
ON [dbo].[UploadsEmailSettings]
AFTER Insert, Update
AS
BEGIN

---проверяем чтобы во вставляемом значении не было одновременно NULL в EmailFrom и EmailSender
IF exists (
select *
from inserted i
where (i.EmailFrom is null and i.EmailSender is null)
)
BEGIN
RAISERROR ('EmailFrom и EmailSender не могут одновременно быть null.', 16, 1)
rollback tran
return
END
END

спасибо, нет, правда))
Вместо триггера:
ALTER TABLE UploadsEmailSettings
ADD CONSTRAINT c_EmailFromEmailSender CHECK(EmailFrom IS NOT NULL OR EmailSender IS NOT NULL);
24 янв 13, 12:19    [13820647]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Тэй
---проверяем чтобы во вставляемом значении не было одновременно NULL в EmailFrom и EmailSender

ALTER TABLE dbo.UploadsEmailSettings ADD CONSTRAINT CHK_UES_Emails CHECK (EmailSender IS NOT NULL OR EmailFrom IS NOT NULL)
24 янв 13, 12:20    [13820660]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление  [new]
Тэй
Member

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

кстати, как это? "грамотно NULLы сравнивать"? в вашем понимании?
24 янв 13, 12:25    [13820689]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление  [new]
iap
Member

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

кстати, как это? "грамотно NULLы сравнивать"? в вашем понимании?
Во всяком случае не операторами = и <>
В простейшем случае
F1=F2 OR F1 IS NULL AND F2 IS NULL
если рассматривать NULLовые значения как равные, конечно.
Теоретически ведь результат должен быть "Неизвестно" ("UNKNOWN").
Если надо сравнить таким образом много полей, то в последних версиях можно короче
EXISTS(SELECT F1, F2, F3 INTERSECT SELECT F4, F5, F6)
вместо
(F1=F4 OR F1 IS NULL AND F4 IS NULL) AND (F2=F5 OR F2 IS NULL AND F5 IS NULL) AND (F3=F6 OR F3 IS NULL AND F6 IS NULL)
Так как UNION, INTERSECT, EXCEPT считают, что NULL=NULL
24 янв 13, 13:12    [13821105]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление  [new]
Тэй
Member

Откуда:
Сообщений: 79
iap,
"Так как UNION, INTERSECT, EXCEPT считают, что NULL=NULL" не знала таких тонкостей, спасибо)
24 янв 13, 13:25    [13821213]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить