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

Откуда:
Сообщений: 679
Всем привет!

Для некоторых таблиц я использую проверку на корректность добавляемых/изменяемых данных с помощью триггера INSTEAD OF.
Примеры проверки
1) Запретить вставку или обновление полей если inserted запись уже есть в таблице при условии что col1 IN (2,3)
2) Запретить вставку или обновление полей если inserted col1 или col2 IS NULL при условии, что col3 > 10

Можно ли тоже самое реализовать с помощью CONSTRAINT, чтобы отказаться от триггера ?
Если да, то в случае срабатывания исключения, как перехватить какое значение/значения вызвало исключение ?
22 май 13, 10:34    [14331025]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Glory
Member

Откуда:
Сообщений: 104760
Testor1
Можно ли тоже самое реализовать с помощью CONSTRAINT, чтобы отказаться от триггера ?

Что мешает попробовать ?

Testor1
Если да, то в случае срабатывания исключения, как перехватить какое значение/значения вызвало исключение ?

Если множественная добавление/изменение, то никак.
22 май 13, 10:37    [14331047]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Testor1
Member

Откуда:
Сообщений: 679
[quot Glory]
Testor1
Можно ли тоже самое реализовать с помощью CONSTRAINT, чтобы отказаться от триггера ?

Что мешает попробовать ?


[quot Glory]
Testor1
Можно ли тоже самое реализовать с помощью CONSTRAINT, чтобы отказаться от триггера ?

Что мешает попробовать ?

IF() - прописывать ? Помню когда несколько констрейнтов прописывал и срабатывало исключение, то я не могу перехватить какой из констрейнтов сработал, чтобы дать бизнес пользователю "человеческий" ответ.

Недавно узнал что можно создавать индексы по условию. Может так лучше и правильней ?
22 май 13, 10:44    [14331109]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37052
Testor1
IF() - прописывать ? Помню когда несколько констрейнтов прописывал и срабатывало исключение, то я не могу перехватить какой из констрейнтов сработал, чтобы дать бизнес пользователю "человеческий" ответ.
Имя констрейнта указано в сообщении об ошибке.
22 май 13, 10:48    [14331136]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Glory
Member

Откуда:
Сообщений: 104760
Testor1
IF() - прописывать ?

Прописывать все, что разрешено синтаксисом

Testor1
омню когда несколько констрейнтов прописывал и срабатывало исключение, то я не могу перехватить какой из констрейнтов сработал, чтобы дать бизнес пользователю "человеческий" ответ.

Имя констрейнта недостаточно "человечно" ?

Testor1
Недавно узнал что можно создавать индексы по условию. Может так лучше и правильней ?

В огороде бузина, а в Киеве дядька. Как фильтрованные индексы помогут соблюдать целостность данных ?
22 май 13, 10:54    [14331181]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Testor1
Недавно узнал что можно создавать индексы по условию. Может так лучше и правильней?
В общем, да.
Но остаётся неясным, что для Вас значит "Запретить вставку или обновление полей если inserted запись уже есть в таблице".
Ибо вставить поле невозможно.
И как Вы идентифицируете запись, - тоже неизвестно.
22 май 13, 11:08    [14331275]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Testor1
Member

Откуда:
Сообщений: 679
iap
Testor1
Недавно узнал что можно создавать индексы по условию. Может так лучше и правильней?
В общем, да.
Но остаётся неясным, что для Вас значит "Запретить вставку или обновление полей если inserted запись уже есть в таблице".
Ибо вставить поле невозможно.
И как Вы идентифицируете запись, - тоже неизвестно.


Создать уникальный индекс по условию, но думаю этот метод не всегда оправдан.
22 май 13, 11:15    [14331325]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Testor1
1) Запретить вставку или обновление полей если inserted запись уже есть в таблице при условии что col1 IN (2,3)

Озвучьте критерий, по которому вы определяете, что "inserted запись уже есть в таблице".

Testor1
2) Запретить вставку или обновление полей если inserted col1 или col2 IS NULL при условии, что col3 > 10

CHECK CONSTRAINT
22 май 13, 11:19    [14331353]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Testor1
с помощью триггера INSTEAD OF

Кстати, триггер INSTEAD OF имеет такой побочный эффект при работе с identity-столбцами:
use tempdb;
go
create table dbo.test(id int identity, name varchar(100));
go
create trigger dbo.test_ii on dbo.test
instead of insert
as
   select * from inserted;
go
insert dbo.test(name)
select 'Иванов' union all
select 'Петров' union all
select 'Сидоров';

/* Все значения identity-поля в таблице inserted в триггере равны 0
id          name
----------- ----------
0           Иванов
0           Петров
0           Сидоров
*/
go
drop table dbo.test;
go

Триггер AFTER такого эффекта не имеет.
22 май 13, 11:27    [14331401]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Testor1
Member

Откуда:
Сообщений: 679
Testor1
омню когда несколько констрейнтов прописывал и срабатывало исключение, то я не могу перехватить какой из констрейнтов сработал, чтобы дать бизнес пользователю "человеческий" ответ.

Имя констрейнта недостаточно "человечно" ?

Like-ом сравнивать текст ошибки и название констрейнта , чтобы узнать какой констрейнт сработал ?
22 май 13, 11:29    [14331414]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Testor1
Member

Откуда:
Сообщений: 679
Гость333
Testor1
с помощью триггера INSTEAD OF

Кстати, триггер INSTEAD OF имеет такой побочный эффект при работе с identity-столбцами:
use tempdb;
go
create table dbo.test(id int identity, name varchar(100));
go
create trigger dbo.test_ii on dbo.test
instead of insert
as
   select * from inserted;
go
insert dbo.test(name)
select 'Иванов' union all
select 'Петров' union all
select 'Сидоров';

/* Все значения identity-поля в таблице inserted в триггере равны 0
id          name
----------- ----------
0           Иванов
0           Петров
0           Сидоров
*/
go
drop table dbo.test;
go

Триггер AFTER такого эффекта не имеет.


Я знаю про этот побочный вариант и использую его для определения операции Insert или Update
С помощью данного триггера, логирую определенные таблицы и фиксирую какая операция была I или U.
22 май 13, 11:31    [14331431]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Glory
Member

Откуда:
Сообщений: 104760
Testor1
Имя констрейнта недостаточно "человечно" ?

Можно подумать, что имена констрейнтам дает Билл Гейтс, а не вы

Testor1
Like-ом сравнивать текст ошибки и название констрейнта , чтобы узнать какой констрейнт сработал ?

Ну вы же зачем то хотите подменить уже сгенерированное исключение своим.
22 май 13, 11:37    [14331483]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Testor1
Member

Откуда:
Сообщений: 679
Glory
Testor1
Имя констрейнта недостаточно "человечно" ?

Можно подумать, что имена констрейнтам дает Билл Гейтс, а не вы

Testor1
Like-ом сравнивать текст ошибки и название констрейнта , чтобы узнать какой констрейнт сработал ?

Ну вы же зачем то хотите подменить уже сгенерированное исключение своим.


Имена констрейнтов придумывает разработчик.
Полагал что есть другой способ узнать какой констрейнт сработал, кроме того как LIKE-ом сканировать текст ошибки. Как-то не правильно это, но спорить с этим не буду.
22 май 13, 13:30    [14332548]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Testor1
Member

Откуда:
Сообщений: 679
Гость333
Testor1
1) Запретить вставку или обновление полей если inserted запись уже есть в таблице при условии что col1 IN (2,3)

Озвучьте критерий, по которому вы определяете, что "inserted запись уже есть в таблице".

Testor1
2) Запретить вставку или обновление полей если inserted col1 или col2 IS NULL при условии, что col3 > 10

CHECK CONSTRAINT


К слову если col3 IN (2,3) тогда проверить в существующей таблице есть ли записи с таким же col1 и col2.
22 май 13, 13:31    [14332563]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Glory
Member

Откуда:
Сообщений: 104760
Testor1
Имена констрейнтов придумывает разработчик.

Значит ли это, что имена таки могут быть "человечискими" ?

Testor1
Полагал что есть другой способ узнать какой констрейнт сработал, кроме того как LIKE-ом сканировать текст ошибки. Как-то не правильно это, но спорить с этим не буду.

А какая разница, какой именно сработал ? От этого реально что-то зависит ?
22 май 13, 13:34    [14332601]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Testor1
Member

Откуда:
Сообщений: 679
Glory
Testor1
Имена констрейнтов придумывает разработчик.

Значит ли это, что имена таки могут быть "человечискими" ?

Testor1
Полагал что есть другой способ узнать какой констрейнт сработал, кроме того как LIKE-ом сканировать текст ошибки. Как-то не правильно это, но спорить с этим не буду.

А какая разница, какой именно сработал ? От этого реально что-то зависит ?


Я всегда пытаюсь дать бизнес пользователю информативную ошибку. Это экономит время бизнес пользователя и хелпдеска.
22 май 13, 13:45    [14332720]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Testor1
дать бизнес пользователю информативную ошибку
fnError ?
22 май 13, 20:12    [14335386]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31430
Testor1
Примеры проверки
1) Запретить вставку или обновление полей если inserted запись уже есть в таблице при условии что col1 IN (2,3)
2) Запретить вставку или обновление полей если inserted col1 или col2 IS NULL при условии, что col3 > 10

Можно ли тоже самое реализовать с помощью CONSTRAINT, чтобы отказаться от триггера ?
Если да, то в случае срабатывания исключения, как перехватить какое значение/значения вызвало исключение ?
Для этих двух случаев можно.

Перехватывать - нужно парсить текст ошибки. Ну или имена констрейнов придумывать понятные человеком (тут ещё зависит от уровня пользователей, чего им будет достаточно для понимания).
22 май 13, 20:25    [14335427]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Testor1
Member

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

в каких сценариях используют instead of?
23 май 13, 00:40    [14336158]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
Testor1,
я бы рекомендовал проверку всякой бизнес-логики реализовать в процедуре до вставки
на мой взгляд всякие констрейнты, чеки, уникуи, триггера и пр
предназначены прежде всего для обеспечения целостности базы
и ошибки в них являются аварийными сигналами для разработчика а не для юзера
и разработчик должен сделать все чтобы не доводить до их появления
сначала сделать валидацию на уровне приложения
а потом в процедуре перед вставкой проверить все и вернуть "человеческое" сообщение
или если нечего сообщать то вставить
а ошибки сервера надо в отдельный лог выводить который админ мониторит
а юзеру сказать что мол ошибка, пинай админа
23 май 13, 03:54    [14336407]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
Testor1
в каких сценариях используют instead of?
на мой взгляд оправдано на вьюхи такое делать, хотя в принципе и на любую другую таблицу не запрещено
23 май 13, 04:08    [14336409]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
leov,

В некоторых случаях проверка ДО ничего не может гарантировать (проблема конкурентности, следует кажись из теории цепей Маркова). Поэтому многие вещи в скуле делается после вставки (и в програмах, см. семафоры и т.п.).
Поэтому ставить констрейнты и тригера AFTER оправдано, а INSTEAD OF не всегда оправдано.
Тем более что 90% ошибок, как вы и писали, отсекаются ещё на клиенте.

Во вторых, процедура гибче и предсказуемее, чем тригера INSTEAD OF (на вью), ибо ещё надо знать как ставить локировки на ней (проблема изменёных данных) и view делает порой ненужный лишний, тяжёлый (все колонки) запрос.
Т.е. для массовых однотипных операций это скорее неприемлемо.

Но в случае проверок целостности данных и валидации, т.е. не внещней (взаимодействующей) бизнес логики делается в первую на констрейнтах и затем можно и на обычных триггерах. А сложные "проверки" (воркфлоу) непосредственно на клиенте, аппликейшин сервере и в процедурах.

Ошибка должна быть информативна, и человечность это только вторая часть. Поэтому её код и точка (детали) обязательна, т.е. она должна быть структурна. Клиент должен позаботится чтобы выкусить из неё нужные данные и представить в нужном виде.
Уже предлагал 14335386 способ обработки, уже можно обрабаотывать в процедурах. По ссылке говорил что могу представить набросок кода обработки для С# (на клиенте, где ей и место).
Но по сути это тоже что есть в фукции fnError. Тем более что уже помимо этой обработки должны быть и другие обработки - клиент (аппликейшин сервер) должен правильно рабоать со скулем, его провайдером.

Этот скуль не такой гибкий в этом плане (детальность/структурность), это уже апликативные языки гибкие что в ошибке хранится всё и детали и человеческий вариант. Но по сути, если 50% ошибок могут вообще и не дойти до клиента (обрабатываться в иные действия), то в принципе незачем для них городить человеческий вид, пусть уже клиент позаботится, дополнив.
Системные вещи обязаны быть эфективными, и не над на это жаловаться. Подстраивайтесь.
23 май 13, 09:31    [14336837]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Testor1
alexeyvg,

в каких сценариях используют instead of?
Могу привести один пример, когда INSTEAD OF INSERT реально помог.
У нас есть таблица проводок. Она коллосальна по размеру.
Часто выполняются м ассовые вставки.
При этом есть пара десятков бизнес-требований к данным,
которые необходимо проверять и не допускать вставку записей,
противоречащих этим требованиям.

Проверка допустимости вставляемых данных была реализована в триггере AFTER INSERT.
Вставка множества записей зачастую выполнялась довольно-таки заметное время.
После этого выполнялась проверка в триггере и в случае обнаружения ошибки - откат.
ROLLBACK, надо сказать, занимает столько же времени, сколько и сама вставка!

Заменил триггер на INSTEAD OF INSERT. Вставляю записи только после успешной проверки.
Самое главное, в случае ошибки откатывать нечего! Ведь вставки-то ещё не было!
Получился выигрыш по эффективности в 2 раза в случае некорректных данных
(не такая уж редкая ситуация, ибо трудно вручную отследить все требования к данным).
23 май 13, 09:47    [14336912]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31430
Testor1
alexeyvg,

в каких сценариях используют instead of?
Сложный контроль целостности и распределённые представления (распределение данных на несколько серверов).

В общем, достаточно нечастые задачи.

Нужно всеми силами стараться использовать FK и CHECK констрейны и уникальные индексы, это самое простое, быстрое, надёжное и понятное тем, кто потом будет рабротать с системой решение.
Далее:
- контроль в AFTER триггерах,
- потом в INSTEAD OF триггерах,
- потом в хранимых процедурах,
- потом в приложении.

Вот примерно такие приоритеты.
23 май 13, 10:23    [14337057]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF и CONSTRAINT  [new]
Testor1
Member

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

в каких сценариях используют instead of?
Могу привести один пример, когда INSTEAD OF INSERT реально помог.
У нас есть таблица проводок. Она коллосальна по размеру.
Часто выполняются м ассовые вставки.
При этом есть пара десятков бизнес-требований к данным,
которые необходимо проверять и не допускать вставку записей,
противоречащих этим требованиям.

Проверка допустимости вставляемых данных была реализована в триггере AFTER INSERT.
Вставка множества записей зачастую выполнялась довольно-таки заметное время.
После этого выполнялась проверка в триггере и в случае обнаружения ошибки - откат.
ROLLBACK, надо сказать, занимает столько же времени, сколько и сама вставка!

Заменил триггер на INSTEAD OF INSERT. Вставляю записи только после успешной проверки.
Самое главное, в случае ошибки откатывать нечего! Ведь вставки-то ещё не было!
Получился выигрыш по эффективности в 2 раза в случае некорректных данных
(не такая уж редкая ситуация, ибо трудно вручную отследить все требования к данным).


Я фактически по этой же причине начал использовать instead of и помимо проверки добавил в триггер функцию логирования изменений. При такой реализации не работает проверка relations, а она бывает порой очень нужна.
Я отказался от проверки в процедурах, потому что надо было копи пейстить много. Плюс удобно когда проверка делается на уровне триггера. Когда админ пытается, что-то исправить на уровне таблиц напрямую, то триггер не даст внести не корректные изменения (согласно бизнес логике).

Сейчас думаю перенести проверку в констрейнт (но он не такой удобный) и логирование в триггер after.
Кстати в instead триггере я отслеживал, какая именно строка не соотвествует бизнес логике и выводил информацию о ней. Это было удобно. Сейчас наверное откажусь от этого :(.
23 май 13, 10:24    [14337060]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить