Проверка значений помещаемых в представление

Другой вывод о модифицируемости представления тот, что вы можете вводить значения которые " проглатываются " (swallowed) в базовой таблице. Рассмотрим такое представление:

CREATE VIEW Highratings AS SELECT cnum, rating FROM Customers WHERE rating = 300;

Это - представление модифицируемое. Оно просто ограничивает ваш доступ к определенным строкам и столбцам в таблице. Предположим, что вы вставляете (INSERT) следующую строку:

INSERT INTO Highratings VALUES (2018, 200);

Это - допустима команда INSERT в этом представлении. Строка будет вставлена, с помощью представления Highratings, в таблицу Заказчиков. Однако когда она появится там, она исчезнет из представления, поскольку значение оценки не равно 300. Это - обычна проблема. Значение 200 может быть просто напечатано, но теперь строка находится уже в таблице Заказчиков где вы не можете даже увидеть ее. Пользователь не сможет понять, почему введя строку он не может ее увидеть, и будет неспособен при этом удалить ее. Вы можете быть гарантированы от модификаций такого типа с помощью включения WITH CHECK OPTION (С ОПЦИЕЙ ПРОВЕРКИ) в определение представления. Мы можем использовать WITH CHECK OPTION в определении представления Highratmgs.

CREATE VIEW Highratings AS SELECT cnum, rating FROM Customers WHERE rating = 300 WITH CHECK OPTION;

Вышеупомянутая вставка будет отклонена.

WITH CHECK OPTION - производит действие все_или_ничего (all-or-nothing). Вы помещаете его в определение представления, а не в команду DML, так что или все команды модификации в представлении будут проверяться, или ни одна не будет проверена. Обычно вы хотите использовать опцию проверки, используя ее в определении представления, что может быть удобно. В общем, вы должны использовать эту опцию, если у вас нет причины, разрешать представлению помещать в таблицу значения, которые он сам не может содержать.

ПРЕДИКАТЫ И ИСКЛЮЧЕННЫЕ ПОЛЯ

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

CREATE VIEW Londonsta1t AS SELECT snum, sname, comm FROM Salespeople WHERE city = 'London';

В конце концов, зачем включать значение city, если все значения city будут одинаковыми.

А как будет выглядит картинка получаемая всякий раз, когда мы пробуем вставить строку. Так как мы не можем указать значение city как значение по умолчанию, этим значением вероятно будет NULL, и оно будет введено в поле city (NULL используется если другое значение по умолчанию значение не было определено. Так как в этом случае поле city не будет равняться значению London, вставляемая строка будет исключена из представления.

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

CREATE VIEW Londonstate AS SELECT snum, sname, comm FROM Salespeople WHERE city = 'London' WITH CHECK OPTION;

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

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

CREATE VIEW Londonstaff AS SELECT * FROM Salespeople WHERE city = 'London' WITH CHECK OPTION;

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

 

SELECT snum, sname, comm FROM Londonstaff;

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

CREATE VIEW Highratings AS SELECT cnum, rating FROM Customers WHERE rating = 300 WITH CHECK OPTION;

попытка вставить или модифицировать значение оценки не равное 300 потерпит неудачу. Однако, мы можем создать второе представление (с идентичным содержанием) основанное на первом:

CREATE VIEW Myratings AS SELECT * FROM Highratings; Теперь мы можем модифицировать оценки не равные 300: UPDATE Myratings SET rating = 200 WHERE cnum = 2004;

Эта команда выполняемая так как если бы она выполнялась как первое представление, будет допустима. Предложение WITH CHECK OPTION просто гарантирует, что люба модификация в представлении, произведет значения, которые удовлетворяют предикату этого представления. Модификация других представлений базирующихся на первом текущем, является все еще допустимой, если эти представления не защищены предложениями WITH CHECK OPTION внутри этих представлений. Даже если такие предложения установлены, они проверяют только те предикаты представлений в которых они содержатся. Так например, даже если представление Myratings создавалось следующим образом

 

CREATE VIEW Myratings AS SELECT * FROM Highratings WITH CHECK OPTION;

проблема не будет решена. Предложение WITH CHECK OPTION будет исследовать только предикат представления Myratings. Пока у Myratings, фактически, не имеется никакого предиката, WITH CHECK OPTION ничего не будет делать. Если используется предикат, то он будет проверяться всякий раз, когда представление Myratings будет модифицироваться, но предикат Highratings все равно будет проигнорирован. Это - дефект в стандарте ANSI, который у большинство программ исправлен. Вы можете попробовать использовать представление наподобие последнего примера и посмотреть избавлена ли ваша система от этого дефекта.

 

Задание на лабораторную работу.

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

2. Утвердить у преподавателя.

3. Реализовать представления.

4. Оформить отчет.

5. Защитить лабораторную работу.

 

Контрольные вопросы.

1. Что такое представление?

2. Какие типы представлений существуют?

3. Для чего используются представления

4. Что такое модифицируемое представление?

5. Какие ограничения накладываются на создение представлений?

6. Какие ограничения накладываются на работу с представлениями?

7. Как Вы понимаете каскадное обновление данных?

8. Какие виды поддержания целостности данных Вам известны?

 

Лабораторная работа № 4

Тема: Понятия триггера. Работа с триггерами.

Цель работы: Изучить понятие триггера базы данных, разновидности триггеров. Научиться создавать триггеры.

Теоретические основы

 

ТРИГГЕРЫ

Создание триггера. Комманда Create trigger.

Создает триггер языка обработки данных, DDL или входа. Триггер — это особая разновидность хранимой процедуры, выполняемая автоматически при возникновении события на сервере базы данных. Триггеры языка обработки данных выполняются по событиям, вызванным попыткой пользователя изменить данные с помощью языка обработки данных. Событиями DML являются процедуры INSERT, UPDATE или DELETE, применяемые к таблице или представлению.

Триггеры DDL срабатывают в ответ на ряд событий языка определения данных (DDL). Эти события прежде всего соответствуют инструкциям Transact-SQL CREATE, ALTER, DROP и некоторым системным хранимым процедурам, которые выполняют схожие с DDL операции. Триггеры входа могут срабатывать в ответ на событие LOGON, возникающее при установке пользовательских сеансов. Триггеры могут быть созданы непосредственно из инструкций Transact-SQL или из методов сборок, созданных в среде выполнения CLR платформы Microsoft.NET Framework, и переданы экземпляру SQL Server. SQL Server допускает создание нескольких триггеров для любой указанной инструкции.

 

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE TRIGGER [ schema_name. ] trigger_name

ON { table | view }

[ WITH < dml_trigger_option > [,...n ] ]

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] }

[ WITH APPEND ]

[ NOT FOR REPLICATION ]

AS { sql_statement [; ] [,...n ] | EXTERNAL NAME < method specifier [; ] > }

< dml_trigger_option >::=

[ ENCRYPTION ]

[ EXECUTE AS Clause ]

< method_specifier >::=

assembly_name.class_name.method_name

 

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)

CREATE TRIGGER trigger_name

ON { ALL SERVER | DATABASE }

[ WITH < ddl_trigger_option > [,...n ] ]

{ FOR | AFTER }{ event_type | event_group }[,...n ]

AS { sql_statement [; ] [,...n ] | EXTERNAL NAME < method specifier > [; ] }

< ddl_trigger_option >::=

[ ENCRYPTION ]

[ EXECUTE AS Clause ]

< method_specifier >::=

assembly_name.class_name.method_name

 

Trigger on a LOGON event (Logon Trigger)

CREATE TRIGGER trigger_name

ON ALL SERVER

[ WITH < logon_trigger_option > [,...n ] ]

{ FOR | AFTER } LOGON

AS { sql_statement [; ] [,...n ] | EXTERNAL NAME < method specifier > [; ] }

< logon_trigger_option >::=

[ ENCRYPTION ]

[ EXECUTE AS Clause ]

< method_specifier >::=

assembly_name.class_name.method_name

 

schema_name. Имя схемы, которой принадлежит триггер DML. Триггеры DML ограничены областью схемы таблицы или представления, для которых они созданы. Аргумент schema_name не может быть указан для триггеров DDL или входа.

trigger_name. Имя триггера. Аргумент trigger_name должен соответствовать правилам для идентификаторов — за исключением того, что trigger_name не может начинаться с символов # или ##.

table | view. Таблица или представление, в которых выполняется триггер DML, иногда указывается как таблица триггера или представление триггера. Указание уточненного имени таблицы или представления не является обязательным. На представление может ссылаться только триггер INSTEAD OF. Триггеры DML не могут быть описаны в локальной или глобальной временных таблицах.

DATABASE. Применяет область действия триггера DDL к текущей базе данных. Если этот аргумент определен, триггер срабатывает всякий раз при возникновении в базе данных события типа event_type или event_group.

ALL SERVER. Применяет область действия триггера DDL или триггера входа к текущему серверу. Если этот аргумент определен, триггер срабатывает всякий раз при возникновении в любом месте на текущем сервере события типа event_type или event_group.

WITH ENCRYPTION. Затемняет текст инструкции CREATE TRIGGER. Использование аргумента WITH ENCRYPTION не позволяет публиковать триггер как часть репликации SQL Server. Параметр WITH ENCRYPTION не может быть указан для триггеров CLR.

EXECUTE AS. Указывает контекст безопасности, в котором выполняется триггер. Позволяет управлять учетной записью пользователя, используемой экземпляром SQL Server для проверки разрешений на любые объекты базы данных, ссылаемые триггером.

FOR | AFTER. Тип AFTER указывает, что триггер DML срабатывает только после успешного выполнения всех операций в инструкции SQL, запускаемой триггером. Все каскадные действия и проверки ограничений, на которые имеется ссылка, должны быть успешно завершены, прежде чем триггер сработает.

Если единственным заданным ключевым словом является FOR, аргумент AFTER используется по умолчанию.

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

INSTEAD OF. Указывает, что триггер DML срабатывает вместо инструкции SQL, используемой триггером, переопределяя таким образом действия выполняемой инструкции триггера. Аргумент INSTEAD OF не может быть указан для триггеров DDL или триггеров входа.

На каждую инструкцию INSERT, UPDATE или DELETE в таблице или представлении может быть определено не более одного триггера INSTEAD OF. Однако можно определить представления на представлениях, где у каждого представления есть собственный триггер INSTEAD OF.

Триггеры INSTEAD OF не разрешены для обновляемых представлений, использующих параметр WITH CHECK OPTION. SQL Server вызывает ошибку, если триггер INSTEAD OF добавляется к обновляемому представлению с параметром WITH CHECK OPTION. Пользователь должен удалить этот параметр при помощи инструкции ALTER VIEW перед определением триггера INSTEAD OF.

{ [ DELETE ] [, ] [ INSERT ] [, ] [ UPDATE ] }. Определяет инструкции изменения данных, по которым срабатывает триггер DML, если он применяется к таблице или представлению. Необходимо указать как минимум одну инструкцию. В определении триггера разрешены любые их сочетания в любом порядке.

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

event_type. Имя события языка Transact-SQL, которое после выполнения вызывает срабатывание триггера DDL. Список событий, которые могут быть использованы в триггерах DDL, приведен в разделе DDL-события.

event_group. Имя стандартной группы событий языка Transact-SQL. Триггер DDL срабатывает после возникновения любого события языка Transact-SQL, принадлежащего к группе event_group. Список групп событий, которые могут быть использованы в триггерах DDL, приведен в разделе Группы DDL-событий.

После завершения инструкции CREATE TRIGGER параметр event_group работает в режиме макроса, добавляя охватываемые им типы события в представление каталога sys.trigger_events.

WITH APPEND. Указывает, что требуется добавить триггер существующего типа.

Аргумент WITH APPEND не может быть использован для триггеров INSTEAD OF или при явном указании триггера AFTER. Аргумент WITH APPEND может использоваться только при указании параметра FOR без INSTEAD OF или AFTER из соображений поддержки обратной совместимости. Аргумент WITH APPEND не может быть указан, если указан параметр EXTERNAL NAME (в случае триггера CLR).

NOT FOR REPLICATION. Указывает, что триггер не может быть выполнен, если агент репликации изменяет таблицу, используемую триггером. Дополнительные сведения см. в разделе Управление ограничениями, идентификаторами и триггерами с помощью параметра «NOT FOR REPLICATION».

sql_statement. Условия и действия триггера. Условия триггера указывают дополнительные критерии, определяющие, какие события — DML, DDL или событие входа — вызывают срабатывание триггера.

Действия триггера, указанные в инструкциях языка Transact-SQL, вступают в силу после попытки использования операции.

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

Триггеры DML используют логические (концептуальные) таблицы deleted и inserted. По своей структуре они подобны таблице, на которой определен триггер, то есть таблице, к которой применяется действие пользователя. В таблицах deleted и inserted содержатся старые или новые значения строк, которые могут быть изменены действиями пользователя. Например, для запроса всех значений таблицы deleted можно использовать инструкцию:

Триггеры DML

Триггеры DML часто используются для соблюдения бизнес-правил и целостности данных. В SQL Server декларативное ограничение ссылочной целостности обеспечивается инструкциями ALTER TABLE и CREATE TABLE. Однако декларативное ограничение ссылочной целостности не обеспечивает ссылочную целостность между базами данных. Ограничение ссылочной целостности подразумевает выполнение правил связи между первичными и внешними ключами таблиц. Для обеспечения ограничений ссылочной целостности используйте в инструкциях ALTER TABLE и CREATE TABLE ограничения PRIMARY KEY и FOREIGN KEY. Если ограничения распространяются на таблицу триггера, они проверяются после срабатывания триггера INSTEAD OF и до выполнения триггера AFTER. В случае нарушения ограничения выполняется откат действий триггера INSTEAD OF, и триггер AFTER не срабатывает.

Первые и последние триггеры AFTER, которые будут выполнены в таблице, могут быть определены с использованием процедуры sp_settriggerorder. Для таблицы можно определить только один первый и один последний триггер для каждой из операций INSERT, UPDATE и DELETE. Если в таблице есть другие триггеры AFTER, они будут выполняться случайным образом.

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

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

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

Если триггер INSTEAD OF, определенный для представления, выполняет по отношению к представлению какую-либо инструкцию, которая бы снова вызвала срабатывание триггера INSTEAD OF, триггер рекурсивно не вызывается. Вместо этого инструкция выполняет изменение базовых таблиц, на которых основано представление. В данном случае определение представления должно удовлетворять всем ограничениям, установленным для обновляемых представлений. Сведения об определении обновляемых представлений см. в разделе Например, если триггер определен как INSTEAD OF UPDATE для представления и выполняет инструкцию UPDATE для этого же представления, инструкция UPDATE, выполняемая триггером, не вызывает нового срабатывания триггера. Инструкция UPDATE, выполняемая в триггере, обрабатывает представление так, как если бы у представления не имелось триггера INSTEAD OF. Столбцы, измененные с помощью инструкции UPDATE, должны принадлежать одной базовой таблице. Каждая модификация базовой таблицы вызывает применение последовательности ограничений и взвод триггеров AFTER, определенных для данной таблицы.