|
|
Понятие триггера. Команда создания триггераC помощью ограничений целостности, правил и значений по умолчанию не всегда можно добиться нужного уровня функциональности. Часто требуется реализовать сложные алгоритмы проверки данных, чтобы гарантировать их достоверность и реальность. Кроме того, бывает необходимо отслеживать изменения значений таблицы, чтобы нужным образом изменить связанные данные. Для решения этих и многих других проблем используются триггеры. Триггер (trigger) SQL Server 2000 - это специальный тип хранимых процедур, запускаемых сервером автоматически при выполнении тех или иных действий с данными таблицы. Каждый триггер привязывается к конкретной таблице. Когда пользователь пытается, например, изменить данные в таблице, сервер автоматически запускает триггер и, только если он завершается успешно, разрешается выполнение изменений. Все производимые триггером модификации данных рассматриваются как одна транзакция. В случае обнаружения ошибки или нарушении целостности данных происходит откат этой транзакции. Тем самым внесение изменений будет запрещено. Будут также отменены все изменения, уже сделанные триггером. Область применения триггеров достаточно широка. Например, при репликации сведением триггеры используются для отслеживания всех выполняемых в таблице изменений. Триггеры собирают информацию о производимых изменениях и сохраняют ее в системных таблицах поддержки репликации. Триггеры также позволяют создать сложное значение по умолчанию, вычисляя его с помощью других столбцов и функций Transact-SQL. Другим примером полезности триггеров является обеспечение нестандартной целостности ссылок, поддержание которой обычными средствами SQL Server невозможно. Триггеры часто используются для выполнения каскадных изменений в нескольких связанных таблицах. Например, в таблице titles базы данных pubs можно определить триггер DELETE, который будет удалять связанные строки в таблицах titleauthor, sales и roysched. Из этих таблиц будут удалены все строки, в которых значения столбца title_id совпадает со значением аналогичного столбца в удаляемой строке таблицы titles. Область применения триггеров не ограничивается какими-то строго очерченными рамками. Вы можете свободно применять триггеры по своему усмотрению, исходя из требований к удобству и производительности выполняемых действий. Для эффективного применения триггеров необходимо четкое понимание принципов их действия, о чем и будет рассказано в следующих разделах этого урока. Не следует применять триггеры для выполнения простых проверок, которые могут быть произведены с помощью правил или ограничений целостности. Кроме того, следует избегать использования триггеров, если те же действия могут быть реализованы с помощью хранимой процедуры или обычного пакета команд Transact-SQL. Использование триггеров нежелательно еще и по той причине, что они удерживают блокировку до завершения триггера, запрещая обращение к ресурсу других пользователей. Триггеры различаются по типу команд, на которые они реагируют. Существует три типа триггеров. INSERT TRIGGER. Триггеры этого типа запускаются при попытке вставки данных с помощью команды INSERT. UPDATE TRIGGER. Триггеры этого типа запускаются при попытке изменения данных с помощью команды UPDATE. DELETE TRIGGER. Триггеры этого типа запускаются при попытке удаления данных с помощью команды DELETE. ВНИМАНИЕ Начиная с SQL Server 7.0, поддерживается возможность создания для одной таблицы нескольких однотипных триггеров. При работе с более ранними версиями пользователь мог для каждой таблицы определить только один триггер каждого типа. ВНИМАНИЕ Триггер не запускается при выполнении команд обработки текстовых блоков, таких как WRITETEXT, UPDATETEXT и READTEXT. Помимо классификации триггеров по типу изменения данных в таблице, они также классифицируются по типу поведения. В SQL Server 2000 существует два параметра, определяющие поведение триггеров. AFTER. Триггер выполняется после успешного выполнения команды, изменяющей данные в таблице. Если же команда по каким-либо причинам не может быть успешно завершена, то триггер также не выполняется. Однако следует отметить, что изменения данных в результате выполнения запроса пользователя и выполнение триггера осуществляется в теле одной транзакции. То есть, если произойдет откат триггера (в нем будет выполнена команда ROLLBACK TRAN), то также будут откачены и пользовательские изменения. Триггеры типа AFTER невозможно определить для представлений. Они могут быть определены только для таблиц. Для каждой таблицы можно определить более одного триггера AFTER для каждой операции (INSERT, UPDATE, DELETE). Если вы предусмотрели для таблицы выполнение нескольких триггеров AFTER, то с помощью системной хранимой процедуры sp_settriggerorder возможно указать, какой триггер будет выполняться первым, а какой последним. Однако назначить триггер, который будет выполняться вторым, третьим и т. д. (но не последним), к сожалению нельзя. INSTEAD OF. В этом случае триггер вызывается вместо команд, назначенных для запуска триггера. Триггеры INSTEAD OF могут быть определены для таблиц и представлений. Вы сможете определить только один триггер INSTEAD OF для каждой операции (INSERT, UPDATE, DELETE). ПРИМЕЧАНИЕ По умолчанию, в SQL Server 2000 все триггеры являются триггерами типа AFTER. Более того, в предыдущих версиях SQL Server все триггеры имели тип AFTER. Триггеров типа INSTEAD OF в предыдущих версиях вообще не существовало. При работе с SQL Server 7.0 и более ранними версиями указание типа триггера не поддерживалось, и все определяемые в этих версиях триггеры действовали как триггеры AFTER. Перед созданием триггера необходимо тщательно продумать последовательность выполнения команд внутри него. Неправильно написанные триггеры могут привести к серьезным проблемам, таким, например, как появление "мертвых" блокировок. Триггеры способны длительное время блокировать множество ресурсов, поэтому следует обратить особое внимание на сведение к минимуму конфликтов доступа. Необходимо снимать блокировки с ресурсов сразу же после того, как они становятся ненужными, а не дожидаться завершения выполнения всех команд триггера. Триггеры не могут быть созданы для временных или системных таблиц, хотя они и могут обращаться к временным таблицам. Если в теле триггера необходимо обратиться к данным системных таблиц, то можно создать представление, которое будет отображать информацию из системных таблиц, и обращаться в триггере к этому представлению. Команда CREATE TRIGGER должна быть первой командой в пакете и может применяться только к одной таблице. Триггер может быть создан только в текущей базе данных, но допускается обращение внутри триггера к другим базам данных, в том числе и расположенным на удаленных серверах. Итак, для создания триггера используется команда CREATE TRIGGER. CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { FOR | AFTER | INSTEAD OF } { [DELETE] [,] [INSERT] [,] [UPDATE] } [ WITH APPEND ]
[ NOT FOR REPLICATION ] AS sql_statement [...n] } Это был первый вариант команды. Теперь же рассмотрим второй вариант: CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { ( FOR | AFTER | INSTEAD OF ) { [INSERT] [,] [UPDATE] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { IF UPDATE (column) [ { AND | OR } UPDATE (column) ] [...n] | IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask) {comparison_operator} column_bitmask [...n] } sql_statement [...n] } Рассмотрим назначение каждого из параметров команды CREATE TRIGGER. trigger_name. Имя триггера, под которым он будет опознаваться хранимыми процедурами и командами Transact-SQL. Имя триггера должно быть уникальным в пределах базы данных. Дополнительно к имени триггера можно указать имя владельца. table. Имя таблицы базы данных, с которой будет связан триггер. view. Имя представления базы данных, с которым будет связан триггер. WITH ENCRYPTION. При указании этого параметра сервер выполняет шифрование кода триггера, так что никто, включая администратора, не сможет получить к нему доступ и прочитать его. Шифрование часто используется для скрытия авторских алгоритмов обработки данных, являющихся интеллектуальной собственностью программиста или коммерческой тайной. AFTER. При указании этого параметра триггер будет запускаться только после того, когда все команды, вызвавшие триггер будут успешно выполнены. Этот параметр используется по умолчанию (то есть когда указывается ключевое слово FOR). INSTEAD OF. При задании этого параметра триггер будет выполняться вместо соответствующих запросов пользователей, приведших к вызову триггера. Собственно пользовательские запросы выполняться не будут. [DELETE] [,] [INSERT] [,] [UPDATE]. Эта конструкция определяет, на какие команды (вставки, удаления или изменения) будет реагировать триггер. При создании триггера должно быть указано хотя бы одно из этих ключевых слов. Допускается создание триггера, реагирующего на две или три команды. WITH APPEND. Использование этого параметра требуется только в том случае, если для базы данных установлен уровень совместимости 6.5 или ниже. Так как до версии SQL Server 7.0 для таблицы было разрешено создание не более одного триггера каждого типа, то по умолчанию создание нового триггера будет приводить к удалению ранее созданного триггера. Параметр WITH APPEND позволяет создавать несколько триггеров каждого типа для базы данных с уровнем совместимости 6.5 или ниже. Для баз данных с уровнем совместимости 7.0 и выше использование этого параметра не обязательно, так как в SQL Server 7.0 и тем более в SQL Server 2000 реализована автоматическая поддержка для одной таблицы до 16 триггеров каждого типа. NOT FOR REPLICATION. При создании триггера с этим параметром запрещается его запуск при модификации таблиц механизмами репликации. Этот параметр часто используется при создании системных триггеров поддержки подсистемы репликации. AS sql_statement [...n]. После ключевого слова AS следует набор команд, которые будут выполнены при запуске триггера (тело триггера). Мы дали описание параметров первого варианта команды CREATE TRIGGER. Теперь рассмотрим параметры, которые используются во втором варианте команды. Основное отличие между командами состоит в наборе параметров после ключевого слова AS. Если в первом варианте после слова AS была указана только конструкция sql_statement [...n], то во втором варианте набор параметров заметно расширен. Хотя они являются частью команды CREATE TRIGGER, их стоит рассматривать как частный случай команд, определяющих тело триггера. Итак, рассмотрим параметры второго варианта команды (параметры, которые уже описаны, мы рассматривать не будем). FOR [INSERT] [,] [UPDATE]. Определяет команду, при выполнении которой будет запускаться триггер. Возможно связывание триггера с несколькими командами. IF UPDATE (column). С помощью этой команды можно проверить, изменялось ли значение в определенном столбце. В случае положительного ответа будет выполнен определенный набор команд. Отметим, что эта команда может быть использована только для команд INSERT или UPDATE, но не для команды DELETE. Имя таблицы (или представления) при определении имени столбца не требуется. AND | OR} UPDATE (column). Эта конструкция применяется совместно с предыдущим параметром в случае, если необходимо проверить изменение более чем одного столбца. Аргумент column задает имя столбца, для которого необходимо отследить факт изменения данных. Ключевое слово AND предписывает запускать триггер только в том случае, если были модифицированы оба столбца (указанные в этой и предыдущей конструкциях). При использовании ключевого слова OR триггер будет выполнен при выполнении изменений в любом из столбцов. [...n]. Этот параметр указывает, что допускается использование более чем одной конструкции { AND | OR} UPDATE (column). IF (COLUMNS_UPDATED()). С помощью этой конструкции можно узнать, какие столбцы таблицы были изменены (UPDATE) или добавлены (INSERT), что допускается только для команд INSERT и UPDATE. Функция COLUMNS_UPDATED() может быть использована в любом месте внутри триггера и возвращает двоичное число, каждый бит которого соответствует конкретному столбцу таблицы. Если тот или бит установлен в 1, то соответствующий столбец был изменен. Младший бит соответствует первому столбцу таблицы, второй бит справа - второму столбцу и т. д. bitwise_operator. Этот параметр задает оператор побитовой обработки, с помощью которого можно определить, изменялся конкретный столбец или нет. Например, можно использовать оператор & (битовый AND) для определения того, был ли изменен конкретный столбец. Оператор побитовой обработки выполняется для операндов COLUMNS_UPDATED() и updated_bitmask. updated_bitmask. Этот аргумент определяет битовую маску для проверки факта изменения в одном или нескольких столбцах. Каждый столбец представлен отдельным битом. Младший бит соответствует первому столбцу таблицы, второй бит - второму столбцу и т. д. Например, если в таблице имеется 7 столбцов, то значение updated_bitmask для проверки факта изменения столбцов 2, 3 и 6 должно быть равно 38 (двоичное значение 0100110). Для проверки факта изменения столбцов 4, 5, 6 и 7 значение updated_bitmask будет равно 120 (двоичное значение 1111000). comparison_operator. Оператор сравнения, используемый для проверки значения, возвращенного в результате выполнения побитовой операции сравнения, на соответствие установленным критериям. Чаще всего это оператор =, хотя допускается использование любых операторов сравнения (<, >, !=). column_bitmask. Битовая маска для проверки, были ли действительно изменены проверяемые столбцы.
Также может использоваться и второй вариант команды: ALTER TRIGGER trigger_name ON ( table | view ) [ WITH ENCRYPTION ] { ( FOR | AFTER | INSTEAD OF ) { [ INSERT ] [ , ] [ UPDATE ] } [ NOT FOR REPLICATION ] AS { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ ...n ] | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] } sql_statement [ ...n ] } Перед тем как выполнять команду ALTER TRIGGER, следует убедиться в существовании в таблице table триггера trigger_name. Аргументы команды ALTER TRIGGER соответствуют аргументам команды CREATE TRIGGER, поэтому мы не будем лишний раз останавливаться на них. При написании триггера следует свести к минимуму обращения к внешним таблицам и другим объектам базы данных. Если пользователь удалит объект, на который ссылался триггер, то при следующем запуске триггера сервер выдаст сообщение об ошибке. SQL Server не отслеживает автоматически связь триггера с внешними объектами и не запрещает их удаление. Тем не менее, если после удаления объекта в базе данных был создан новый объект с таким же именем, то триггер будет ссылаться на новый объект. Если структура нового и старого объектов одинакова, то работа триггера не нарушится. Если же в структуру нового объекта внесены существенные изменения, то триггер необходимо соответствующим образом модифицировать.
FROM <спи Перечень источников: 1. Агальцов В. П. Базы данных: учебное пособие. – М.: Мир, 2002. 2. Вендров А. М. Проектирование программного обеспечения экон. информационных систем: Учебник. – М.: Финансы и статистика, 2002. – 352 с.: ил. 3. Администрирование Microsoft SQL Server. Учебный курс. Пер. с англ. – 2-е изд., испр. – М.: Издательско-торговый дом «Русская редакция», 2002 – 640 стр. 4. Горев А., Ахаян Р., Макашарипов С. Эффективная работа с СУБД. – СПб.: Питер, 1997 – 740 стр.
с Текущий контроль знаний студентов по данной теме предусматривает самостоятельное выполнение практических заданий с дальнейшей проверкой правильности их выполнения у доски.
Вопросы текущего контроля знаний по теме «Язык структурных запросов SQL. Хранимые процедуры и триггеры. Обеспечение достоверности, целостности и непротиворечивости данных»
1. Создать запрос, в котором необходимо вывести названия фильмов, жанр которых «Комедия», а также имена зрителей, поставивших им балл больше 6 2. Создать хранимую процедуру, которая выбирает информацию о названиях фильмов и их жанрах, балл которых равен 8 3. Создать хранимую процедуру, которая в случае просмотра какого-либо фильма зрителем добавляет соответствующую запись в таблицу «Рейтинг» 4. Создать хранимую процедуру, которая удаляет запись о просмотре указанного фильма указанным зрителем 5. Создать хранимую процедуру на добавление записей в таблицу «Фильм» Ененн65у По данной теме предусмотрена самостоятельная работа студентов в форме составления обобщающей таблицы «Понятия хранимой процедуры, триггера и соответствующие команды языка SQL для их создания», а также работа с лекционным материалом по дисциплине «Базы данных» с целью обобщения и систематизации знаний по теме «Ссылочная целостность. Операции, нарушающие ссылочную целостность базы данных и соответствующие стратегии для её поддержания».
По данной теме предусмотрена практическая работа. Практическая работа № 5. Тема: Работа с хранимыми процедурами
Вопросы практической работы № 5
1. Создать хранимую процедуру на добавление записей в таблицу-справочник (процедура должна содержать параметры, соответствующие полям таблицы) 2. Создать хранимую процедуру на удаление записей из таблицы-справочника (процедура должна содержать параметры, соответствующие полям таблицы) 3. Создать хранимую процедуру на редактирование записей в таблице-справочнике (процедура должна содержать параметры, соответствующие полям таблицы) 4. Создать триггер, который позволяет удалять запись из дочерней таблицы в случае удаления соответствующей записи в родительской таблице 5. Создать триггер, который в случае удаления из дочерней таблицы записи, устанавливает в NULL соответствующее значение ключа родительской таблицы 6. Выполнить все указанные выше операции для Вашей информационной системы (см. Приложение А)
Не нашли, что искали? Воспользуйтесь поиском по сайту: ©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.
|