Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 96
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 96 (15 июля 2006 г.)
Новым посетителям сайта
Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 58-ти задач первого этапа. При подсчете рейтинга каждого
участника отбрасывается один самый худший показатель среди всех решенных им упражнений.
Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов.
Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий.
Новости сайта
§ Собрал отзывы о сайте, опубликованные на форумах и высказанные в переписке. С первой частью уже можно ознакомиться здесь
§ Появился поиск по сайту от Гугл. Правда он находится на сайте "Книги и статьи по SQL". Поиск выполняется по страницам, не требующим авторизации, т.е. по форуму пока поиска нет. Это в планах на будущее.
§ Выставил новую задачу от Roman S. Golubin. Ее номер 87, сложность 3 балла. Уже есть первые решившие, так что можно приблизительно оценить ее реальную сложность. Лидер рейтинга Snowbear (138, 4.038) тоже решил эту задачу, оставив автора пока на второй позиции.
§ Новый человек в сотне: Squid (задач 101, время 5.141)
§ Вошла в 20-ку vlksm(137, 22.350), решив новую задачу 87. У нее есть шансы попасть в десятку, если достаточно быстро будет решена 138 задача. Давно пора разбавить мужской коллектив :-).
§ Сохранили шансы попасть в ТОР 10: a2010 (136, 11.292) =Maxim= (117, 7.484)
§ Продолжили свое восхождение к вершине: SoVa (123, 87.147) FanOfBeer (117, 66.363) ba (116, 35.296) Ocean (106, 24.041)
§ На этой неделе улучшил качество своего сертификата: SoVa (B06007933) [AR] (г.Богданович, Россия)
Проблема, с которой я столкнулся, довольно известна. Я имею два триггера, которые должны отработать в предопределенном порядке, т.е. триггер A должен выполниться сначала, а после него должен отработать триггер B. Вы можете поинтересоваться, а почему бы не иметь один триггер, который объединит триггеры A и B в один триггер AB? Хороший вопрос. К сожалению, триггер A используется для репликации (For Replication), в то время как более поздний триггер - не для репликации, что определяет наличие именно двух
триггеров.
Давайте создадим испытательную среду. Для этого нам понадобятся две таблицы. Одна - для написания и тестирования триггеров, а вторая - для журнализации триггера и времени его выполнения.
Один триггер будет триггером на вставку, который будет обновлять столбец First некоторым случайным числом. Этот триггер будет называться trg_UpdateFirst
CREATE TRIGGER trg_UpdateFirst ON dbo.[Trigger Priority] FOR INSERT AS declare @id int, @val as float Select @id = id from inserted select @val = floor(rand() * 10) Update [Trigger Priority] set [First] = @val Where ID = @id Insert into TriggerLog (TriggerName) values ('trg_UpdateFirst')
Последняя строка триггера журнализирует имя триггера и время его срабатывания в таблице TriggerLog. Следующий триггер используется для обновления столбца Second значением из столбца First.
CREATE TRIGGER trg_UpdateSecond ON dbo.[Trigger Priority] FOR INSERT AS declare @id int Select @id = id from inserted Update [Trigger Priority] set [Second] = [First] Where ID = @id Insert into TriggerLog (TriggerName) values ('trg_UpdateSecond')
Последний триггер используется для обновления столбца Last суммой значений столбцов First и Second.
CREATE TRIGGER trg_UpdateLast ON dbo.[Trigger Priority] FOR INSERT AS declare @id int Select @id = id from inserted Update [Trigger Priority] set [Last] = [First] + [Second] Where ID = @id Insert into TriggerLog (TriggerName) values ('trg_UpdateLast')
Теперь, чтобы получить ожидаемые результаты, триггеры trg_UpdateFisrt, trg_UpdateSecond и trg_UpdateLast должны выполняться в вышеперечисленном порядке. Что вы об этом думаете? Каков будет порядок? Случайным образом или в некотором особом порядке?
Прежде чем ответить на этот вопрос, давайте посмотрим, что произойдет. После вставки одной записи в таблицу [Trigger Priority] первый столбец содержит пятерку, что нормально, и второй тоже - 5, что также правильно. Однако в последнем столбце находится NULL! Почему? Разве не должно быть 10?
Теперь давайте проверим таблицу TriggerLog. Порядок столбцов - trg_UpdateLast, trg_UpdateFirst и trg_UpdateSecond. После небольшого исследования выясняется, что триггеры выполняются в том порядке, в котором они были созданы. Таким образом, в идеале триггеры следует создавать в таком порядке: trg_UpdateFirst, trg_UpdateSecond и trg_UpdateLast. Это ни в коем случае не является простой задачей в силу динамического характера процесса разработки, который по большей части не контролируется разработчиками.
Другой вопрос. Как на более поздней стадии Вы собираетесь узнать о порядке срабатывания триггеров?
select * from sysobjects where xType ='TR' order by id
С помощью вышеупомянутого запроса Вы можете идентифицировать порядок, в котором выполнятся триггеры.
Установить порядок
Теперь вопрос о том, как мы можем задать порядок выполнения. Имеется хранимая системная процедура, которая для того и существует, чтобы ответить на подобный вопрос. Эта хранимая процедура - sp_settriggerorder. У этой SP есть три параметра.
Первый параметр - это имя триггера, а второй параметр - порядок. Этот порядок может принимать одно из трех значений: " First (первый)", "None (ни первый, ни последний)", и " Last (последний)". Последний параметр представляет собой тип триггера, т.е. Insert, Update или Delete. Это означает, что Вы не можете позволить себе иметь четыре или пять триггеров одного и того же типа, которые бы выполнялись в определенном порядке. Однако это вряд ли встречается в практике. По крайней
мере, я не встречал еще так много триггеров одного типа на одной таблице.
Этот порядок не может быть установлен опциями Alter Trigger или Create Trigger. Если оператор Alter Trigger изменяет первый или последний триггер, то первоначально установленные на триггере атрибуты First или Last удаляются, и значение заменяется на None. Значение порядка должно быть переустановлено с помощью хранимой процедуры sp_settriggerorder.
Разрешения
Владелец триггера и таблицы, на которой определен триггер, имеет разрешение на выполнение sp_settriggerorder. Члены ролей db_owner и db_ddladmin в текущей базе данных, так же как серверная роль sysadmin, могут выполнять эту хранимую процедуру.
Получение порядка
Следующая проблема заключается в установлении порядка, в котором выполняются триггеры, на более поздней стадии. Если я не ошибаюсь, нет никакого прямого способа получить эту информацию из Enterprise Manager SQL Server. Вместо этого Вы пишете простые запросы.
укажет, является ли trg_UpdateFirst первым (First) триггером на вставку?
Предостережения
Когда Вы генерируете скрипт для триггеров, приоритет их срабатывания не будет заскриптован. Это означает, что Вам придется повторно запустить приоритетные сценарии. Это аргумент в пользу отказа от приоритетных триггеров.
Сервер SQL 2005
В SQL Server 2005 имеется дополнительный параметр в sp_settriggerorder, который должен сообщить, является ли данный триггер триггером базы данных или триггером сервера. Это обусловлено тем, что в SQL Server 2005 Вы можете написать также DDL триггеры.
Заключение
Установка приоритетного порядка для триггера SQL Server не составляет труда. Однако Вы должны взять на себя дополнительную заботу, принимая эту возможность в структуре вашей разработки. Будет хорошо, если Microsoft может обеспечить решения для рассмотренных выше проблем.
§ Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL.
§ Поступила в продажу книга SQL. Задачи и решения, посвященная анализу ошибок, допускаемых при решении задач первого этапа. На сайте издательства Питер можно сделать заказ и познакомиться с содержанием.
Контакты
По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77@yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.