Главная » Microsoft SQL Server, Базы данных » Потенциальные препятствия на пути модификации данных

0

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

Как показано в табл. 16.2, инструкции INSERT и UPDATE имеют больше подводных камней, чем инструкция DELETE, так как вставляют в таблицу данные, которые должны следовать множеству определенных правил. Инструкция DELETE только удаляет данные, поэтому и число возможных ловушек невелико.

Таблица 16.2. Потенциальные проблемы модификации данных
Потенциальная проблема Инструкция

INSERT

Инструкция

UPDATE

Инструкция

DELETE

Тип и длина данных X X
Первичный ключ X X
Внешний ключ X X X
Уникальный индекс X X
Запрет пустых значений и отсутствие умолчаний X X
Проверка ограничений X X
Триггер INSTEAD OF X X X
Триггер AFTER X X X
Необновляемые представления X X X
Представления с параметром check X X
Безопасность X X X

Проблема типа и длины данных

Тип и длина столбцов данных могут влиять на результаты инструкций INSERT и UPDATE. Одной из первых проверок, которые должны проходить новые данные, является проверка на их тип и длину. Часто ошибка типа данных вызвана отсутствием кавычек или, наоборот, их наличием. SQL Server выполняет неявное (т.е. автоматическое) преобразование данных. В то же время преобразования, которые автоматически выполняются в других языках программирования, могут не сработать в SQL Server. Приведем показательный пример:

USE OBXKites

INSERT Price (ProductID, Price, EffectiveDate)

Values (1DB8D8D60-76F4-46C3-90E6-A8648F63C0F01,

45.00′, 6/25/2002 )

Server: Msg 260, Level 16, State 1, Line 1 Disallowed implicit conversion from data type varchar

to data type money, table 1OBXKites.dbo.Price 1, column 1 Price’ .

Use the CONVERT function to run this query.

Проблема в приведенном примере заключалась в излишних кавычках, обрамляющих новое значение цены. СУБД SQL Server не способна автоматически преобразовать строковое значение в числовое. Если вам необходимо такое преобразование, воспользуйтесь функцией Convert() или Cast ().

Дополнительная Подробно о типах данных и таблицах речь пойдет в главе 17. О преобразова- йнформаций нии типов данных и связанных с этой операцией функциях см. в главе 8.

Проблемы первичного ключа

Первичные ключи могут влиять на инструкции INSERT и UPDATE.

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

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

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

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

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

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

На самом деле тот факт, что столбцы идентичности отвергают поставляемые им значения, может создать серьезные проблемы, особенно если на такие же значения первичного ключа уже ссылаются вторичные таблицы. В учебной базе данных басен Эзопа первичные ключи “прошиты” в инструкции INSERT в сценариях заполнения, так как они заранее известны в операциях преобразования данных.

Решение проблемы сводится к использованию параметра базы данных identity_insert. Когда этот параметр установлен в значение on, защита столбца идентичности временно отключается, что позволяет вставлять в него свои данные. В базе данных параметр identity_ insert может быть одновременно установлен в значение on только для одной таблицы. Следующий пакет инструкций SQL использует параметр identity_insert для заполнения первичного ключа.

USE CHA2

— attempt to insert into an identity column INSERT dbo.Guide (GuidelD, FirstName, LastName)

VALUES (10, ‘Bill1, ‘Fletcher’)

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

Server: Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for identity column in table

‘Guide’ when IDENTITY_INSERT is set to OFF.

Теперь установим параметр identity_insert и попробуем выполнить другие вставки:

SET IDENTITY_INSERT Guide On

INSERT Guide (GuidelD, FirstName, LastName)

VALUES (100, ‘Bill’, ‘Mays’)

INSERT dbo.Guide (GuidelD, FirstName, LastName)

VALUES (101, ‘Sue’, ‘Atlas’)

Чтобы увидеть, какие значения теперь будут присвоены столбцу идентичности, снова отключим параметр identity_insert и выполним еще одну вставку:

SET IDENTITY_INSERT Guide Off INSERT Guide ( FirstName, LastName)

VALUES (‘Arnold’, ‘Bistier’)

SELECT GuidelD, FirstName, LastName FROM dbo.Guide

Теперь посмотрим на результат:

GuidelD FirstName LastName

1Dan                   Smith

2 Jeff                    Davis

3Tammie             Commer

4 Lauren               Jones

5Greg                  Wilson

100                                  Bill         Mays

101                                  Sue         Atlas

102                                  Arnold    Bistier

Как было продемонстрировано, вставка вручную идентификатора с номером 101 привела к тому, что следующему было присвоено значение 102.

Еще одна проблема, связанная со столбцом идентичности, заключается в том, что достаточно тяжело узнать только что созданный идентификатор. Так как SQL Server генерирует это значение в ходе выполнения инструкции INSERT, программа, выполняющая вставку строки, его не знает. Сама инструкция INSERT проблем не вызывает — все выявляется тогда, когда сгенерированное значение приходится отображать в интерфейсе пользователя.

SQL Server предлагает три метода получения значения идентификатора.

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

?               scope_identity (). Эта системная функция возвращает последнее сгенерированное значение столбца идентичности в процессе выполнения текущей процедуры или пакета. Лично я рекомендую использовать именно этот метод получения последнего сгенерированного значения столбца идентичности.

?               ident_current {таблица). Эта функция возвращает последнее значение столбца идентичности, сгенерированное для указанной таблицы. Несмотря на кажущуюся схожесть с функцией scope_identity (), данная функция не учитывает вставки значений идентичности, выполненные в других таблицах. Таким образом, вы получаете гарантированно корректный результат.

Глобальные уникальные идентификаторы GUID великолепно справляются с ролью первичных ключей. Их основное отличие от значений идентичности заключается в том, что они генерируются кодом SQL или с помощью значений столбца по умолчанию, а не во время выполнения самой вставки. Таким образом, разработчик получает больший контроль над созданием этих идентификаторов. Если значение вставляется в столбец, для которого определено значение по умолчанию, то проблемы не возникнут. Значение по умолчанию используется только тогда, когда в инструкции вставки для столбца явно не указано значение. Идентификаторы GUID можно использовать и по другим причинам, однако самой основной я считаю простоту их использования.

Дополнительная Более подробно вопросы создания первичных ключей будут рассмотрены в информация главе 17. Там же приведена сравнительная характеристика GUID и столбцов идентичности.

Глобальные универсальные идентификаторы создаются с помощью функции newid (). Если значение по умолчанию для первичного ключа задано как NewID (), то новый идентификатор генерируется для каждой новой строки. К тому же функция newid () может быть указана непосредственно в списке инструкции INSERT. . .VALUES. Эта функция будет выполняться даже в составе выражения инструкции INSERT. . . SELECT, работающей с группой строк. В составе хранимой процедуры или приложения результат выполнения этой функции может быть сохранен в переменной. Впоследствии эта переменная может быть использована в инструкции INSERT. . . VALUES для вставки ее значения в новую строку. Любой из этих вариантов хорошо справляется со своей работой, и в приложении их можно комбинировать.

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

В следующем примере продемонстрированы различные методы генерирования GUID для первичного ключа при вставке новых строк в таблицу ProductCategory базы данных OBXKites. В первом примере мы проверим работу функции newid ().

USE OBXKites Select NewID()

Получаем следующий результат:

5 СВВ2 800-5207-4323-АЗ1б -Е 9 б 3ААСВ 6 081

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

— GUID вставляется по умолчанию

–(значением по умолчанию для столбца является функция NewID())

INSERT dbo.ProductCategory (ProductCategorylD, ProductCategoryName)

VALUES (DEFAULT, ‘Из умолчания’)

— GUID вставляется функцией INSERT dbo.ProductCategory (ProductCategorylD, ProductCategoryName)

VALUES (NewID(), ‘Из функции’)

— GUID вставляется из переменной DECLARE @NewGUID Uniqueldentifier SET @NewGUID = NewID()

INSERT dbo.ProductCategory (ProductCategorylD, ProductcategoryName)

VALUES (@NewGUID, ‘Из переменной1)

Для просмотра результатов вставки мы отфильтруем только те строки, которые начинаются со слова “Из”:

SELECT ProductCategorylD, ProductcategoryName FROM dbo.ProductCategory

WHERE ProductcategoryName LIKE ‘Из %’

Будет получен следующий результат:

ProductCategorylD                           ProductcategoryName

25894DA7-В5ВВ-435D-954 0-6В9207C6CF8F Из умолчания 393414DC-8611-4460-8FD3-4657Е4В4 9373 Из функции FF868338-DF9A-4B8D-89В6-9С28293CA25F Из переменной

Следующая инструкция INSERT использует функцию newid () для вставки нескольких идентификаторов GUID:

INSERT dbo.ProductCategory

(ProductCategorylD, ProductcategoryName)

Select NewIDO, LastName From CHA2 . dbo . Guide

Приведенная инструкция SELECT запрашивает новые идентификаторы:

SELECT ProductCategorylD, ProductcategoryName FROM dbo.ProductCategory

Теперь посмотрим на результаты:

ProductCategorylD                           ProductcategoryName

1B2BBE15-B415-43ED-BCA2-293050B7EFE4 Kite 23FC5D45-8B60-4800-A505-D2F556F863C9 Accessory

3          88 9671A-F2CD-4B79-8DCF-19F4F4703 693 Video

5471F896-A414-432B-A57 9-0880757ED097 Fletcher 428F29B3-111B-4ECE-B6EB-E0913A9D34DC Atlas E4B7D325-8122-48D7-A61B-A83E258D8729 Bistier

SQL Server предлагает двух отличных кандидатов на роль первичных ключей — столбцы идентичности и GUID. То, какой из них будет использоваться, зависит от нескольких факторов. В любом случае у вас есть несколько методов вставки новых строк. Кем бы вы ни были — программистом или администратором базы данных, — ситуация всегда будет у вас под контролем.

Проблемы внешних ключей

Внешние ключи могут влиять на выполнение инструкций INSERT, UPDATE и DELETE — они могут блокировать эти операции. Вставка новой строки вторичной таблицы, имеющей внешний ключ, не соответствующий ни одному первичному, может просто не выполниться.

В следующем примере значение идентификатора категории товара (ProductCategorylD) не существует в таблице ProductCategory. В результате ограничение внешнего ключа блокирует выполнение операции вставки:

— Внешние ключи: проблемы вставки INSERT Product (ProductID, Code,

ProductCategorylD, ProductName)

VALUES (‘9562C1A5-44 99-4626-BB33-E5E14 0ACD2AC1 ,

‘999’

1DB8D8D6 0-76F4-4 6C3-90E6-A8648F63C0F0′,

‘Basic Box Kite 21″‘)

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with COLUMN FOREIGN KEY

constraint ‘FK______ Product___ Product         7B905C751.

The conflict occurred in database ‘OBXKites’, table ‘ProductCategory’, column ‘ProductCategorylD’.

The statement has been terminated.

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

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

В следующем примере операция обновления блокируется по той причине, что предпринимается попытка обновления значения внешнего ключа вторичной таблицы значением, которое не существует в первичной:

— Внешние ключи: проблемы обновления вторичной таблицы

UPDATE Product

SET ProductCategorylD =

‘DB8D8D6 0-76F4-46C3-90E6-A864 8F63C0F0′

WHERE ProductID = ‘67804443-7E7C-4769-A41C-3DD3CD3621D9′

Server: Msg 547, Level 16, State 1, Line 1

UPDATE statement conflicted with COLUMN FOREIGN KEY

Constraint ‘FK______ Product___ Product         7B905C75′.

The conflict occurred in database ‘OBXKites’, table ‘ProductCategory’, column ‘ProductCategorylD’.

The statement has been terminated.

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

В следующем примере ошибка вызвана не таблицей ProductCategory, даже несмотря на то, что обновляется именно она; ошибку провоцирует таблица Product. Таблица Product имеет ограничения внешнего ключа, и одно из его значений теперь должно ссылаться на значение первичной таблицы, которого больше не существует.

— Внешние ключи: проблемы обновления первичной таблицы UPDATE ProductCategory SET ProductCategorylD =

‘DB8D8D60-76F4-46C3-90E6-A8648F63C0F0′

WHERE ProductCategorylD =

‘1B2BBE15-B415-43ED-BCA2-2 93 050B7EFE4′

Server: Msg 547, Level 16, State 1, Line 1

UPDATE statement conflicted with COLUMN REFERENCE constraint

‘FK___ Product__ Product      7B905C75′. The conflict occurred

in database ‘OBXKites’, table ‘Product’, column ‘ProductCategorylD’.

The statement has been terminated.

Дополнительная Подробно о роли ссылочной целостности при выборе внешних ключей см. в ^информация \ главе 2. Сам процесс создания внешних ключей подробно описан в главе 17.

Проблемы уникальных индексов

Уникальные индексы могут влиять на выполнение инструкций INSERT и UPDATE.

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

Как правило, завершится неудачей даже вся транзакция, содержащая операции вставки или обновления таких строк. В то же время существует параметр ignore dup key, позволяющий транзакциям выполняться, запрещая только те операции, которые приводят к дублированию строк.

Дополнительная Более подробно об ограничениях уникальных индексов вы узнаете в главах 17

^информация \ и 50.

Проблемы пустых значений и значений по умолчанию

Допустимость пустых значений и значений по умолчанию столбцов могут оказывать влияние на выполнение инструкций INSERT и UPDATE.

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

Таблица 16.3. Модификация данных, значения по умолчанию и пустые значения
Свойства столбца
Значения по умолчанию Нет Нет Есть Есть
Допустимость пустых значений Да Нет Да Нет
Инструкция посылает Результат
данные Данные Данные Данные Данные
null null Ошибка null Ошибка
Значение по умолчанию null Ошибка Значение по Значение по умол
умолчанию чанию
Ничего null Наиболее распро Значение по Значение по умол
страненная ошибка умолчанию чанию

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

Дополнительная Детальная информация о создании значений по умолчанию и конфигурирова- информация нии пустых значений приведена в главе 17. О работе с пустыми значениями при извлечении данных см. в главе 8.

Проблемы ограничений проверки

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

В следующем примере устанавливается ограничение, которое отсеивает лиц, не достигших на момент приема на работу 21-летнего возраста (следует отметить, что это ограничение уже применено при создании базы данных с помощью сценария Create_CHA2 . sql):

USE СНА2 go

ALTER TABLE dbo.Guide ADD CONSTRAINT

СK_Guide_Age21 CHECK (DateDiff(yy,DateOfBirth, DateHire)

>= 21)

В следующем запросе в базу данных вставляется информация об экскурсоводе Мэри Джонсон. Так как она уже достигла 26-летнего возраста, вставка будет выполнена:

INSERT Guide(lastName, FirstName, Qualifications, DateOfBirth,

DateHire)

VALUES (1 Johnson’, ‘Mary’,

‘E.R. Physician’, ’14/1/71′, ‘1/6/97′)

В противоположность этому Грег Франклин достиг только 19-летнего возраста, и ограничение проверки запретит вставку его данных:

INSERT Guide (lastName, FirstName,

Qualifications, DateOfBirth, DateHire)

VALUES (‘Franklin’, ‘Greg’,

‘Guide’, ’12/12/83′, ‘1/1/2002′)

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with TABLE CHECK constraint

‘CK_Guide_Age21′.

The conflict occurred in database ‘CHA2′, table ‘Guide’.

The statement has been terminated.

Дополнительная Подробно об ограничениях проверки, их достоинствах и недостатках речь пой- «нфорглация дет в главе 17.

Проблемы триггеров instead of

Триггеры INSTEAD OF оказывают влияние на выполнение инструкций INSERT, UPDATE и DELETE.

Триггеры представляют собой особые хранимые процедуры, которые прикрепляются к таблице и вызываются при выполнении в ней каких-либо операций модификации данных. Существуют два типа триггеров: INSTEAD OF и AFTER. Отличаются они временем выполнения и отношением к операции модификации данных.

Триггеры INSTEAD OF выполняются вместо соответствующей операции; при этом сама инструкция отклоняется. Эти триггеры могут заменить собой операцию, проведя ее в корректной форме, также они могут выполнить какое-либо другое действие.

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

В следующем примере триггер InsteadOf Demo выполняется вместо инструкции вставки:

USE СНА2 go

CREATE TRIGGER InsteadOfDemo ON Guide

INSTEAD OF INSERT AS

Print ‘Пример триггера INSTEAD OF’

Return

Следующий запрос вставит указанную в триггере тестовую строку вместо фактически указанных в инструкции данных:

INSERT Guide(lastName, FirstName,

Qualifications, DateOfBirth, DateHire)

VALUES (‘Jamison’, ‘Tom’,

‘Biologist, Adventurer’, ’14/1/56′, ‘1/9/99′)

Будет получен следующий результат:

Пример триггера INSTEAD OF (1 row(s) affected)

Инструкция INSERT должна была быть выполнена, но вставила ли она в таблицу строку? Проверим.

SELECT GuidelD FROM Guide

WHERE LastName = ‘Jamison’

GuidelD

(0 row(s) affected)

Дополнительная Создание триггеров будет описано в главе 23. Процесс выполнения транзакций информация модификации данных и время запуска триггеров рассмотрен в главе 51.

Следует отметить, что в приведенном примере триггер InsteadOf Demo перед своим созданием был удален.

Проблемы триггеров after

Триггеры AFTER могут оказывать влияние на выполнение инструкций INSERT, UPDATE и DELETE.

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

предполагалось изначально. В отличие от триггеров INSTEAD OF, триггеры AFTER отчитываются об ошибке, если инструкция была отменена.

В главе 51 мы обсудим все операции, неявно выполняемые в транзакциях, даже если они не содержит инструкции BEGIN TRANSACTION. Триггеры AFTER запускаются на выполнение сразу же после инструкции, но перед подтверждением операции. Таким образом, во время выполнения триггера AFTER транзакция считается открытой.

В следующем примере создается триггер Af terDemo, прикрепленный к таблице Guide. В этом триггере содержатся команды raiseerror и rollback transaction:

USE СНА2

CREATE TRIGGER AfterDemo ON Guide

AFTER‘INSERT, UPDATE AS

Print ‘Пример триггера AFTER’

— logic in a real trigger would decide what to do here RAISERROR (‘Пример ошибки’, 16, 1 )

ROLLBACK TRAN Return

В результате применения этого триггера к таблице Guide результат операции вставки будет следующим:

INSERT Guide(lastName, FirstName,

Qualifications, DateOfBirth, DateHire)

VALUES (‘Harrison’, ‘Nancy’,

‘Pilot, Sky Diver, Hang Glider,

Emergency Paramedic’, ’25/6/69′, ’14/7/2000′)

Пример триггера AFTER

Server: Msg 50000, Level 16, State 1,

Procedure AfterDemo, Line 7 Пример ошибки

Поиск строки с данными о Нэнси Харрисон ни к чему не приведет, поскольку транзакция была отменена.

Дополнительна*. Более подробно триггеры AFTER будут описаны в главе 23. О дополнительных информация стратегиях создания триггеров вы узнаете в главе 24.

Следует обратить внимание, что программный код на компакт-диске, прилагаемом к книге, относящийся к настоящей главе, удаляет триггер AfterDemo, чтобы все остальные примеры могли работать.

Вычисляемые столбцы

С необновляемыми представлениями непосредственно связан вопрос вычисляемых столбцов — любая попытка записи данных в такой столбец обречена на неудачу.

Дополнительная Более подробно о создании вычисляемых столбцов вы узнаете в главе 17.

информация’

Проблемы необновляемых представлений

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

Представление vMedGuide, создаваемое в следующем примере, является необновляемым, так как предикат DISTINCT исключает дублирования, что, в свою очередь, не дает возможности серверу однозначно определить, какая именно строка обновляется:

CREATE VIEW dbo.vMedGuide AS

SELECT DISTINCT GuidelD, LastName, Qualifications FROM dbo.Guide

WHERE Qualifications LIKE ‘%Aid%1 OR Qualifications LIKE ‘%medic%’

OR Qualifications LIKE 1%Physician%’

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

UPDATE dbo.vMedGuide

SET Qualifications = 1E.R. Physician, Diver’

WHERE GuidelD = 1

Будет получен следующий результат:

Server: Msg 4404, Level 16, State 1, Line 1

View or function ‘dbo.vMedGuide’ is not updatable

because the definition contains the DISTINCT clause.

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

Проблемы представлений с параметром проверки

Представления с параметром WITH CHECK OPTION влияют на выполнение инструкций INSERT и UPDATE.

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

Дополнительная Подробно о параметре with check option и проблеме исчезающих столбцов информация см. в главе 14. Использование ролей системы безопасности будет рассмотрено ‘ в главе40.

Добавление в представление параметра WITH CHECK OPTION может вызвать еще одну проблему. Он влияет на выполнение условия WHERE как для извлекаемых данных, так и для вставляемых и обновляемых. Если данные после операций вставки или обновления не способны отображаться в представлении, то параметр WITH CHECK OPTION может привести к невозможности выполнения соответствующей инструкции в целом.

В следующем примере в упомянутое уже ранее представление добавляется параметр WITH CHECK OPTION, после чего выполняются две попытки обновления данных. Первая из них удовлетворяет требованиям предложения WHERE. Вторая операция пытается удалить строки из результирующего набора данных, который вернул представление, поэтому она завершается неудачей:

ALTER VIEW dbo.vMedGuide AS

SELECT GuidelD, LastName, Qualifications FROM dbo.Guide

WHERE Qualifications LIKE ‘%Aid%’

OR Qualifications LIKE ‘%medic%’

OR Qualifications LIKE ‘%Physician%1 WITH CHECK OPTION

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

UPDATE dbo.vMedGuide

SET Qualifications = ‘E.R. Physician, Diver1

WHERE GuidelD = 1

UPDATE dbo.vMedGuide

SET Qualifications = ‘Diver’

WHERE GuidelD = 1

Server: Msg 550, Level 16, State 1, Line 1 The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

Проблемы системы безопасности

Вопросы безопасности могут влиять на выполнение инструкций INSERT, UPDATE и DELETE.

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

Дополнительная Более подробно о системе безопасности и ролях мы поговорим в главе 40.

информация

Большинство проблем модификации без труда могут предупредить программисты и администраторы баз данных, если они только понимают систему работы SQL Server, документируют базу данных, знакомы со схемой базы, с хранимыми процедурами и триггерами.

Резюме

Извлечение и модификация информации являются основными задачами приложений работы с базами данных. В этой главе мы рассмотрели инструкции языка DML INSERT, UPDATE и DELETE, а также вопросы их блокировки в базе данных.

На этом мы завершаем изучение инструкций DML, которым посвятили десяток глав. В следующих главах мы приступим к задачам программирования и проектирования баз данных в SQL Server с помощью T-SQL, CLR и новой архитектуры, ориентированной на службы.

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии