авторефераты диссертаций БЕСПЛАТНАЯ БИБЛИОТЕКА РОССИИ

КОНФЕРЕНЦИИ, КНИГИ, ПОСОБИЯ, НАУЧНЫЕ ИЗДАНИЯ

<< ГЛАВНАЯ
АГРОИНЖЕНЕРИЯ
АСТРОНОМИЯ
БЕЗОПАСНОСТЬ
БИОЛОГИЯ
ЗЕМЛЯ
ИНФОРМАТИКА
ИСКУССТВОВЕДЕНИЕ
ИСТОРИЯ
КУЛЬТУРОЛОГИЯ
МАШИНОСТРОЕНИЕ
МЕДИЦИНА
МЕТАЛЛУРГИЯ
МЕХАНИКА
ПЕДАГОГИКА
ПОЛИТИКА
ПРИБОРОСТРОЕНИЕ
ПРОДОВОЛЬСТВИЕ
ПСИХОЛОГИЯ
РАДИОТЕХНИКА
СЕЛЬСКОЕ ХОЗЯЙСТВО
СОЦИОЛОГИЯ
СТРОИТЕЛЬСТВО
ТЕХНИЧЕСКИЕ НАУКИ
ТРАНСПОРТ
ФАРМАЦЕВТИКА
ФИЗИКА
ФИЗИОЛОГИЯ
ФИЛОЛОГИЯ
ФИЛОСОФИЯ
ХИМИЯ
ЭКОНОМИКА
ЭЛЕКТРОТЕХНИКА
ЭНЕРГЕТИКА
ЮРИСПРУДЕНЦИЯ
ЯЗЫКОЗНАНИЕ
РАЗНОЕ
КОНТАКТЫ


Pages:     | 1 |   ...   | 31 | 32 ||

«Е.Мамаев MS SQL SERVER 2000 Книга посвящена одной из самых мощных и популярных современных систем управления базами данных - Microsoft SQL Server 2000. ...»

-- [ Страница 33 ] --

GLOBAL П Использование этой опции предписывает создать глобальный курсор, кото рый будет существовать вплоть до закрытия соединения, в котором он был создан. При этом не важно, был ли создан курсор в хранимой процедуре, пакете или триггере. Однако, хотя курсор и называется глобальным, видно его будет только в пределах соединения, в котором он был создан.

(~ Замечание } В одном и том же соединении может быть создано два курсора с одинаковыми име нами. Это связано с тем, что локальный и глобальный курсоры считаются объекта ми различных типов, и поэтому допускается применение одинаковых имен для ло кального и глобального курсора. В этом случае при использовании курсора нужно дополнительно указывать тип курсора, с которым будет выполняться работа.

П FORWARD_ONLY Использование этого ключевого слова предписывает серверу создать курсор, из которого возможна только последовательная выборка в направлении от первой строки к последней.

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

• STATIC При указании этого параметра будет создан статический курсор.

• KEYSET Предписывает создание ключевого курсора.

Глава 31. Использование курсоров • DYNAMIC Предписывает создание динамического курсора.

• FAST_FORWARD Указание этого ключевого слова совместно с параметром READONLY позволя ет создавать курсор, оптимизированный для последовательного просмотра данных, т. е. когда строки выбираются друг за другом в направлении от пер вой к последней. Использование FAST_FORWARD не допускается совместно с параметрами SCROLL ИЛИ FOR_UPDATE. Кроме того, параметры FORWARD_ONLY И FAST_FORWARD ЯВЛЯЮТСЯ ВЗаИМОИСКЛЮЧЭЮЩИМИ.

• READ_ONLY Указание этого параметра позволяет запретить использование курсора для вы полнения любых изменений исходных данных (изменение или удаление). Ис пользование параметра READ_ONLY не обязательно для статического курсора.

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

• SCROLL_LOCKS При указании этого параметра сервер блокирует все строки, входящие в ре зультирующий набор. Это позволяет повысить скорость выполнения опера ций чтения и модификации данных с помощью курсора, т. к. не нужно будет ожидать каждый раз разблокирования нужных ресурсов. Одновременное ис пользование параметров FAST_FORWARD И SCROLLLOCKS не допускается.

О OPTIMISTIC Создание курсора с этим параметром позволяет контролировать попытки из менения через курсор данных, которые уже были изменены пользователями.

При этом не используется полное блокирование данных результирующего набора, как это происходит при использовании параметра SCROLL_LOCKS. ЕС ЛИ в исходной таблице имеется столбец timestamp, то он автоматически ис пользуется для отслеживания изменений. Если же такого столбца нет, то вы полняется подсчет контрольной суммы каждой строки результирующего набора курсора. Когда пользователь пытается произвести изменение или уда ление данных, происходит сравнение значений, хранимых во внутренних структурах курсора, со значениями строки. Если они совпадают, то измене ния выполняются. В противном случае пользователь получает сообщение о том, что исходные данные были изменены (другим пользователем или при ложением), и операция откатывается. Не допускается одновременное ис пользование ОПЦИЙ FAST_FORWARD И OPTIMISTIC.

П TYPE_WARNING Если тип курсора, указанный пользователем при его создании, по тем или иным причинам не может быть использован, то сервер выполняет неявное автоматическое преобразование типа курсора. При этом по умолчанию поль 1228 Часть V. Программирование зователь не получает никакого сообщения о выполняемых изменениях. Ис пользование параметра TYPEWARNING предписывает серверу в обязательном порядке информировать пользователя о выполняемом преобразовании типа курсора.

Приведем пример создания курсора с использованием переменной:

DECLARE @state char(2) SET @state='CA' DECLARE [auth s t a t e ] CURSOR SCROLL KEYSET LOCAL FOR SELECT au_id, au_lname, au_fname, s t a t e F O a u t h o r s RM W E E state=@state HR ( Замечание ) Порядок расположения строк в курсоре соответствует их порядку в результате вы борки, возвращаемой после выполнения ассоциированного с курсором запроса SELECT. Используя раздел ORDER BY, пользователи могут управлять порядком рас положения строк в курсоре.

Открытие курсора Одного объявления курсора недостаточно. Прежде чем приступить к использо ванию курсора, его необходимо открыть. Открытие курсора включает выполне ние ассоциированного с курсором запроса SELECT. Дальнейшие действия с по лученным результатом выборки зависят от типа курсора. Например, при откры тии статического курсора сервер помещает весь результирующий набор данных В системную базу ДаННЫХ Tempdb.

Открытие курсора производится с помощью команды OPEN, имеющей следую щий синтаксис:

OPEN {{[GLOBAL] cursor_name} | cursor_variable name} Параметр GLOBAL указывает, что открывается глобальный курсор. Указание этого параметра необходимо, если в соединении созданы локальный и глобальный кур соры, имеющие одинаковые имена. Если параметр GLOBAL не используется, то подразумевается, что пользователь работает с локальным курсором. Если выпол няется открытие глобального курсора, и в соединении нет локального курсора с таким же именем, то применение параметра GLOBAL также необязательно.

Имя открываемого курсора указывается с помощью параметра cursorname.

Имя курсора также можно указать с помощью переменной (параметр c u r s o r v a r i a b i e n a m e ). Независимо от типа указания имени курсора следует убедиться, что курсор объявлен. В противном случае будет выдано сообщение об ошибке.

В приведенном ниже примере выполняется открытие курсора, объявленного в предыдущем примере:

OPEN [auth state] Глава 31. Использование курсоров Выборка данных После открытия курсора пользователь может приступать к выборке или измене нию данных. В этом разделе будет рассмотрено только выполнение выборки данных из курсора, тогда как изменение и удаление данных будет рассмотрено в следующих разделах.

Для считывания строки данных из курсора используется команда FETCH, имею щая синтаксис:

FETCH [ [NEXT I PRIOR ] FIRST | LAST I ABSOLUTE {n I @nvar} I RELATIVE {n | tovar) ] FROM ] {{[GLOBAL] cursor_name} I @cursor_variable_name} [INTO @variable_name[,...n] ] Непосредственно после слова FETCH указывается строка, которую нужно вы брать. Выбираемую строку можно определить, указав либо ее абсолютную, либо относительную позицию. Затем следует ключевое слово FROM, а далее приводит ся имя курсора, из которого будут выбираться данные. После ключевого слова INTO указываются имена переменных, в которые будут помещены значения столбцов выбираемой строки курсора.

( Замечание ^) Прежде чем выбрать данные, сервер выполняет переход на указанную строку. То есть сначала указанная строка становится текущей, и только после этого произво дится выборка данных. При этом возможен выход за пределы диапазона строк. На пример, если текущей является последняя строка результирующего набора, а поль зователь пытается выбрать следующую строку, то такая попытка закончится неудачей.

Рассмотрим более подробно назначение и использование параметров команды:

П NEXT Выполняется переход на следующую строку, располагающуюся в результи рующем наборе непосредственно после текущей. Использование этой опции при первой выборке данных из курсора делает текущей первую строку ре зультирующего набора и возвращает данные, хранящиеся в этой строке. Если пользователь не указывает явно, из какой строки будет выполняться выбор ка, то станет использоваться опция NEXT, Т. е. данные будут считываться по следовательно.

• PRIOR В отличие от предыдущей опции, с помощью PRIOR осуществляется переход на предыдущую строку, из которой и считываются данные.

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

1230 * Часть V. Программирование LAST О Этот параметр служит для перехода на самую последнюю строку результи рующего набора курсора. Эта строка становится текущей, после чего из нее считываются данные.

• ABSOLUTE Параметр предназначен для перехода на строку с определенным номером (абсолютная адресация). Указанная строка становится текущей и из нее вы полняется считывание данных. Номер строки должен изменяться в пределах от 1 и до числа, соответствующего количеству строк в курсоре. Номер строки может быть указан двумя способами:

• п. Константа, определяющая номер строки.

• @nvar. Имя переменной, в которой задается номер строки, на которую нужно перейти.

О RELATIVE {П I gnvar} В отличие от предыдущего параметра, устанавливающего текущую строку с указанным абсолютным номером, использование параметра RELATIVE позво ляет делать текущей строку, расположенную на указанное число строк после или до текущей строки (относительная адресация). Подобно предыдущему параметру, смещение может задаваться как с помощью константы, так и с помощью переменной. При этом могут использоваться как положительные, так и отрицательные значения. Применение положительных значений позво ляет переместить указатель текущей строки на указанное количество строк к концу результирующего набора, тогда как указание отрицательного значения перемещает указатель на указанное количество строк к началу результирую щего набора. При использовании значения 0 указатель не смещается и по вторно считывается текущая строка.

• GLOBAL Указание этого параметра необходимо, если работа ведется с глобальным курсором и в текущем соединении имеется еще и локальный курсор с таким же именем, что и глобальный курсор. Если параметр GLOBAL не указан, то считывание данных выполняется из локального курсора. Если имя курсора уникально (т. е. имеется только один курсор с указанным именем), то ис пользование параметра GLOBAL не требуется.

П cursor_name Этот параметр подразумевает указание имени курсора, из которого осущест вляется выборка данных. К моменту выборки курсор должен быть уже от крыт. В противном случае будет выдано сообщение об ошибке.

О @cursor_variable_name Помимо указания имени курсора непосредственно, пользователь также мо жет задать имя курсора с помощью переменной, для чего и предназначен рассматриваемый параметр. Использование переменных для работы с курсо рами позволяет динамически создавать множество курсоров.

Глава 31. Использование курсоров О @variable_name[,...п] Этот параметр подразумевает указание имен переменных, в которые будут со хранены значения всех столбцов текущей строки курсора. Переменные должны быть предварительно объявлены и иметь тип данных, соответствующий типу данных столбцов курсоров. Допускается применение переменных типа данных, допускающего неявное преобразование типов данных столбцов курсора. Коли чество переменных, указанных после ключевого слова INTO, ДОЛЖНО В ТОЧНОСТИ соответствовать количеству столбцов курсора. Причем порядок указания пере менных должен соответствовать порядку столбцов курсора..

( Замечание ) Если в команде FETCH не указывается ключевое слово INTO И, соответственно, не приведен список переменных, в которые будут сохранены значения текущей строки курсора, то команда FETCH просто выведет соответствующие значения на экран по добно команде SELECT.

Приведем пример выборки данных из динамического курсора:

DECLARE cursl CURSOR LOCAL DYNAMIC SCROLL READONLY FOR SELECT au_id, au_lname, phone FROM authors WHERE state in CCA', 'UT') OPEN cursl DECLARE @ID char(ll), SLName char(40), @Phone char(12) FETCH FIRST FROM cursl INTO @ID, @LName, SPhone SELECT [ID автора]=@Ю, [Телефон] =@Phone, [Фамилия автора]=@LName FETCH NEXT FROM cursl INTO @ID, @LNarae, @Phone SELECT [ID автора]=@ID, [Телефон]=@Phone, [Фамилия автора]=@LName FETCH NEXT FROM cursl INTO @ID, @LName, SPhone SELECT [ID aBTopaJ=@ID, [Телефон]=@Phone, [Фамилия автора]=@LName FETCH NEXT FROM cursl FETCH NEXT FROM CUrsl FETCH NEXT FROM cursl Будет возвращен результат:

ID автора Телефон Фамилия автора 172-32-1176 408 496-7223 White (1 row(s) affected) ID автора Телефон Фамилия автора 213-46-8915 415 986-7020 Green (1 row(s) affected) ID автора Телефон Фамилия автора 1232 Часть V. Программирование 238-95-7766 415 548-7723 Carson (1 row(s) affected) au_id au_lname phone 267-41-2394 O'Leary 408 286- (1 row(s) affected) au id au_lname phone 274-80-9391 Straight 415 834- (1 row(s) affected) au id au_lname phone 409-56-7008 Bennet 415 658- (1 row(s) affected) Изменение данных Для выполнения изменений данных с помощью курсора предназначена команда UPDATE. Однако ее синтаксис несколько отличается от синтаксиса, используе мого при выполнении изменений обычным способом.

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

За одну операцию изменения данных, как и при работе с представлениями, до пускается изменение значений столбцов, расположенных в одной таблице. Если строка курсора строится на основе данных трех таблиц и необходимо изменить все значения строки курсора, то для этого придется выполнить три команды UPDATE.

При работе с курсорами применяется следующий синтаксис команды UPDATE:

UPDATE table_name SET {column_name={DEFAULT I NULL I expression}} [,...n] WHERE CURRENT OF cursor_name С помощью параметра t a b i e n a m e указывается имя таблицы, в которой необхо димо выполнить изменения. Список столбцов, в которых необходимо осущест вить изменения, а также значения, которые будут им присвоены, указываются после ключевого слова SET.

Раздел WHERE, в котором обычно определяется логическое условие, ограничи вающее диапазон обрабатываемых строк, содержит конструкцию CURRENT OF Глава 31. Использование курсоров cursorname. Эта конструкция позволяет однозначно идентифицировать строку исходной таблицы, в которой необходимо выполнить изменения. Вместо пара метра cursorname подставляется имя курсора, определяющего строку таблицы, для которой необходимо выполнить изменения. Допускается изменение любых столбцов таблицы, в том числе и не входящих в результирующий набор курсора.

В качестве примера рассмотрим изменение фамилии автора. В начале примера выведем исходное состояние данных. Затем создадим курсор и выполним необ ходимые изменения. После этого снова выведем строки таблицы и посмотрим на изменения.

BEGIN TRAN SELECT au_id, aulname, au_fname, state FROM authors WHERE state! = 'CA' DECLARE curs2 CURSOR LOCAL SCROLL FOR SELECT au_id, aulname, au_fname FROM authors WHERE state! = 'CA' OPEN curs FETCH FROM curs FETCH FROM curs FETCH FROM curs UPDATE authors SET state='XY\ au_lname=au_lname+' ' +LEFT (aufname, 1) + '. ' WHERE CURRENT OF curs FETCH FROM curs UPDATE authors SET state='YZ\ aulname=LEFT(au_fname, 1) +'.' + ' '+au_lname WHERE CURRENT OF curs SELECT au_id, au_lname, au_fname, state FROM authors WHERE state!='CA' ROLLBACK TRAN r Замечание Как видно, пример выполняется в виде транзакции. В конце программы выполняется откат транзакции и всех изменений, произведенных в ходе примера. Это сделано для того, чтобы сохранить данные таблицы authors в первоначальном виде, т. к.

они еще нам понадобятся для выполнения других примеров.

В результате работы приведенного кода будет получен результат:

au id аи Iname ' аи fname state Часть V. Программирование аи id аи lname аи fname state KS 341-22-1782 Smith Meander TN Morningstar 527-72-3246 Greene XY 648-92-1872 Blotchet-Halls R. Reginald YZ 712-45-1867 I. del Castillo Innes IN 722-51-5454 DeFrance Michel MD 807-91-6654 Panteley Sylvia ОТ 899-46-2035 Ringer Anne UT 998-72-3567 Ringer Albert (8 row(s) affected) Как видно, был изменен столбец, не входящий в результирующий набор курсора.

Удаление данных Удаление данных из курсора производится с помощью команды DELETE, имею щей синтаксис:

DELETE [FROM] table_name WHERE CURRENT OF cursor_name Назначение и использование параметров этой команды аналогично назначению и использованию аналогичных параметров команды UPDATE, которая была опи сана в предыдущем разделе.

При выполнении команды DELETE С приведенным синтаксисом происходит удале ние строки указанной таблицы, связанной с текущей строкой курсора. Предвари тельно необходимо сделать в курсоре текущей нужную строку. Если строка курсо ра формируется на основе значений нескольких таблиц, то для удаления всех данных необходимо выполнить команду DELETE отдельно для каждой таблицы.

Глава 31. Использование курсоров Изменим предыдущий пример, заменим команду UPDATE на команду DELETE И посмотрим к чему это приведет:

BEGIN TRAN SELECT au_id, au_lname, au_fname, state FROM authors WHERE state!='CA' DECLARE curs2 CURSOR LOCAL SCROLL FOR SELECT au_id, au_lname, au_fname FROM authors WHERE state!='CA' OPEN curs FETCH FROM curs FETCH FROM curs FETCH FROM curs DELETE authors WHERE CURRENT OF curs FETCH FROM curs DELETE authors WHERE CURRENT OF curs SELECT au_id, au_lname, au_fname, state FROM authors WHERE state!='CA' ROLLBACK TRAN аи fname Закрытие курсора После того, как все необходимые операции с использованием курсора были вы полнены, и хранящиеся в нем данные больше не нужны, можно закрыть кур сор. Закрытие курсора подразумевает освобождение всех областей памяти (как оперативной, так и в базе данных Tempdb), используемых для хранения данных курсора. Однако при этом не происходит удаления курсора как объекта. Закры тый курсор может быть снова открыт. При этом происходит повторное выпол нение запроса SELECT, ассоциированного с курсором.

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

( Замечание ^ Естественно, операция закрытия курсора может быть применена только к открытому курсору.

Закрытие курсора выполняется с помощью команды Transact-SQL:

CLOSE {{[GLOBAL] cursor_name} I @cursor_variable_name} Указание параметра GLOBAL необходимо, если работа ведется с глобальным кур сором, и в текущем соединении имеется еще и локальный курсор с таким же именем, что и локальный курсор. Если параметр GLOBAL не задан, то считыва ние данных выполняется из локального курсора. Если имя курсора уникально (т. е. имеется только один курсор с указанным именем), то использование пара метра GLOBAL не требуется.

Имя закрываемого курсора приводится с помощью параметра cursorname. Имя курсора может быть также указано с помощью переменной (параметр @cursor_variable_name).

Глава 31. Использование курсоров Освобождение курсора Операция закрытия курсора приводит к освобождению областей памяти, ис пользуемых для хранения результирующего набора курсора. Однако при этом курсор может быть повторно открыт. То есть курсор продолжает существовать как объект базы данных. Для полного удаления всей информации, ассоцииро ванной с курсором, включая его имя и код запроса SELECT, необходимо выпол нить освобождение курсора.

Если провести аналогию с таблицами, то закрытие курсора сравнимо с полным удалением всех строк таблицы. Однако таблица продолжает существовать как объ ект базы данных, имеющий определенную структуру и имя. Освобождение курсо ра сравнивается с удалением самой таблицы. Кода пользователь удаляет таблицу, то она перестает существовать как объект базы данных. После этого можно соз дать новую таблицу с таким же именем, но имеющую другую структуру.

Для освобождения курсора используется команда DEALLOCATE, имеющая синтаксис:

DEALLOCATE {{[GLOBAL] cursor_name} I @cursor_variable_name} Назначение параметров этой команды аналогично одноименным параметрам команды объявления, открытия, выборки или закрытия курсора, которые рас сматривались ранее в этой главе.

Дополнительные средства В Transact-SQL имеется набор дополнительных средств, незаменимых при рабо те с курсорами. Например, часто необходимо знать, как много строк содержится в курсоре. Количество строк, имеющихся в последнем открытом в соединении курсоре, может быть получено с помощью функции @@CURSOR_ROWS. ЕСЛИ В со единении создается более одного курсора, то после открытия очередного курсо ра рекомендуется сохранять значение функции @@CURSOR_ROWS В локальной пе ременной. В противном случае после открытия следующего курсора функция @@CURSOR_ROWS будет возвращать значения для нового курсора.

Если функция @@CURSOR_ROWS возвращает значение 0, то либо в соединении не было открыто ни одного курсора, либо последний открытый курсор не содер жит ни одной строки, либо последней операцией работы с курсором было за крытие или освобождение курсора.

Если возвращается значение —1, то был открыт динамический курсор. Возвра щение значения меньше — 1, говорит о том, что открыт ключевой курсор. Абсо лютное значение показывает, сколько ключей имеется в курсоре. Возвращение положительного числа говорит об открытии статического курсора, в котором имеется указанное количество строк.

При последовательном обращении к строкам курсора необходимо определять, не выполняется ли обращение к строке, выходящей за границы курсора. На пример, если текущей строкой является 3-я строка, а пользователь пытается обратиться на 5 строк назад, то такая операция будет невыполнима. Конечно, 1238 Часть V. Программирование можно отслеживать попытки обращения к строкам курсора программно, осно вываясь на количестве строк в курсоре, однако это не всегда удобно.

В Transact-SQL имеется специальная функция @@FETCH_STATUS, возвращающая результат выполнения последней команды выборки данных из курсора. Если функция возвращает значение 0, то последняя операция была выполнена ус пешно. Если возвращается значение —1, то это означает, что была предпринята попытка выборки строки, находящейся за пределами результирующего набора.

Например, выборка строки с абсолютным номером - 4, или номером, превы шающим значение, возвращенное для курсора функцией @@CURSOR_ROWS.

Если функция @@FETCH_STATUS возвращает значение —2, то это означает, что пользователь попытался выбрать поврежденную строку (row missing). Это проис ходит, когда из ключевого курсора производится выборка строки, удаленной другим пользователем после открытия курсора.

( Замечание ) При работе с несколькими курсорами следует учитывать, что функция @@FETCH_STATUS возвращает результат выполнения последней команды F E T C H.

При этом не важно, с каким именно курсором ведется работа. Пользователь строит алгоритм таким образом, чтобы проверка результата выполнения выборки данных выполнялась как можно ближе к команде F E T C H.

В качестве примера рассмотрим использование функции @@FETCH STATUS ДЛЯ обнаружения границы курсора:

DECLARE curs3 CURSOR LOCAL STATIC FOR SELECT au_lname FROM authors OPEN curs DECLARE @LName char(30), @Rs varchar(40) SET @Rs='' FETCH FIRST FROM curs3 INTO SLName WHILE @@FETCH_STATUS= BEGIN SET @Rs=@Rs+LEFT(@LName,l) FETCH FROM curs3 INTO SLName END SELECT @RS CLOSE curs DEALLOCATE curs Будет возвращен следующий результат:

BBCDdDGGGHKLMMOPRRSSSWY (1 row(s) affected) Глава Триггеры Часто разработчикам приходится реализовывать сложные алгоритмы поддержки целостности данных. В предыдущих главах рассматривалось использование ог раничений целостности Primary Key, Foreign Key, Unique, правил, умолчаний и т. д. Эти средства предоставляют разработчикам достаточно эффективные ме ханизмы обеспечения целостности данных. Однако все же их часто бывает не достаточно. Например, с помощью упомянутых механизмов нельзя разрешить изменение данных в том случае, если в одном из столбцов находится опреде ленное значение.

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

Использование триггеров Триггеры (triggers) SQL Server 2000 представляют собой набор команд Transact SQL, выполняемых автоматически при осуществлении тех или иных модифика ций данных в таблице. Физически триггеры являются ни чем иным, как храни мыми процедурами специального типа. Каждый триггер связан с конкретной таблицей и запускается сервером автоматически каждый раз, когда пользователи пытаются произвести вставку, изменение или удаление данных. Триггер получа ет всю информацию о выполняемых пользователем изменениях в таблице. Раз работчик реализовывает в триггере необходимые проверки и изменения данных в других таблицах базы данных.

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

1240 Часть V. Программирование ( Замечание J В SQL Server 2000 триггеры могут быть созданы не только для таблиц, но и для представлений.

Существует несколько типов триггеров, каждый из которых реагирует на опре деленный тип изменений данных. В SQL Server 2000 существует три типа триг геров, классифицирующихся по названию команд, на которые они реагируют:

П INSERT TRIGGER. Триггеры этого типа вызываются при попытке пользова теля добавить данные в таблицу, например, с помощью команды INSERT.

• UPDATE TRIGGER. Этот тип триггеров выполняется при изменении данных с помощью команды UPDATE.

• DELETE TRIGGER. Этот тип триггеров выполняется при удалении данных с помощью команды DELETE.

(_ Замечание Триггеры не запускаются при выполнении команд UPDATE TEXT И WRITE TEXT, ВЫ ПОЛНЯЮЩИХ обработку больших блоков данных (столбцов с типом данных image, t e x t и ntext). Однако если обработка этих данных производится с помощью ко манд UPDATE, вызов триггеров все же осуществляется.

Помимо классификации триггеров по типу операции, на которую они реагиру ют, они также различаются по поведению:

П AFTER. Это стандартный тип триггеров, активно используемый в предыду щих версиях SQL Server. По умолчанию триггер SQL Server 2000 имеет именно этот тип.

О INSTEAD OF. Триггеры этого типа выполняются взамен пользовательских действий. То есть запрос пользователя не выполняется, а вносятся лишь из менения, осуществляемые в теле триггера.

В версиях SQL Server ранее 7.0 в каждой таблице разрешалось определять толь ко один триггер каждого типа. Однако, начиная с SQL Server 7.0, для одной таблицы допускается создавать множество, триггеров каждого типа. Это позволя ет реализовывать сложные многоуровневые механизмы контроля целостности данных. Теперь разработчик может не создавать один большой триггер, вклю чающий множество проверок, а организовать несколько небольших триггеров, каждый из которых выполняет отдельную проверку.

Триггеры весьма широко используются при разработке промышленных систем.

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

Глава 32. Триггеры Разработчики могут использовать триггеры для решения самых разнообразных задач. Однако выполнение триггеров является довольно "тяжелой" задачей и по возможности нужно избегать их использования. Если задачу можно решить на уровне ограничений целостности, пакета или хранимой процедуры, то так и следует сделать. Триггеры можно использовать лишь тогда, когда они дают зна чительный выигрыш функциональности и предлагают значительные удобства для пользователей.

(~ Замечание ( Сервер на все время выполнения триггера удерживает блокировки на данные, к ко торым обращается этот триггер. Это является еще одной причиной, по которой сле дует осторожно использовать триггеры.

Ограничения использования триггеров Хотя в триггере допускается применение довольно большого количества команд Transact-SQL, тем не менее, все же существуют некоторые ограничения на вы полняемые операции. Эти ограничения связаны с тем, что сервер должен гаран тировать соблюдение требований ACID. To есть в триггере должны выполняться только те операции, которые могут быть отменены в случае отката транзакции.

Однако в SQL Server 2000 существует набор операций, для которых не реализо ваны механизмы отката. Ярким примером является восстановление резервной копии базы данных. Но вряд ли понадобится выполнять восстановление резерв ной копии базы данных в триггере.

Замечание Кроме того, ограничения на использование команд могут быть также вызваны тем, что ряд команд должен выполняться как первая команда пакета, что не реализуемо в триггере.

Приведем список тех операций, выполнение которых в триггере неразрешено:

• создание и удаление правил (CREATE RULE И DROP RULE);

П создание и удаление умолчаний (CREATE DEFAULT И DROP DEFAULT);

П создание и удаление индексов (CREATE INDEX И DROP INDEX);

П создание, изменение и удаление триггеров (CREATE TRIGGER, ALTER TRIGGER и DROP TRIGGER);

П создание, изменение и удаление хранимых процедур (CREATE PROCEDURE, ALTER PROCEDURE И DROP PROCEDURE);

• создание, изменение и удаление таблицы (CREATE TABLE, ALTER TABLE И DROP TABLE);

• создание, изменение и удаление представления (CREATE VIEW, ALTER VIEW И DROP VIEW);

1242 Часть V. Программирование • создание, изменение и удаление базы данных (CREATE DATABASE, ALTER DATABASE И DROP DATABASE);

• обновление статистики (UPDATE STATISTICS);

• создание схемы (CREATE SCHEMA);

• усечение таблицы (TRUNCATE TABLE);

• управление правами доступа пользователей к объектам базы данных (GRANT, REVOKE и DENY);

П восстановление резервной копии базы данных или журнала транзакций (RESTORE DATABASE, RESTORE LOG, LOAD DATABASE И LOAD LOG);

• выполнение команд RECONFIGURE, DISK RESIZE И DISK I N I T.

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

Создание триггера Как и при написании хранимых процедур, перед созданием триггера следует тщательно продумать алгоритм выполнения изменений и проверок данных, а также согласовать последовательность этих действий с другими триггерами и хранимыми процедурами. Дело в том, что триггеры способны длительное время блокировать значительную часть ресурсов, и неправильно написанные триггеры могут создавать мертвые блокировки (deathlocks).

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

Для создания триггера используется команда CREATE TRIGGER, имеющая синтак сис:

CREATE TRIGGER trigger_name ON {table I view} [WITH ENCRYPTION] { { FOR [{ AFTER | INSTEAD OF }] { [DELETE] [ ] [INSERT] [ ] [UPDATE] },, [ WITH APPEND ] [ NOT FOR REPLICATION ] ' AS [ { IF UPDATE (column) [ { AND | OR ) UPDATE (column) ] [...П ] I IF (COLUMNS_UPDATED() { bitwise_operator ) updated_bitmask ) ( comparison_operator } column_bitmask [...n ] } ] sql statement [... n ] } Глава 32. Триггеры Замечание Создание триггера должно выполняться как отдельная команда, т. е. триггер не мо жет быть создан внутри хранимой процедуры. Как видно из синтаксиса, триггер мо жет быть создан только в текущей базе данных. Однако в его теле могут произво диться обращения к таблицам различных баз данных, возможно расположенных на различных серверах.

Рассмотрим назначение и использование параметров команды:

О trigger_name Этот параметр подразумевает указание имени триггера. При выборе имени нужно следовать стандартным правилам именования объектов SQL Server 2000.

При необходимости имя триггера может быть заключено в ограничители.

Имя указывается в формате Unicode и максимальная его длина составляет 128 символов. Дополнительно можно указать имя владельца триггера. Однако указание имени базы данных не допускается.

• { table I view } Имя таблицы или представления текущей базы данных, к которой будет при вязан триггер. Дополнительно разрешается указание имени владельца, одна ко указание имени базы данных не разрешается. Указанная таблица или представление должны существовать к моменту создания триггера.

(~~ Замечание ^ Триггер не может быть создан для системной таблицы. Кроме того, не допускается создание триггеров в системных базах данных.

• WITH ENCRYPTION При указании этой опции сервер будет выполнять шифрование кода, ис пользуемого для создания триггера. Этот код хранится в системной таблице syscomments, имеющейся в каждой базе данных. Шифрование обычно при меняется, когда необходимо скрыть от пользователя информацию о том, ка кие операции выполняются в триггере.

П FOR После этого ключевого слова указывается тип создаваемого триггера. Допус тимы варианты:

• AFTER. Будет создан стандартный триггер. Если не указано ни AFTER, НИ INSTEAD OF, TO ПО уМОЛЧЭНИЮ ИСПОЛЬЗуеТСЯ AFTER.

• INSTEAD OF. Создаваемый триггер будет выполняться взамен команды, приведшей к запуску триггера. Только один триггер этого типа может быть определен для каждой таблицы или представления. Путем создания для представления триггеров INSTEAD OF МОЖНО разрешить пользователям выполнять изменение данных представления, принадлежащих более чем одной таблице. Механизмы выполнения этих изменений должны быть реализованы непосредственно в теле триггера.

1244 Часть V. Программирование DELETE. Создаваемый триггер будет вызываться при попытке удаления • данных из указанной таблицы.

• INSERT. Создаваемый триггер будет вызываться при попытке добавления в указанную таблицу новых строк с помощью команды.

• UPDATE. Создаваемый триггер будет вызываться при попытке изменения данных в указанной таблице.

• WITH APPEND Использование этой опции необходимо только для баз данных, имеющих уровень совместимости (compatibility level) ниже чем 70, т. е. база данных ра ботает в режиме SQL Server 6.x, допускающем создание в каждой таблице всего одного триггера. При использовании опции WITH APPEND ДЛЯ баз дан ных с уровнем совместимости 60 или 65 для каждой таблицы будет разреше но создание более одного триггера каждого-типа. Это бывает необходимо во время применения репликации сведением, использующей триггеры для сбора данных. Если уровень совместимости базы данных 70, то команда CREATE TRIGGER ИСПОЛЬЗует ОПЦИЮ WITH APPEND ПО уМОЛЧЭНИЮ.

• NOT FOR REPLICATION При указании этой опции триггер не будет вызываться, если модификация данных (вставка, изменение или удаление) производится средствами подсис темы репликации. Это необходимо для триггеров репликации сведением, чтобы избежать повторного считывания данных. Однако эта возможность может быть использована и пользователями.

о AS Ha этом ключевом слове заканчивается определение триггера, и описывают ся действия, которые он будет производить, а также некоторые дополнитель ные условия выполнения триггера.

• IF UPDATE (column) С помощью этой конструкции можно разрешить выполнение триггера толь ко при осуществлении изменений в определенном столбце таблицы. Имя нужного столбца указывается с помощью параметра'column. Однако исполь зование этой возможности допускается только для триггеров типа UPDATE И INSERT. Если необходимо разрешить вызов триггера при изменении более чем одного столбца, то можно указать имена дополнительных столбов с по мощью параметра UPDATE (column), объединив множество таких параметров с помощью логических операторов OR ИЛИ AND.

Замечание Функция UPDATE (column) применяется внутри триггеров и возвращает значение TRUE, если производится изменение указанного столбца, и F A L S E — в противном случае. Та ким образом можно определить, выполняется ли изменение конкретного столбца.

Глава 32. Триггеры П IF (COLUMNS J J P D A T E D О... ) Эта конструкция является вторым способом выполнения проверки измене ний определенных столбцов. Функция COLUMNS J J P D A T E D о возвращает дво ичное значение, каждый бит которого соответствует одному столбцу. Самый правый бит соответствует самому левому столбцу таблицы. Если выполняется изменение в одном из столбцов, то в возвращаемом функцией COLUMNS UPDATED)) двоичном значении соответствующий бит будет установ лен в 1.~В противном случае бит устанавливается в 0. Анализируя полученное значение, можно определить, какие именно столбцы пытается изменить пользователь. Вся же конструкция IF (COLUMNS JJPDATEDО... ) позволяет выполнять триггер только тогда, когда изменяются определенные столбцы.

При необходимости произвести проверку множества столбцов эта конструк ция является более удачным решением, чем использование конструкции IF UPDATE (column), т. к. она более компакта в использовании. Рассмотрим па раметры, применяемые для проверки изменения нужных столбцов:

• b i t w i s e o p e r a t o r. Этот параметр подразумевает указание оператора по битовой обработки. Возвращаемое функцией COLUMNS JJPDATEDо двоич ное значение подвергается дополнительной обработке для выделения из него флагов изменения отдельных столбцов. Для этого обычно использу ется оператор & (побитовое AND). НО также допускается использование и любых других побитовых операторов, хотя область их применения до вольно узка.

• updated_bitmask. Этот параметр подразумевает указание константы, оп ределяющей маску анализа изменяемости столбцов. Если используется побитовый оператор &, то в маске необходимо установить биты, соответ ствующие столбцам, при изменении которых должен выполняться триг гер. Например, если таблица имеет 6 столбцов, но пользователь изменяет лишь 1, 3 и 4 столбцы, то функция COLUMNSJJPDATEDO возвратит деся тичное значение 13 (двоичное 001101). Если триггер должен выполняться при изменении 1 и 4 столбцов, то маска должна быть равна 9 (двоичное 001001). В качестве же побитового оператора нужно использовать &. Тогда общий синтаксис будет выглядеть как (COLUMNS_UPDATEDO & 9).

• comparison_operator. После того, как возвращенное функцией COLUMNS_UPDATED( ) двоичное значение будет обработано побитово, полу ченный результат необходимо проверить — действительно ли выполняет ся изменение нужных столбцов. Параметр comparisonoperator подразу мевает указание оператора сравнения. Обычно указывается оператор =, но также могут использоваться и другие операторы (,,! = и т. д.).


• column_bitmask. Этот параметр подразумевает указание битовой маски столбцов, с которой будет сравниваться значение, полученное после вы полнения выражения (COLUMNSJJPDATEDO {bitwise_operator} updated^ bitmask). Триггер будет вызван только в том случае, если выполняется указанное условие. Продолжим рассмотрение предыдущего примера. Если необходимо, чтобы триггер выполнился только при изменении как 1, так 1246 Часть V. Программирование и 4 столбцов, то полное условие будет выглядеть как IF (COLUMNSJJPDATEDO & 9) = 9. Если же необходимо выполнить триггер, когда производится изменение либо 1, либо 4 столбца, то нужно исполь зовать условие (COLUMNS_UPDATED() & 9) 0.

• [... п ]. Этот параметр говорит о том, что можно использовать множество проверок на то, какие столбцы были изменены.

О sql_statement [...п] Этот параметр подразумевает указание команд Transact-SQL, которые будут выполняться при вызове триггера. Могут применяться команды цикла, вы борки данных, изменения, вставки и удаления строк. Внутри триггера также можно создавать транзакции. Список операций, которые не разрешено вы полнять в теле триггера, был приведен в предыдущем разделе.

Модификация триггера Как бы хорошо вы ни планировали код триггера, все же иногда бывает необхо димо внести в его код те или иные изменения. Это может быть вызвано обна ружением ошибки или изменением структуры таблицы, к которой привязан триггер. При выполнении изменения триггера сервер просто обновляет его код в системной таблице syscomments. При следующем вызове сервер будет исполь зовать новый код.

Для изменения параметров и кода триггера предназначена команда ALTER TRIGGER, имеющая синтаксис:

ALTER TRIGGER t r i g g e r _ n a m e ON ( t a b l e I view) [WITH ENCRYPTION] { { (FOR I AFTER I INSTEAD OF) { [DELETE] [,] [INSERT] [,] [UPDATE] } [NOT FOR REPLICATION] AS s q l _ s t a t e m e n t [... n ] } I { (FOR I AFTER I INSTEAD OF) { [INSERT] [,] [UPDATE] } [NOT FOR REPLICATION] AS ( IF UPDATE (column) [ {AND I OR} UPDATE (column) ] [... n ] I IF (COLUMNSJJPDATEDO { b i t w i s e _ o p e r a t o r } u p d a t e d _ b i t m a s k ) {comparison_operator} columnjbitmask [... n ] } sql_statement [...n] }} Назначение и использование параметров команды ALTER TRIGGER аналогично одноименным параметрам команды CREATE TRIGGER И В ЭТОМ разделе рассмат риваться не будет.

При выполнении изменений следует учитывать, что нужно указывать все необ ходимые опции. Например, если при создании триггера использовалась опция WITH ENCRYPTION (шифрование кода триггера), то при изменении триггера также следует указать эту опцию, если необходимо сохранить код в зашифрованном виде. В противном случае код будет сохранен в открытом виде. Это касается и всех других опций.

Глава 32. Триггеры Правом изменения триггера обладают члены фиксированных ролей базы данных dbowner и db_ddladmin, а также владелец таблицы. Право изменения триггера не может быть предоставлено другим пользователям. Если все же необходимо, чтобы изменение триггера выполнил определенный пользователь, то можно сделать его владельцем таблицы или включить в одну из указанных ролей.

Удаление триггера Если триггер больше не нужен или, более того, начал мешать нормальной рабо те, то его можно удалить. Для этого используется команда Transact-SQL:

DROP TRIGGER { t r i g g e r } [,...n] Единственный параметр этой команды, t r i g g e r, определяет имя триггера, ко торый необходимо удалить. С помощью одной команды DROP TRIGGER МОЖНО удалить множество триггеров, перечислив их через запятую.

Правом удаления триггера обладает владелец таблицы, к которой относится триггер, а также все члены фиксированных ролей базы данных dbowner и db_ddladmin.

Программирование триггеров В разд. "Создание триггера"был приведен синтаксис команды CREATE TRIGGER, С помощью которой выполняется создание триггера. Однако там не было практи чески ничего сказано об особенностях написания самого тела триггера. Хотя в начале главы и было сказано, что триггеры являются специальным типом хра нимых процедур, программированием триггеров имеет некоторую специфику по сравнению с программированием хранимых процедур. Ранее уже было сказано, что в триггере можно использовать большую часть команд Transact-SQL. В на чале главы также был приведен список операций и команд, которые не разре шено выполнять в теле триггера.

В этом же разделе будут рассмотрены средства, предлагаемые Transact-SQL спе циально для триггеров, позволяющие реализовывать высоко функциональные алгоритмы обработки данных.

При запуске триггера для соединения ODBC сервер автоматически меняет неко торые параметры конфигурации, выполняя для триггера следующие команды:

• SET QUOTED_IDENTIFIER ON — ДВОЙНЫе КЭВЫЧКИ будут ЯВЛЯТЬСЯ аНЭЛОГОМ квадратных скобок, т. е. заключенные в двойные кавычки строки станут рас сматриваться как имена объектов;

• SET TEXTSIZE 2147483647 — устанавливает размер строк, возвращаемых ко мандой SELECT при выборке данных из столбцов с типом данных t e x t и ntext;

П SET ANSIDEFAULTS ON — внутри триггера будут использоваться стандартные значения параметров ANSI;

1248 Часть V. Программирование П SET CURSOR_CLOSE_ON_COMMIT OFF — запрещает автоматическое закрытие курсора при фиксировании транзакции;

П SET IMPLICITJTRANSACTIONS OFF — запрещает неявное начало транзакций.

Пользователь должен будет явно инициировать транзакцию, указывая коман ду BEGIN TRAN.

Описанные параметры являются всего лишь параметрами по умолчанию, авто матически устанавливаемыми сервером для триггера. Однако внутри триггера указанные параметры могут быть легко изменены с помощью команды SET.

Хорошим тоном является написание триггеров, которые не обращаются ни к каким объектам базы данных кроме таблицы, с которой они ассоциированы.

Если триггер обращается к внешней таблице, представлению или хранимой процедуре, то изменение этих объектов может привести к нарушению функцио нирования триггера.

Также не рекомендуется написание триггеров, возвращающих данные с помо щью команды SELECT. Дело в том, что многие приложения анализируют содер жимое результата выборки, возвращаемое при выполнении того или иного за проса. При этом данные, возвращенные триггером, могут быть неправильно восприняты приложениями. Однако команда SELECT может быть с успехом ис пользована для присвоения значений переменным.

В теле триггера можно вызывать любые хранимые процедуры, в том числе и сис темные. Однако эти процедуры также должны следовать описанным правилам.

В Transact-SQL имеется набор функций, позволяющих получать дополнитель ную информацию о триггере:

• COLOMNS_UPDATED (). Эта функция возвращает двоичное значение, каждый бит которого соответствует одному столбцу связанной с триггером таблицы.

В этом значении устанавливаются в 1 биты, соответствующие столбцам, в ко торых выполняется изменение значений. Более подробно использование этой функции было описано при рассмотрении синтаксиса команды CREATE TRIGGER в одном из предыдущих разделов этой главы.

П UPDATE (column). С помощью этой функции можно узнать, был ли изменен кон кретный столбец. Функция UPDATE (column) возвращает булево значение. Эта функция может быть с успехом использована вместо предыдущей функции.

• TRIGGER_NESTLEVEL ( [ o b j e c t _ i d ] ). С П М Щ Ю ЭТОЙ фуНКЦИИ МОЖНО ОПреДе ООЬ лить уровень вложенности триггера. Параметр o b j e c t i d определяет иден тификационной номер триггера как объекта базы данных. Для получения этого идентификационного номера используется функция O B J E C T I D (trigger_name). Применяя функцию TRIGGER_NESTLEVEL, МОЖНО контроли ровать, вызывается ли триггер непосредственно или вследствие изменения данных таблицы другим триггером.


До сих пор не было сказано о том, как же в триггере будет выполняться кон троль изменений, производимых пользователем. Для отслеживания этих изме нений сервер автоматически создает при вызове триггера две специальных таб Глава 32. Триггеры лицы — i n s e r t e d и deleted. Рассмотрим, какие данные содержатся в этих таб лицах при выполнении различных операций:

О INSERT. При вызове триггера этого типа таблица i n s e r t e d содержит список строк, вставляемых пользователем. При успешном завершении триггера (фиксации транзакции) все строки из таблицы i n s e r t e d переносятся в пользо вательскую таблицу базы данных. В таблице deleted не содержится никаких данных, и обращаться к этой таблице в триггере INSERT не имеет смысла.

П DELETE. Для триггеров этого типа в таблице d e l e t e d приводится список всех строк, которые будут удалены. В таблице i n s e r t e d не содержится никаких данных, и анализировать эту таблицу не нужно.

О UPDATE. При выполнении триггеров этого типа данные имеются как в таблице inserted, так и в таблице deleted. Последняя из них — список всех строк таблицы, которые пытается изменить пользователь. В таблице inserted указы ваются строки, которые будут внесены в таблицу вместо соответствующих строк таблицы deleted. To есть сервер как бы удаляет старые строки и встав ляет вместо них измененные строки. Анализируя строки в таблицах inserted и deleted, можно отследить все изменения, выполняемые пользователями.

Таблицы inserted и deleted не существуют физически, а представляют логиче ские структуры, создаваемые сервером индивидуально для каждого триггера. В эти таблицы запрещено вносить любые изменения, так что они доступны в режиме "только для чтения". Эти таблицы представляют своего рода частичную копию журнала транзакций, в которой отображается всего лишь одна транзакция.

В таблице deleted отображается состояние, которое данные имели до начала транзакции, тогда как в таблице i n s e r t e d — состояние, в котором будут нахо диться данные после фиксирования изменений. Сама же таблица, в которой производятся изменения, находится в состоянии, в котором она бы была после фиксирования всех изменений. В теле триггера разрешается вносить любые из менения в данные этой таблицы, причем даже в те строки, которые не были изменены пользователем.

Триггер может анализировать содержимое таблиц i n s e r t e d и deleted для при нятия решения о фиксировании или отмене транзакции и внесении различных изменений в связанную с триггером таблицу, а также в любые другие таблицы, расположенные как в текущей, так и в других базах данных.

Пример создания триггера В завершение этой главы приведем пример создания триггера, отвергающего изменение данных. Для этого сначала создадим новую таблицу smaiitities', в которую скопируем часть столбцов таблицы t i t l e s :

SELECT title_id, title=CAST(title as nvarchar(30))+ CASE WHEN LEN (title) 30 THEN '...' ELSE '" END, •: V type, price, sales = ytd_sales. -.-•••.,• --.

INTO smaiitities.. ;

FROM titles Часть V. Программирование Просмотрим содержимое созданной таблицы:

SELECT * F O s m a l l t i t l e s RM Будет получен следующий результат:

title id t i t l e type BU1032 The Busy Executive's Database...

BU1111 Cooking with Computers: Surrep...

BU2075 You Can Combat Computer Stress...

BU7832 Straight Talk About Computers MC2222 Silicon Valley Gastronomic Tre...

MC3021 The Gourmet Microwave MC3026 The Psychology of Computer Coo...

PC1035 But Is It User Friendly?

PC8888 Secrets of Silicon Valley PC9999 Net Etiquette PS1372 Computer Phobic AND Non-Phobic...

PS2091 Is Anger the Enemy?

PS2106 Life Without Fear PS3333 Prolonged Data Deprivation: Fo...

PS7777 Emotional Security: A N e w A l g o...

TC3218 Onions, Leeks, and Garlic: Coo...

TC4203 Fifty Years in Buckingham Pala...

TC7777 Sushi, Anyone? _ (18 row(s) affected) Теперь создадим триггер, который будет запрещать изменение названия серий popuiarcomp и psychology, а также разрешим изменение книг тиражом более 10 000 экземпляров только пользователю sa. Кроме того, запретим всякое изме нение столбца t i t i e i d, в котором хранится идентификационный номер книги.

Код триггера будет таким:

CREATE TRIGGER smalltitles_triggerl ON smalltitles WITH ENCRYPTION FOR UPDATE AS IF UPDATE(title_id) BEGIN IFROLLBACK (SELECT title_id i AND inserted)deleted книги запрещено', WHERE typeTRAN=('popular_comp', 'psychology')) ENDd.title_id psychologyназвания type 1 IN ('popular_comp', 'psychology') SELECT * TRAN FROM EXISTS(SELECTсерий popular_comp и RAISERROR type deleted идентификационного номера RETURN * FROM AND inserted WHERE *WHERE ROLLBACK FROM deleted as FROM d type!= IN UPDATE(type) i.title_id) as (SELECT запрещено', FROM BEGIN RAISERROR IN1,11) SELECT ('Изменение WHERE title_ID NOT,11) Глава 32. Триггеры RETURN END IF EXISTS(SELECT * FROM deleted WHERE sares10 000) AND SUSER_SID()!=SUSER_SID('sa') BEGIN RAISERROR ('Изменение книг тиражом более 10 000 разрешено только пользователю sa', 1,11) SELECT * FROM deleted WHERE sales ROLLBACK TRAN RETURN END COMMIT TRAN RETURN Теперь попытаемся выполнить изменение столбца t i t l e i d :

UPDATE smalltitles SET title_id=LEFT(title_id, 5) WHERE LEFT (title, 1) IN ('С, 'Т', 'L') Будет получен результат:

Msg 50000, Level 1, State Изменение идентификационного номера книги запрещено title id title type price sales (6 row(s) affected) I Причем триггер отклонит попытку изменить идентификационный номер на то же самое значение:

U D T smalltitles S T t i t l e _ i d = title_id P AE E Однако на этот раз будет возвращен следующий результат:

Msg 50000, Level I, State Изменение идентификационного номера книги запрещено title_id title type price sales (0 row(s) affected) Такое поведение триггера обусловлено логикой проверки изменений. Избавить ся от этой проблемы легко. Достаточно Добавить в первую проверку триггера дополнительное условие, в котором будет выясняться существование хотя бы одной строки с измененным идентификационным номером.

Теперь же попытаемся изменить название серии для книги с идентификацион ным номером РС8888 (серия popular_comp):

UPDATE s m a l l t i t l e s SET type = *new_comp' W E E t i t l e _ i d = 'PC8888' HR Часть V. Программирование Будет получен результат:

Msg 50000, Level I, State Изменение названия серий popular_comp и psychology запрещено title_id title. type price Предметный указатель Предметный указатель FULL OUTER FULLTEXTCATALOGPROPERTY FULLTEXTSERVICEPROPERTY 1108 LEFT LEFT OUTER LEN G LIKE LOG GETANSINULLllll LOG10 GETDATE() 1105 logread.exe 40, GO 1087 LOWER GOTO 1085 LTR1M GRANT Grid Pane M GROUP BY guest makepipe.exe GUID 609, 1097 Master MAX MIN H Model money HAS_DBACCESS MONTH HAVING Msdb HOSTJDO msmerge_contents 610, HOST_NAME() 1 msmerge_tombsttfne msrepl_transactions I N IAM IDENTJNCR1111 nchar 1095, IDENT_SEED 1111 NEWID() IDENTITY 956, 1111 NO ACTION IDENTITYCOL 1161 NOT IF...ELSE 1083 ntext NULL IN NULLIF INDEX_COL numeric INDEXPROPERTY nvarchar INNER INSENSITIVE О INSERT 325, int INTO 1165 OBJECT_NAME IS_MEMBER1108 OBJECTPROPERTY IS_SRVROLEMEMBER 1108 odbccmpt.exe odbcping.exe ISDATE OPEN ISNULL 1070, OPTION ISNUMERIC OR isql.exe ORDER BY isqlw.exe osql.exe itwiz.exe OUTPUT К KEEP PLAN 1199 PARSENAME KEEPFIXED PLAN 1199 PATINDEX Предметный указатель / SELECT...INTO PERCENT SERVERPROPERTY PERMISSIONS SESSIONJJSER PI SET:

POWER ANSI_NULL_DFLT_OFF PRINT ANSI_NULL^DFLT_ON processadmin DEADLOCK_PR1OR1TY Profiler IDENTITYJNSERT 1MPLICIT_JRANSACT1ON OFF ON qrdrsvc.exe 584 LOCK_TIMEOUT QUOTENAME 1109 QUOTEDJDENTIFIER REMOTE_PROC_JRANS R TRANSACTION ISOLATION LEVEL setupadmin Setupsql.

exe 75, RADIANS SHUTDOWN RAID SID 203, RAND SIGN READ ONLY SIN readpipe.exe real 1091 smalldatetime rebuildm.exe 40, 120, 540 ' smallint RECOVERY 774 small money Remsetup.exe 79 snapshot.exe 40, REPLACE 1110 SOME REPLICATE 1110 SOUNDEX replmerg.exe 40, 587 sp addalert RESTORE DATABASE 563, 565 spadd j o b RESTORE LOG 566 sp_add_operator RETURN 1213 sp_addapprole REVERSE 1110 sp_addextendedproc REVOKE 259 spaddgroup RIGHT 1110 sp_addlinkedserver RIGHT OUTER 1169 spaddlinkedsrvlogin ROBUST PLAN 1198 sp_addlogin ROLLBACK TRAN 854 sp_addremotelogin ROLLBACK WORK 854 spaddrole ROLLUP 1182 sp addrolemember ROUND 1106 spaddserver ROWCOUNT_BIG() 1112 spaddsrvrolemember ROWGUID 609, 610 sp_addtype ROWGUIDCOL 609, 950, 1162 sp_adduser RTRIM 1110 sp_attach_db 542, sp_attach_single_file db sp_bindefault sp_bindnile sp_changedbowner sa22l sp_changeobjectowner 825, SAVE TRAN 853 sp_configure 162, scm.exe 40 allow update 230, SCROLL 1225 allow updates securityadmin 222 cost threshold for parallelism SELECT 1159 default language SELECT INTO 325 fill factor Предметный указатель sql_variant 'locks sqlagent.exe 'max server memory' sqlcli.bat 'max worker threads' sqlcst.bat 'rnin memory per query" sqldiag.exe 'min server memory sqlftwiz.exe 'nested triggers' sqlins.bat 'priority boost' sqlmaint.exe 'query governor cost limit' sqlrem.bat 'recovery interval' sqlservr.exe 40, 'remote access' sqltrace.exe 'remote proc trans' 177, sqlupg.bat 'remote query timeout' SQRT 'set working set size' SQUARE 'show advanced options' STANDBY 'two digit year cutoff STATS_DATE 'user connections' STDEV sp_dboption STDEVP sp_depends 978, STR sp_detach_db 542, STUFF spdropapprole SUBSTRING sp_droplinkedsrvlogin SUM sp_dropremotelogin SUSER_SID sp_droprole SUSER_SID() sp_droprolemember SUSER_SNAME spdropserver sysadmin 221, spdropsrvrolemember sysalerts sp_droptype sysdatabases spdropuser sysdepends sp_fkeys sysindexes sp^grantdbaccess syslogins sp_grantlogin sysoperators sp_help 974, sysservers sp_helpdb SYSTEMJJSER sp_helpfile systypes sphelpfilegroup sysusers sp_helplinkedsrvlogin sysxlogins 206, sp_helpremotelogin sp_helprolemember sp_helprotect sphelpsrvrole sphelpsrvrolemember TAN sphelptext Tempdb sp_helpuser text sp_remoteoption timestamp 617, sp_rename 1010, tinyint sp_renamedb TOP sp_revokedbaccess TRIGGER_NESTLEVEL([object_id]) sp_serveroption TYPEPROPERTY sp_setapprole sp_spaceused sp_unbindefault U sp_unbindrule sp_updatestats 1051 UNION sp_user_counter 724 UNIQUE SPACE 1,110 UPDATE 1130, Предметный указатель VARP UPDATE STATISTICS VARYING UPPER vswitch.exe 41, USE USER ID w WAITFOR WHERE VAR WHILE..CONTINUE varbinary WITH TIES varchar A Блокировки 845, использование Административные задачи 264 конкуренция:

Администрирование 120 оптимистическая автоматизация 468 пессимистичекая Архивирование 526 конфликты Атрибут 791 менеджер Аудит 719 мертвые 848, основы проблема:

грязного чтения неповторяемого чтения База данных:

последнего изменения автоматический рост чтения фантомов архитектура типы восстановление управление выбор сопоставления уровни блокирования изменение имени использование групп файлов 883 В копирование объектов неформатированные разделы 885 Вертикальный фильтр объекты 820 Виртуальный сервер отсоединение 542, 942 Владелец базы данных передача прав владения 918 Вложенные триггеры планирование конфигурации 881 Внешние запросы получение информации 920 Волокна присоединение 541, 942 Восстановление проектирование 785, 794 Временные таблицы размещение: Вторая нормальная форма Вторичный сервер по умолчанию Выборка данных файлов Выражения распределения распределенная системные создание удаление 945 Глобальная временная таблица уменьшение размера 926 Глобальный уникальный идентификатор управление свойствами 911 609, централизованная 785 Горизонтальный фильтр Предметный указатель управление Горячая резервная копия фактор запосления Группы:

Инструмент DTS Designer серверов задачи страниц файлов 809 ActiveX Script Bulk Insert д Copy SQL Server Objects 427, Data Driven Query Dynamic Properties Двойные кавычки Execute Package Двухфазный протокол фиксирования Execute Process Динамически подключаемые Execute SQL библиотеки File Transfer Protocol Динамический моментальный снимок Message Queue Динамический фильтр Send Mail Диспетчер безопасности Windows NT Добавление строк 11216, 1122, 1127 Transfer Database Доверительные отношения 204 Transfer Error Messages 446, Домен 791 Transfer Jobs Дублирование дисков 780 Transform Data источники данных Использование процессоров Использование файла подкачки Задача Закачка данных Запись 788, Запрос 1057 Каналы именованные ограничение по цене исполнения 180 Кардинальное число тайм-аут для удаленных серверов Категория событий цена распараллеливания Класс событий Запуск:

Кластер 775, SQL Server 2000 147, 155, 157, Клиент-сервер запроса Клиенты приложения Ключ 792, Зеркальное отображение дисков Кодовая страница Зеркальный набор дисков Колонка 738, Константы И Контрольная точка Конфигурирование базы данных Идентификатор безопасности 203, Конфигурирование сервера Идентификация пользователя Конфликт изменения Избыточность Координатор распределенных транзакций Изменение данных 616, Именованные каналы 73, Копирование данных Импорт данных Копирование резервное 526, 535, Индексирование представлений Кортеж Индексы 820, Курсоры использование выборка кластерный динамические некластерный дополнительные средства переименовывание закрытие перестроение изменение данных представления создание 1030 использование удаление 1044 ключевые (окончание рубрики см. на стр. 1260) уникальный Предметный указатель Ограничения целостности Курсоры (окончание):

Ограниченные идентификаторы объявление Операнды освобождение Оператор:

открытие параметры рассылки сообщений последовательные 1219, последней надежды прокручиваемые Операторы 1062, статические Определяемые пользователем типы удаление данных данных 821, Определяемые пользователем функции Л Основной сервер Отношение 790, Локальная:

временная таблица п система м Пакет DTS 330, 456, Пакет команд Параметризированные запросы Магнитная лента Переменные 1058, Макет таблицы Перенаправление:

Максимальное количество событий пользователей сообщений Массивное копирование Перенос данных Массивы RAID Подготовка к установке 52, Мастера Подзапрос Менеджер:

Поле 788, блокировок 849, Пользователь:

ресурсов базы данных Метка домена Модели данных локальный Моментальный снимок 582, Порядок сортировки 63, Мониторинг Поток Event Viewer Почтовый профиль Performance Monitor Права доступа 55, 234, SQL Server Profiler Правила 821, Task Manager Представления 820, выбор инструмента Профиль трассировки основы осуществление фильтр н Разделяемая память 74, Распределенные:

запросы Набор символов 63, 65 транзакции Неформатированные разделы 885 Расщепление страниц 182, Нить выполнения 171 Резервная копия Нормализация 799 Резервный сервер Репликация данных 185, Роли 221, О Обновление Обработчик распределенных запросов Объект 722. Связанные серверы Обязанности администратора 265 Сервер Предметный указатель Сетевые библиотеки 69, 121 Умолчания 820, Система безопасности 198 Управление:

Системные таблицы 179 группой серверов Скрипт 120 памятью Слот 1025 серверами и группами Службы: Усечение SQL Server 2000 16, 119, 161 Установка 45, 74, трансформации данных 328 Утилиты командной строки События 472, 737 Учетная запись 205, 209, Сокеты TCP/IP 74, Сопоставление Ф Unicode Спящий режим 189 Файл автоматической установки Стандартные идентификаторы 823 Файл-сервер Статистика 1048 Файлы Степень заполнения 517 Фактор заполнения 181, Стоимость владения 45 Функции 1063, Столбец Страница Строка Сущность 790, Хранимые процедуры 821, Сценарий Счетчики ц Цена распараллеливания запроса Таблицы 790, 820, э Технология RAID Типы данных Точки сохранения 853 Экспорт данных Транзакции 845, 854 Экстенты Триггеры 821, 1239 Электронная почта Я Удаление данных 1134 Язык по умолчанию Удаленные серверы 307 Язык структурированных запросов

Pages:     | 1 |   ...   | 31 | 32 ||
 





 
© 2013 www.libed.ru - «Бесплатная библиотека научно-практических конференций»

Материалы этого сайта размещены для ознакомления, все права принадлежат их авторам.
Если Вы не согласны с тем, что Ваш материал размещён на этом сайте, пожалуйста, напишите нам, мы в течении 1-2 рабочих дней удалим его.