Практическая работа 1. Проектирование базы данных
 Скачать 2.91 Mb.
|
|
| IF (COLUMNS_UPDATED() {bitwise_operator} Update_bitmask) {comparison_operator}column_bitmask […n] } sql_statement […n] -- тело триггера. } } Вторая альтернатива команды {IF UPDATE…} используется для детального анализа изменений содержимого колонок с помощью специальных функций, битовых масок, операторов побитовой обработки, оператор сравнения и логических операторов. Команда ALTER TRIGGER позволяет изменить параметры и тело триггера. С помощью команды DROP TRIGGER можно удалть любой триггер базы данных. Переименовать триггер можно системной хранимой процедурой sp_rename, а получить информацию о триггере можно при помощи системных хранимых процедур sp_helptext и sp_helptrigger. Внутри триггера допускается использование любых команд языка Transact – SQL с некоторыми ограничениями. Также допускается и вызов хранимых процедур, включая системные. Задание к практической работе №11 Задание 1. Создать таблицу authsmall из таблицы authors базы данных Pubs и для новой таблицы запрограммировать триггер auth_del, который будет выводить информацию о попытках удаления и количестве удаляемых строк, выполнив действия: 1. Создание таблицы authsmall c колонками au_id, au_fname, au_lname, phone и копирование в нее данных из таблицы authors: SELECT au_id, au_fname, au_lname, phone INTO authsmoll FROM authors PRINT ‘Содержимое таблицы authsmoll:’ SELECT * FROM authsmall 2. Создание и программирование триггера: CREATE TRIGGER auth_del ON authsmall FOR DELETE AS PRINT ‘Попытка удаления’ + STR (@@ POWCOUNT)+ 178 ‘строк в таблице authsmall’ PRINT ‘Пользователь’ + CURRENT_USER IF CURRENT_USER <> ‘dbo’ BEGIN PRINT ‘Удаление запрещено’ ROLLBACK TRANSACTION END ELSE PRINT ‘Удаление разрешено’ 3. Тестирование триггера : DELETE FROM authsmall WHERE au_fname = ‘Johnson’ DELETE FROM authsmall WHERE 2*2=5 Задание 2. Создать триггер auth_upd для таблицы authsmall, построенный в первом задании, который будет разрешать изменение столбца au_id этой таблицы всем, кроме владельца dbo, выполнив следующие действия: 1. Создание и программирование триггера: CREATE TRIGGER auth_upd ON authsmall FOR UPDATE AS SET NOCOUNT ON -- не сообщать о завершении команд; PRINT ‘Попытка изменения данных в таблице authsmall’ IF (COLUMNS_UPDATE () &1)! = 0 -- 1-й столбец; PRINT ‘Изменение столбца au_id’ IF (COLUMNS_UPDATE () &2)! = 0 -- 2-й столбец; PRINT ‘Изменение столбца au_fname’ IF (COLUMNS_UPDATE () &4)! = 0 -- 3-й столбец; PRINT ‘Изменение столбца au_lname’ IF UPDATE (Phone) PRINT ‘Изменение столбца phone’ IF ((CURRENT_USER = ‘dbo’) AND (COLUMNS_UPDATED()&1)! = 0 -- 1-ый стлбец; BEGIN PRINT ‘Пользователь dbo не может изменять’ + ‘идентификационный номер автора’ ROLLBACK TRANSACTION END 2. Тестирование триггера: UPDATED authsmall SET phone =‘415 986 - 7020’, au_fname = ‘John’ WHERE au_lname = ‘Green’ UPDATED authsmall SET phone =‘913 843 - 7302’, au_id = ‘748-126859’ WHERE au_lname = ‘Smith’ Задание 3. Создать триггер для команд INSERT и UPDATE, запрещающий производить изменения для автора Billy Geitsi, выполнив действия: 1. Создание и программирование триггера: CREATE TRIGGER auth_ ins_upd ON authsmall 179 FOR INSERT, UPDATE AS IF EXISTS (SELECT * FROM authsmall -- inserted; WHERE au_lname = ‘Geitsi’ -- фамилия; au_fname = ‘Billy’) -- имя; BEGIN PRINT ‘Недопустимо написание кнги’+ ‘автором Billy Geitsi’ ROLLBACK TRANSACTION END 2. Тестирование триггера: UPDATE authsmall SET au_lname = ‘Geitsi’, au_fname = ‘Billy’ WHERE au_lname = ‘Smith’. 180 ПРАКТИЧЕСКАЯ РАБОТА №12. СОЗДАНИЕ И УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ 12.1 Цель практической работы Изучение способов обеспечения надежной работы SQL Server с помощью механизма транзакций и контрольных точек, приобретение навыков управления локальными и распределенными транзакциями различных видов, а также ознакомление с физической и логической архитектурой журнала транзакций и способами восстановления баз данных. 12.2 Методические рекомендации для выполнения практической работы Одним из способов повышения надежности работы системы MS SQL Server 2000 является применение встроенного в систему механизма транзакций и контрольных точек и умелое его управление. Транзакция – это одна или несколько последовательных команд языка Transact – SQL, образующих логически завершенный пакет и выполняемых как единое целое. Если по какой-либо причине хотя бы одна из команд пакета не выполняется, то происходит откат системы к состоянию, в котором она была до начала транзакции, и транзакция считается не выполненной. По умолчанию каждая команда выполняется как самостоятельная транзакция. При необходимости в пакете можно явно указать начало и конец транзакции. Обработка транзакций в любой системе управления базами данных должна производиться с соблюдением следующих правил – правил ASID (Atomicity, Consistency, Isolation и Durability): Atomicity – атомарность: выполняемые в транзакции изменения либо выполняются все, либо не выполняются вовсе; Consistency – согласованность: все данные после выполнения транзакции должны находиться в согласованном состоянии с соблюдением всех правил и ограничений целостности; Isolation – изолированность: изменения данных, выполняемых Durability – долговечность: после завершения транзакции ничто не может вернуть систему в состояние, в котором она была до начала транзакции (происходит фиксация транзакции). Транзакции должны как можно меньше включать команд и изменять минимум данных. Соблюдение этого требования позволит наиболее эффективным образом обеспечить одновременную работу с данными множества пользователей системы SQL Server. Они определяются на уровне соединения с сервером. Поэтому при закрытии соединения происходит откат невыполненной транзакции, и ее нельзя выполнить позже после восстановления соединения. SQL Server поддерживает три вида определений транзакций: явное,автоматическое и подразумеваемое. Для управления явными транзакциями применяют команды: BEGIN TRANSACTION [Имя транзакции] – начало транзакции; COMMIT TRANSACTION [Имя транзакции] – конец транзакции; ROLLBACK TRANSACTION[Имя транзакции] – откат транзакции; В последних двух командах слово TRANSACTION можно либо опускать, либо заменять словом WORK. Во всех трех командах допускается использование сокращения 181 TRAN вместо слова TRANSACTION и переменной строкового типа, которой присваивается имя транзакции, вместо непосредственного указания этого имени. Дополнительный аргумент WITH MARK ‘Описание’ позволяет специальным образом маркировать транзакцию в журнале транзакций, что используется при восстановлении базы данных. Если команды явного определения транзакций не используются, то сервер работает в одном из двух режимов: а) в режиме автоматического начала транзакций, в котором каждая рассматривается как отдельная транзакция, при этом если команда выполнена успешно, то сделанные ей изменения фиксируются, и выполняется следующая команда, в противном случае производится откат транзакции и выполнение команды повторяется; б) в режиме неявного начала транзакции, когда начала транзакции не указывается, а ее завершение задается явно командой COMMIT или инициируется командами: ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRANCATE TABLE и UPDATE; в этом режиме можно использовать команды COMMIT и ROLLBACK; после завершения текущей транзакции, начинается выполнение следующей, если не был задан откат транзакции. Режим автоматического начала транзакций устанавливается по умолчанию или командой. SET IMPLICIT – TRANSACTION OFF Режим неявного (или подразумевающегося) начала транзакций задается только командой SET IMPLICIT – TRANSACTION ON. Когда в запросах используются разные базы данных, даже если они расположены на одном физическом сервере, необходимо использовать распределенные транзакции, которые на самом деле представляют собой несколько отдельных транзакций, выполняемых локально в каждой базе данных, используемой в запросе. Если при этом одна из локальных транзакций не выполняется, то происходит откат распределенной транзакции. Для управления распределенными транзакциями в MS SQL Server 2000 используется координатор DTC (Distribution Transaction Coordinator), удовлетворяющий спецификации “X/OPUN XA for Distributed Transaction Processing”. Координатор MS DTC начинает и заканчивает локальные транзакции, а также откатывает их назад, если одна из них закончилась с ошибкой. При выполнении распределенных транзакций пользователь может обращаться не только к серверам SQL Server, но и к другим источникам данных: Oracle, Access, источники ODBC и другие. Для клиентского приложения работа с определенными транзакциями практически ничем не отличается от работы с локальными транзакциями, так как все согласование транзакций в различных источниках данных выполняется автоматически и невидимо для пользователя. 182 Распределенная транзакция может быть начата несколькими способами. 1. Если приложение в локальной транзакции использует распределенный запрос, то сервер автоматически начинает выполнение распределенной транзакции. 2. Если приложение начинает локальную транзакцию и из нее вызывает удаленную хранимую процедуру при установленном параметре REMOTE_PROC_TRANSACTION, то эта транзакция автоматически расширяется до распределенной транзакции (см.sp_configure). 3. Приложение может начать распределенную транзакцию, используя методы OLE DB или ODBC. 4. Сервер начинает выполнение распределенной транзакции, если встречает команду BEGIN DISTRIBUTED TRANSACTION Имя транзакции. В этом случае для завершения и отката транзакций используются команды COMMIT TRANSACTION и ROLLBACK TRANSACTION. При работе с явными транзакциями можно использовать вложенные транзакции, выполнение которых инициируется из тела уже активной транзакции. Для управления вложенными транзакциями используются те же команды, при этом каждая команда COMMIT работает только с последней начатой транзакцией. Если в команде ROLLBACK не задано имя транзакции, то откатываются все вложенные транзакции и транзакция самого высокого уровня. Если же имеется необходимость откатить лишь часть транзакций, то предварительно надо создать точку сохранения с помощью команды SAVE TRANSACTION, которую следует указывать при откате. Функция @@TRANSACTION предназначена для определения количества активных транзакций, начатых в активном соединении. Во всех транзакциях нельзя использовать следующие команды: ALTER DATABASE, BACKUP LOG, CREATE DATABASE, DISK INIT, DROP DATABASE, DUMP TRANSACTION, LOAD DATABASE, LOAD TRANSACTION, RECONFIGURE, RESTORE DATABASE, RESTORE LOG, UPDATE STATISTICS, а также системной хранимой процедуры sp_droption и любой другой хранимой процедуры, изменяющей значения в системной базе master. Для отката таких действий необходимо использовать архивирование базы данных и последующее ее восстановление. MS SQL Server для обеспечения целостности данных использует журнал транзакции, который имеет сложную физическую и логическую архитектуру и в котором производятся все промежуточные модификации до завершения транзакции, после которой происходит фиксация изменений в самой базе данных. Контрольные точки минимизируют данные в журнале транзакций. 183 Задание к практической работе №12 Задание 1. Проверить режимы автоматического начала транзакций и неявного начала транзакций, используя переключатель IMPLICIT_TRANSACTION и команду COMMIT. Задание 2. Создать несколькими способами распределенные транзакции и убедиться в корректности их выполнения. Задание 3. Создать вложенные транзакции, выполнив следующие команды: CREATE TABLE #aaa (cola int) -- 0-й уровень BEGIN TRAN -- 1-й уровень INSERT INTO #aaaVALUES (111) BEGIN TRAN -- 2-й уровень INSERT INTO #aaaVALUES (222) BEGIN TRAN -- 3-й уровень INSERT INTO #aaaVALUES (333) SELECT * FROM #aaa SELECT ‘Вложенность транзкций’, @@TRANCOUNT ROLLBACK TRAN SELECT * FROM #aaa -- откат на 0-й уровень SELECT ‘Вложенность транзакций’, @@TRANCOUNT Проанализировать полученные результаты. Задание 4. Написать пример пакета запросов с использованием команд COMMIT и ROLLBACK для автоматических, неявных и явных транзакций. Задание 5. Написать пример пакета команд, иллюстрирующих использование средств оптимизации при откате транзакций. Задание 6. Используя средства MS SQL Server 2000, изучить физическую и логическую архитектуру журнала транзакций. Задание 7. С помощью системной хранимой процедуры sp_configure изменить интервал контрольных точек для базы данных Pubs. Задание 8. Уточнить синтаксис команд управления транзакциями и написать пример пакета с использованием всех вариантов этих команд. 184 ПРАКТИЧЕСКАЯ РАБОТА №13. СОЗДАНИЕ, ПРИМЕНЕНИЕ И УПРАВЛЕНИЕ КУРСОРАМИ 13.1 Цель практической работы Изучение назначения и типов курсоров, синтаксиса и семантики команд языка Transact – SQL для создания и открытия курсоров, выборки данных из курсора и изменения строк таблиц с помощью курсоров, удаления данных, закрытия и освобождения курсоров, а также приобретения навыков их применения и управления с помощью команд и системных хранимых процедур SQL Server. 13.2 Методические рекомендации для выполнения практической работы Набор данных, имеющийся в таблице базы данных, называется полным набором строк таблицы (complete set of rows). Набор строк, возвращаемый команду SELECT, называется результирующим набором данных (result set). Он является частью полного набора, отфильтрованного горизонтально с помощью условий, заданных в разделе WHERE. Можно в результирующий набор не включать те или иные столбцы, применяя вертикальную фильтрацию. Результирующие наборы могут содержать сотни тысяч строк, и клиентские приложения не всегда справляются с таким объемом данных. Для решения этой проблемы используются курсоры, которые представляют собой окна, налагаемые на результирующие набором данных и выделяющие требуемую часть данных. Перемещая созданный курсор, можно получить доступ ко всем результирующим данным. Таким образом, курсоры SQL Server представляют собой механизм обмена данными между сервером и клиентом, который минимизирует ресурсы клиентского приложения. Однако всегда, когда это возможно, следует избегать использования курсоров и применять команды SELECT, UPDATE, DELETE и INSERT. MS SQL Server поддерживает три вида курсоров: 1. Курсоры Transact – SQL, которые применяются внутри триггеров, хранимых процедур и сценариев; 2. Курсоры сервера, которые действуют на сервере и реализуют программный интерфейс приложений для ODBC, OLE DB и DB_Library; 3. Курсоры клиента, которые реализуются на клиенте и выбирают весь результирующий набор для ускорения обработки данных. Один курсор может базироваться на нескольких таблицах, расположенных в разных базах данных. Операция считывания определенных в курсоре данных называется выборкой (fetch). Если за одну операцию курсор позволяет выбрать несколько строк таблицы, то такой курсор называется блочным. По способу просмотра данных курсоры бывают последовательные (forward only), которые обеспечивают просмотр строк только в одном направлении – от начала к концу, и прокручиваемые, которые допускают просмотр в обоих направлениях и переход к произвольной строке. По представляемым возможностям курсоры делятся на четыре типа: статические, динамические, последовательные ключевые. Тип курсора определяется на стадии его создания и не может быть изменен. Статический курсор (static cursor) называют также курсорами моментального снимка (snapshot cursor). При открытии такого курсора сервер выбирает все данные, 185 соответствующие заданным критериям, и сохраняет результирующий набор строк в системной базе данных tempdb без изменения, если даже исходные строки и изменяются. Поэтому статический курсор всегда открывается в режиме “только для чтения”. Динамические курсоры (dynamic cursor) противоположны статическим. При их использовании не создается полная копия исходных данных, а выполняется динамическая выборка данным из исходных таблиц только при обращении пользователя к тем или иным данным, при этом на время выборки соответствующие строки блокируются сервером. После выборки строк исходные строки могут изменяться пользователями, но эти изменения уже не отражаются в выбранных строках. С другой стороны, изменения в выбранных строках не будут видны другим пользователям, пока они не будут подтверждены (committed). Последовательный курсор выбирает данные только от начала к его концу. Он не хранит результирующий набор. Строки считываются из базы данных, как только они выбираются в курсоре. Это позволяет отображать все изменения в базе данных. В курсоре видно самое последнее состояние данных. Курсоры, зависящие от набора ключей (keyset-driven cursor), или ключевые курсоры, построены на основе уникальных идентификаторов. Множество всех уникальных идентификаторов (ключей) строк таблиц базы данных называется набором ключей. Сервер блокирует строки исходных таблиц только на время составления таблицы ключей. Ключевой курсор представляет набор ключей, идентифицирующих строки полного результирующего набора курсора. Набор ключей строится в системной базе данных tempdb. При работе с курсорами можно выделить пять основных операций: создание курсора, открытие курсора, выборка из курсора и изменение строк данных с помощью курсора, закрытие курсора и освобождение курсора. |