Джеффри Мак-Манус - Обработка баз данных на Visual Basic®.NET Страница 30

Во-вторых, существует потенциальная проблема, состоящая в том, что сгенерированный уникальный ключ на самом деле не будет уникальным, т.е. при создании ключа не выполняется проверка существования записи с таким же значением первичного ключа. Конечно, вероятность того, что будет сгенерировано два одинаковых значения, очень мала, но она все же существует (тип данных integer в SQL Server имеет длину 4 бита, т.е. диапазон возможных значений: -2,1×109…2,1×109).

Бизнес-ситуация 3.1: создание триггера для поиска созвучных слов

Брэд Джонс, президент компании Jones Novelties Incorporated, одобрил предварительную работу своего разработчика базы данных. Теперь он готов приступить к следующей проблеме, связанной с базой данных, и создать запросы для извлечения информации о клиентах по введенному созвучному имени (например, имена произносятся одинаково, но пишутся по-разному) с учетом случайных опечаток в нем. Как организовать поиск клиента, если вы не помните точное написание его имени, а только его произношение: Smith или Smyth, McManus или MacManus? Каждому человеку с необычной фамилией наверняка приходилось сталкиваться с ее неверной записью на слух.

Разработчик базы данных для решения этой проблемы решил воспользоваться функцией soundex(), специально предусмотренной для этого в SQL Server. Она преобразует слово в алфавитно-цифровое значение, которое представляет базовые звуки слова. Если такое значение создается в момент ввода имени, то поиск имени можно вести по его алфавитно-цифровому значению. Конечно, такой запрос вернет гораздо больше записей, чем нужно, но все они будут отвечать одному произношению.

Для реализации этого компонента в базе данных Jones Novelties нужно выполнить следующие действия:

• изменить таблицу tblCustomer для вставки нового поля LastNameSoundex;

• запустить запрос обновления данных для создания алфавитно-цифровых значений звучания для имен клиентов в таблице tblCustomer;

• создать триггер, который сгенерирует в поле LastNameSoundex алфавитно-цифровое значение звучания для введенного или измененного имени;

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

Разработчик базы данных начинает с создания нового поля LastNameSoundex в таблице tblCustomer для хранения в нем алфавитно-цифровых значений звучания имен всех клиентов. Это можно сделать с помощью следующей команды:

ALTER TABLE tblCustomer ADD

LastNameSoundex varchar(4) NULL

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

UPDATE tblCustomer

SET LastNameSoundex = soundex(lastName)

GO

SELECT LastName, LastNameSoundex

FROM tblCustomer

GO

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

Далее разработчику нужно создать триггер, чтобы вставить алфавитно-цифровое значение звучания для каждого введенного в базу данных имени нового клиента.

CREATE TRIGGER trCustomerT

ON tblCustomer

FOR insert, update

AS

 UPDATE tblCustomer

 SET tblCustomer.LastNameSoundex = soundex(tblCustomer.LastName)

 FROM inserted

 WHERE tblCustomer.ID = inserted.ID

Хотя в SQL Server 2000 для одной таблицы допускается определение нескольких триггеров одного типа (для вставки, обновления и удаления), но порядок их выполнения контролируется только частично, т.е. можно указать только первый и последний выполняемый триггер. Для гарантированного выполнения данного триггера после всех триггеров вставки для таблицы tblCustomer (например, триггера присвоения значения столбцу ID) нужно выполнить в программе SQL Query Analyzer следующую команду:

sp_settriggerorder @[email protected]='last', @stmtype='INSERT'

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

Для обработки нужного набора записей триггеру должно быть известно, какие записи участвовали в выполнении процедуры, которая привела к ее запуску. Откуда же у триггера такие сведения? Триггеры обладают доступом к этой информации благодаря виртуальным таблицам вставки и удаления. Виртуальная таблица вставки содержит записи, вставленные (или обновленные) процедурой, которая привела к запуску триггера вставки. Аналогично, виртуальная таблица удаления содержит записи, удаленные процедурой, которая привела к запуску триггера удаления.

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

После создания надежного триггера, который генерирует алфавитно-цифровое значение звучания для имени для каждой записи в таблице tblCustomer, нужно проверить его работоспособность, вставив запись, которую невозможно извлечь с помощью традиционного запроса. Допустим, что в базе данных есть несколько клиентов с именем Smith, и пробуем вставить в нее запись о клиенте Smythe с помощью следующей команды INSERT:

INSERT INTO tblCustomer (FirstName, LastName)

VALUES ('Abigail', 'Smythe')

Теперь работу созданного триггера можно проверить с помощью следующего запроса:

SELECT LastNameSoundex, LastName

FROM tblCustomer

WHERE LastName = 'Smythe' 

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

CREATE PROC LastNameLookup

@name varchar(40) AS

SELECT * FROM tblCustomer

WHERE soundex(@name) = LastNameSoundex

Наконец, можно приступать к извлечению записей из базы данных на основе значений звучания. Для этого нужно выполнить хранимую процедуру LastNameLookup. LastNameLookup 'smith'

После выполнения этой процедуры программа Query Analyzer возвращает набор записей, состоящий из клиентов с фамилией, созвучной smith, включая клиента с фамилией Smythes (рис. 3.21).

РИС. 3.21. Набор записей, возвращенный хранимой процедурой LastNameLookup

Управление пользователями и средства безопасности с помощью программы SQL Server Enterprise Manager

Одной из наиболее важных причин использования сервера SQL Server является управление несколькими пользователями, которые пытаются осуществить доступ к одним и тем же данным одновременно. Хотя это приводит к возникновению большого количества проблем (например, при попытке пользователей получить доступ к привилегированной информации или одновременно обновить одну запись), но их можно решить с помощью параметров работы сервера.

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

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

Создание и сопровождение учетных записей пользователей

Для надежной и гибкой системы безопасности требуется создать систему идентификации каждого пользователя. В SQL Server система идентификации пользователей основана на учетных записях пользователей. Учетной записью (login) называется объект (как правило, человек), который обладает доступом к SQL Server. Учетные записи используются для регистрации в системе пользователей. Пользователь может обладать специальными разрешениями учетной записи для доступа к отдельной базе данных. Широкий набор разрешений пользователей определяется с помощью ролей.