Создание баз данных

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

Рассмотрим  следующий пример:

BEGIN TRY

PRINT 'Первая попытка';

--Имитация ошибки (деление на ноль)

DECLARE @i int

SET @i = 5/0

PRINT 'Вторая попытка';

END TRY

BEGIN CATCH

PRINT 'Секция обработки ошибки';

END CATCH;

PRINT 'Третья попытка';

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

Первая попытка

Секция обработки ошибки

Третья попытка

В этом примере SQL Server выполняет секцию TRY, пока не встречает строку имитирующую ошибку (в данном случае деление на ноль). После этого все последующие инструкции в блоке TRY (в данном случае вывод сообщения о второй попытке) пропускаются, и управление передается в секцию CATCH. Следом за блоком CATCH выполняется следующая по порядку инструкция, выводящая сообщение о третьей попытке.

Если в  данном примере закомментировать строку, вызывающую ошибку, блок TRY будет выполнен полностью, а блок CATCH - проигнорирован:

Первая попытка

Вторая попытка

Третья попытка

Активация сообщений об ошибках вручную. Инструкция RAISERROR

Чтобы вернуть  произвольное сообщение об ошибке в  вызывающую процедуру или клиентское приложение, используют команду RAISERROR.

Синтаксис этой команды следующий:

RAISERROR ( сообщение или номер ошибки, степень_серьезности, состояние, [ дополнительные_аргументы ])

Степень серьезности  ошибки является указанием на то, какие  меры следует принимать с учетом этой ошибки. Система обозначений  степеней серьезности ошибок в СУБД SQL Server охватывает широкий спектр сообщений об ошибках, включая те, которые по существу являются информационными (со значениями степеней серьезности 1-18), считаются относящимися к системному уровню (19-25) и даже рассматриваются как катастрофические (20-25). При возникновении ошибок со степенями серьезности 20 и выше автоматически завершается работа пользовательских соединений. Если необходимо завершить выполнение процедуры и активировать в клиентской программе ошибку, как правило, указывается степень серьезности 16.

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

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

RAISERROR('Имитация ошибки',16,1)

Управление транзакциями

Концепция транзакций – неотъемлемая часть любой клиент-серверной  базы данных.

Под транзакцией понимается неделимая с точки зрения воздействия на БД последовательность операторов манипулирования данными (чтения, удаления, вставки, модификации), приводящая к одному из двух возможных результатов: либо последовательность выполняется целиком, если все операторы правильные, либо вся транзакция откатывается, если хотя бы один оператор не может быть успешно выполнен. Обработка транзакций гарантирует целостность информации в базе данных. Таким образом, транзакция переводит базу данных из одного целостного состояния в другое.

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

• уменьшение баланса исходящего счета;
• увеличение баланса принимающего счета.

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

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

BEGIN TRAN[SACTION] – объявление начала транзакции (в журнале транзакций фиксируются первоначальные значения изменяемых данных и момент начала транзакции).

COMMIT TRAN[SACTION] – фиксация транзакции (если в теле транзакции не было ошибок, то эта команда предписывает серверу зафиксировать все изменения, сделанные в транзакции, после чего в журнале транзакций помечается, что изменения зафиксированы и транзакция завершена).

ROLLBACK TRAN[SACTION] – откат транзакции (когда сервер встречает эту команду, происходит откат транзакции (отмена всех изменений), восстанавливается первоначальное состояние системы и в журнале транзакций отмечается, что транзакция была отменена).

Рассмотрим следующий пример.

1. Откройте новое окно запроса и выберите Sales в качестве активной базы данных
2. Введите и выполните следующий запрос

BEGIN TRANSACTION

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

3. Введите и выполните следующий запрос

INSERT City

VALUES ('Новый город')

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

SELECT *

FROM City

В таблице появилась новая запись, но эти изменения видны только в данном соединении

5. Откройте новое окно запроса, введите и выполните в нем предыдущий запрос. Запрос не вернет результатов, поскольку он ждет завершения транзакции, запущенной в другом окне.
6. Вернитесь в первое окно, введите и выполните следующий запрос

ROLLBACK TRANSACTION

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

Операция  оформления нового заказа предполагает добавление новых записей сразу  в две таблицы: Order и OrdItem. Реализуем данную двойную операцию в виде единой транзакции:

BEGIN TRAN

 

BEGIN TRY

  INSERT [Order](IdCust)

  VALUES (2)

 

  INSERT OrdItem(IdOrd,IdProd,Qty,Price)

  VALUES (SCOPE_IDENTITY(),1,1,5)

END TRY

BEGIN CATCH

  ROLLBACK TRAN

  RAISERROR('Ошибка',16,1)

  RETURN

END CATCH

 

COMMIT TRAN

Триггеры

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

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

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

Оператор	Содержимое таблицы inserted	Cодержимое таблицы deleted
INSERT	Добавленные строки	Пусто
UPDATE	Новые строки	Старые строки
DELETE	Пусто	Удаленные строки

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

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

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

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

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

 

CREATE TRIGGER имя_триггера 
ON имя_таблицы 
AFTER { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
AS 
{ инструкции T-SQL }

 

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

Определим в  таблице City триггер, отслеживающий все изменения в данной таблице. Для хранения информации обо всех операциях модификации, выполняемых над данными в этой таблице, создадим специальную таблицу sysCityAudit со следующими столбцами: IdOperation (уникальный идентификатор операции), TypeOp (тип операции: вставка, обновление или удаление), IdCity, CityName, DateAndTime (дата и время выполнения операции), UserName (имя пользователя, изменившего данные). Запрос на создание данной таблицы приведен ниже:

CREATE TABLE [dbo].[sysCityAudit](

[IdOperation] [int] IDENTITY(1,1) NOT NULL,

[TypeOp] [varchar](50) NOT NULL,

[IdCity] [int] NOT NULL,

[CityName] [nvarchar](20) NULL,

[DateAndTime] [datetime] NOT NULL CONSTRAINT [DF_sysCityAudit_DateAndTime]  DEFAULT (getdate()),

[UserName] [nvarchar](256) NOT NULL CONSTRAINT [DF_sysCityAudit_UserName]  DEFAULT (user_name()),

 CONSTRAINT [PK_sysCityAudit] PRIMARY KEY CLUSTERED

(

[IdOperation] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

Запрос на создание триггера, отслеживающего все  изменения в таблице City:

CREATE TRIGGER [dbo].[tr_CityAudit] ON [dbo].[City]

  AFTER INSERT,DELETE,UPDATE

AS

BEGIN

  SET NOCOUNT ON;

 

  IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)

    INSERT sysCityAudit(TypeOp,IdCity,CityName)

    SELECT 'Обновление', IdCity, CityName

    FROM inserted

  ELSE IF EXISTS(SELECT * FROM inserted) --

    INSERT sysCityAudit(TypeOp,IdCity,CityName)

    SELECT 'Вставка', IdCity, CityName

    FROM inserted

  ELSE

    INSERT sysCityAudit(TypeOp,IdCity,CityName)

    SELECT 'Удаление', IdCity, CityName

    FROM deleted

END

Проверьте работоспособность  вновь созданного триггера. Для этого  произведите в таблице City различные изменения и убедитесь, что подробная информация о них была записана в таблицу sysCityAudit.