НОВЫЙ КУРС БД 2013

количество оперативной памяти, доступной SQL Server, при помощи увеличения физической оперативной памяти на сервере или при помощи параметра MAX SERVER MEMORY;

4)Еще одна возможность справиться с неприятностями — использовать флаги трассировки. Флаги трассировки используются, чтобы включать или выключать определенное поведение в SQL Server. Установить флаг трассировки можно, используя DBCC TRACEON. С флагами трассировок можно ознакомится на сайте Microsoft в разделе MSDN.

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

http://www.rsdn.ru/article/db/mssqllocks.xml http://msdn.microsoft.com/ru-ru/library/ms190805.aspx http://msdn.microsoft.com/ru-ru/library/ms187373(v=sql.90).aspx http://www.sql.ru/articles/mssql/2004/04110303advancedlocking.shtml http://www.t-sql.ru/post/nolock.aspx

4.6Триггеры

4.6.1Общие сведения о триггерах и DDL триггеры

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

триггеры, срабатывающие при изменении данных, т.е. срабатывают при вызове UPDATE, INSERT или DELETE сценариев (триггеры языка описания данных Data Definition Language - DDL)

триггеры, срабатывающие при изменении метаданных, т.е. CREATE, ALTER, DROP сценариев (триггеры языка манипулирования данными Data Manipulation Language – DML)

DDL триггеры были впервые введены в 2005 версии SQL Server. Они необходимы, если нужно следить за внесением изменений в структуру базы данных и регистрировать хронологию этих изменений. Про DDL триггеры я расскажу кратко, а более подробно остановлюсь на DML триггерах. Итак, триггеры DDL создаются для определенной команды Transact-SQL, т.е. настраиваются на определенное событие в базе данных, например, создание таблицы или представления. При этом набор команд, для которых можно определить триггеры DDL, очень большой. Вот лишь некоторые из этих команд:

CREATE DATABASE, ALTER DATABASE, DROP DATABASE;

CREATE LOGIN, ALTER LOGIN, DROP LOGIN;

CREATE USER, ALTER USER, DROP USER;

CREATE TABLE, ALTER TABLE, DROP TABLE;

CREATE VIEW, ALTER VIEW, DROP VIEW;

CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE и т. п.

101

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

CREATE TRIGGER <имя триггера> ON DATABASE FOR [событие срабатывания] AS

<код триггера>

В самом простом варианте триггер DDL может выглядеть так:

CREATE TRIGGER MyDDLTrigger ON DATABASE FOR DROP_TABLE AS

PRINT 'Таблица была удалена'

Если выполнить эту команду в текущей базе данных, то при любом удалении таблицы в этой базе будет выдано сообщение 'Таблица была удалена'. Конечно, в реальной базе данных используют более функциональные триггеры, например, триггеры, отправляющие уведомления по электронной почте при помощи хранимых процедур Database Mail. В большинстве случаев требуется получить в триггере более подробную информацию о сути происходящих событий (например, чтобы отправить эту информацию по электронной почте). Для этого удобнее всего использовать специальную функцию EVENTDATA(). Например, чтобы получить информацию о конкретном запросе (с именем таблицы), который произвел ее удаление, можно использовать код вида:

CREATE TRIGGER MySecondDDL ON DATABASE FOR DROP_TABLE AS

SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar( max)')

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

CREATE TRIGGER MyNextDDL ON DATABASE FOR DROP_TABLE AS

PRINT ' далять таблицы в этой базе данных запрещено!';

ROLLBACK

Бывает необходимо, что триггер DDL или DML нужно на время отключить, можно воспользоваться командой DISABLE TRIGGER, например:

DISABLE TRIGGER <имя триггера> ON DATABASE

Удаление триггера DDL производится стандартной командой:

DROP TRIGGER <имя триггера> ON DATABASE

Я не буду подробно останавливаться на этом виде триггеров, поскольку этот вид триггеров нужен в основном для администрирования системы, нежели для обеспечения целостности данных. Данный вид триггеров выходит за рамки данной статьи. Информацию о них можно посмотреть здесь: http://msdn.microsoft.com/ru-ru/library/ms175941.aspx

102

4.6.2 DML триггеры

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

CREATE TRIGGER <имя триггера> ON [<имя схемы>.]<имя таблицы или представления> [WITH ENCRYPTION | EXECUTE AS <CALLER | SELF | <USER> >]

FOR | AFTER | INSTEAD OF [DELETE] [,] [NSERT] [,] [UPDATE] [WITH APPEND]

[NOT FOR REPLICATION] AS

<код триггера> | EXTERNAL NAME <ИМЯ МЕТОДА В СБОРКЕ>

Давайте рассмотрим этот длинный код. Синтаксис определения триггера похож на создание хранимой процедуры. При указании аргумента WITH ENCRYPTION сервер выполняет шифрование кода триггера, чтобы никто, включая администратора, не мог получить к нему доступ и прочитать его. Шифрование часто используется для скрытия авторских алгоритмов обработки данных, являющихся интеллектуальной собственностью программиста или коммерческой тайной. Аргумент WITH APPEND позволяет создавать несколько триггеров каждого типа. При создании триггера с аргументом NOT FOR REPLICATION запрещается его запуск во время выполнения модификации таблиц механизмами репликации. Конструкция AS <код триггера> определяет набор SQLкоманд, которые будут выполнены при запуске триггера. Отметим, что внутри триггера не допускается выполнение ряда операций, таких, например, как: создание, изменение и удаление базы данных; восстановление резервной копии базы данных или журнала транзакций. Выполнение этих команд не разрешено, так как они не могут быть отменены в случае отката транзакции, в которой выполняется триггер. После команды ON указывается имя таблицы или представления, на которое накладывается триггер. Опцию FOR | AFTER | INSTEAD OF [DELETE] [,] [NSERT] [,] [UPDATE] рассмотрим более подробно. Она указывает, при каких событиях должен срабатывать триггер. Т.е. мы можем указать как одно действие, так и все сразу через запятую. И прежде чем рассмотреть все эти действия обратимся к ключевым словам FOR, AFTER и INSTEAD OF. Ключевые слова FOR и AFTER работают одинаково и срабатывают сразу при модификации данных. Поэтому, какое слово использовать - разницы нет (чем это вызвано я объяснить не могу).Эти параметры определяют поведение триггеров:

AFTER (или FOR) триггер выполняется после успешного выполнения вызвавших его команд. Если же команды по какой-либо причине не могут быть успешно завершены, триггер не выполняется. Следует отметить, что изменения данных в результате

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

изменения. Можно определить	несколько AFTER -триггеров				для каждой операции
( INSERT, UPDATE, DELETE ).	Если	для	таблицы	предусмотрено		выполнение
нескольких AFTER -триггеров,	то	с	помощью	системной		хранимой
процедуры sp_settriggerorderможно указать, какой из них будет					выполняться первым, а

какой последним. По умолчанию в SQL Server все триггеры являются AFTER-триггерами.

INSTEAD OF триггер вызывается вместо выполнения команд. В отличие от AFTER - триггера INSTEAD OF -триггер может быть определен как для таблицы, так и для представления. Для каждой операции INSERT, UPDATE, DELETE можно определить только один INSTEAD OF -триггер. триггеры INSTEAD OF срабатывают до триггеров FOR или AFTER. И если в таблице имеются триггеры AFTER, они сработают после обработки ограничений. В случае нарушения ограничений выполняется откат действий триггеров INSTEAD OF, а триггер AFTER не срабатывает. Графически схему работы триггеров можно представить так, как показано на рисунке 1.

103

Рисунок 1. орядок выполнения триггеров

Определившись, с командами FOR и INSTEAD OF перейдем к событиям, на которые реагируют триггеры. Как уже было сказано существует три типа триггеров:

INSERT TRIGGER – запускаются при попытке вставки данных

UPDATE TRIGGER – запускаются при попытке изменения данных

DELETE TRIGGER – запускаются при попытке удаления данных

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

Допускается создание триггера, реагирующего на две или на все три команды. При выполнении любой из команд (Insert, Update, Delete) сервер создает две специальные таблицы: inserted и deleted. В них содержатся списки строк, которые будут вставлены или удалены по завершении транзакции. Читателю может быть не очень понятно, о какой транзакции идет речь, и поэтому я хочу это пояснить. Дело в том, что когда производится запуск триггера, SQL Server скрытно запускает транзакцию (так называемую неявную транзакцию), если транзакция явно не

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

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

104

Триггер с командой INSERT

Пример такого триггера:

CREATE TRIGGER DML_AddRecordToMarks ON Students FOR INSERT

AS

IF EXISTS (Select ‘true’ from students where group is null) BEGIN

RAISERROR (‘Группа студента должна быть указана’)

ROLLBACK TRAN END

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

Триггер с командой DELETE

Пример такого триггера:

CREATE TRIGGER DML_PrintDelete ON Students FOR DELETE

AS

PRINT(‘ роизведено удаление студента’)

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

Триггер с командой UPDATE

Пример такого триггера:

CREATE TRIGGER DML_AddRecordToMarks ON Students FOR UPDATE

AS

PRINT(‘ роизведено обновление студента’)

Этот триггер сработает при любом изменении данных из таблицы Students. При этом в таблице deleted находятся старые значения строк, которые будут удалены при успешном завершении триггера. Новые значения строк содержатся в таблице inserted. Эти строки добавятся в исходную таблицу после успешного выполнения триггера.

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

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

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

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

105

Команда вставки записи в таблицу Сделка может быть, например, такой:

INSERT INTO Сделка VALUES (3,1,-299,'01/08/2002')

Создаваемый триггер должен отреагировать на ее выполнение следующим образом: необходимо отменить команду, если в таблице Склад величина остатка товара оказалась меньше продаваемого количества товара с введенным кодом (в примере код товара=3 ). Во вставляемой записи количество товара указывается со знаком "+", если товар поставляется, и со знаком "-", если он продается. Представленный триггер настроен на обработку только одной добавляемой записи.

CREATE TRIGGER Insert_ Сделка ON Сделка FOR INSERT

AS

IF @@ROWCOUNT=1 BEGIN

IF NOT EXISTS(SELECT * FROM inserted

WHERE -inserted.количество<=ALL(SELECT

Склад.Остаток FROM Склад,Сделка

WHERE Склад.КодТовара= Сделка.КодТовара))

BEGIN ROLLBACK TRAN

PRINT

'Отмена поставки: товара на складе нет'

END END

Давайте теперь посмотрим как создать триггер для обработки операции удаления записи из таблицы Сделка, например, такой команды:

DELETE FROM Сделка WHERE КодСделки=4

Для товара, код которого указан при удалении записи, необходимо откорректировать его остаток на складе. Триггер обрабатывает только одну удаляемую запись.

CREATE TRIGGER Delete_Сделка

ON Сделка FOR DELETE

AS

IF @@ROWCOUNT=1 -- удалена одна запись

BEGIN

DECLARE @y INT,@x INT

--определяется код и количество товара из --удаленной из таблицы Склад записи

SELECT @y=КодТовара, @x=Количество

FROM deleted

--в таблице Склад корректируется количество --товара

UPDATE Склад

SET Остаток=Остаток-@x WHERE КодТовара=@y

END

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

106

Приложение 1 Типы данных SQL Server (2005 версия)

	Обозначение	Класс	Размер	Описание
	типа		(байт)
	Bit	Целочисленные	1	Хранит значения 0 или 1. Если разрешены NULL
		данные		значения, то используется еще 1 бит байта.
	Bigint	Целочисленные	8	От -263 до 263-1 (92 квинтиллиона)
		данные
	Int	Целочисленные	4	От -2 147 483 648 до 2 147 483 647
		данные
	Smallint	Целочисленные	2	От -32768 до 32677
		данные
	Tinyint	Целочисленные	1	От 0 до 255
		данные
	Decimal	Целочисленные	Задается	От -1038-1 до 1038-1
		данные	отдельно
	Numeric	Целочисленные	Задается	От -1038-1 до 1038-1
		данные	отдельно
	Money	Финансовые	8	От -263 до 263-1 (92 квинтиллиона)
		данные
	Smallmoney	Финансовые	4	От -214 748.3648 до 214 748.3647
		данные
	Float (real)	Приближенные	Задается	Задается в битах!
		данные	отдельно	От -1.79Е+308 до 1.79Е+308
	Datetime	Данные о дате и	8	С 1 января 1753 года по 31 декабря 9999
		времени		Точность 3 сотых секунды
	Smalldatetime	Данные о дате и	4	С 1 января 1900 года по 6 июня 2079 года
		времени		Точность 1 минута
	Cursor	Специальные	1	Указатель на курсор
		числовые данные
		Специальные		Это количество секунд прошедших с 1 января 1970
	Timestamp		8	года (изменение данного столбца пользователем
		двоичные данные
				запрещено) точность 3 сотых секунды
		Специальные		GUID (Globally Unique Identifier) — статистически
	Uniqueidentifier		16	уникальный 128-битный идентификатор. (изменение
		двоичные данные
				данного столбца пользователем запрещено)
				Символьные данные фиксированной длины. Если
	Char	Символьные	Задается	количество данных не хватает до заданной длины,
		данные	отдельно	пустоты заполняются пробелами. Максимальная
				длина 8000 символов. Кодировка любая!

				Символьные данные переменной длины. Если
				количество данных не хватает до заданной длины,
	Varchar	Символьные	Задается	пустоты не заполняются пробелами. Максимальная
		данные	отдельно	длина 8000 символов, однако, для обозначения длины
				можно использовать varchar (max), что позволяет
				хранить 231 байт символов. Кодировка любая!
	Text	Символьные	Задается	Устаревший тип данных, применяется для
		данные	отдельно	совместимости. Не применять!
		Символьные		Символьные данные фиксированной длины. Если
	Nchar	данные в	Задается	количество данных не хватает до заданной длины,
		кодировке	отдельно	пустоты заполняются пробелами. Максимальная
		Unicode		длина 4000 символов. Кодировка Unicode!
				Символьные данные переменной длины. Если
		Символьные		количество данных не хватает до заданной длины,
	Nvarchar	данные в	Задается	пустоты не заполняются пробелами. Максимальная
		кодировке	отдельно	длина 4000 символов, однако для обозначения длины
		Unicode		можно использовать nvarchar (max), что позволяет
				хранить 231 байт символов. Кодировка любая!
		Символьные
	Ntext	данные в	Задается	Устаревший тип данных, применяется для
		кодировке	отдельно	совместимости. Не применять!
		Unicode
	binary	Двоичные данные	Задается	Двоичные данные фиксированной длины с
			отдельно	максимальной длиной 8000 байтов
				Двоичные данные переменной длины с максимальной
	Varbinary	Двоичные данные	Задается	длиной 8000 байтов, однако, для обозначения длины
			отдельно	можно использовать Varbinary (max), что позволяет
				хранить 231 байт данных.
	Image	Двоичные данные	Задается	Устаревший тип данных, применяется для
			отдельно	совместимости. Не применять!
			Определяе
			тся	Этот тип данных предназначен для работы с
	table	-	динамичес	результирующим набором данных. Используются в
			ки по	основном в функциях. Использовать такой тип для
			особому	столбцов таблицы запрещено!
			принципу
			Определяе
			тся	Универсальный тип данных, длина определяется в
	Sql_variant	-	динамичес	зависимости от того, к какому типу будут приведены
			ки по	данные в дальнейшем. (приведение к определенному
			особому	типу должно быть явным!)
			принципу
	xml	Символьные	Задается	Хранит данные с использованием специальных тегов
		данные	отдельно	XML.

108

Приложение 2. Максимальные размеры и количества для различных объектов SQL Server

В следующей таблице приводятся максимальные размеры и количества различных объектов, определяемых в базах данных SQL Server или ссылающихся на них Transact-SQL инструкциях.

			Максимальные		Максимальные
	Объект компонента SQL Server		размеры и		размеры и
			количества для SQL		количества для SQL
	Database Engine
			Server (32-разрядная		Server (64-
			версия)		разрядная версия)
	Размер пакета1		65 536 * размер		65 536 * размер
			сетевого пакета		сетевого пакета
	Байтов на столбец с короткой строкой	8000		8000
	Байтов на GROUP BY, ORDER BY	8,060		8,060
	Байтов на ключ индекса2	900		900
	Байтов на внешний ключ	900		900
	Байтов на первичный ключ	900		900
	Байтов на строку8	8060		8060
	Байтов в исходном тексте хранимой		Меньше размера		Меньше размера
	процедуры		пакета или 250 МБ		пакета или 250 МБ
	Байтов на столбец varchar(max),	2^31-1		2^31-1
	varbinary(max), xml, text или image
	Символов на	2^30-1		2^30-1
	столбец ntext или nvarchar(max)
	Количество кластеризованных индексов	1		1
	в таблице
	Столбцов на GROUP BY, ORDER BY		Ограничивается		Ограничивается
			только числом байтов		только числом
					байтов
	Столбцов или выражений в инструкции	10		10
	GROUP BY WITH CUBE или WITH
	ROLLUP
	Столбцов на ключ индекса7	16		16
	Столбцов на внешний ключ	16		16
	Столбцов на первичный ключ	16		16
	Столбцов на неширокую таблицу	1024		1024
	Столбцов на широкую таблицу	30000		30000
	Столбцов на инструкцию SELECT	4096		4096
	Столбцов на инструкцию INSERT	4096		4096

109

Соединений на одного клиента	Максимальное	Максимальное
	значение настроенных	значение
	соединений	настроенных
		соединений
Размер базы данных	524 272 ТБ	524 272 ТБ
Баз данных на один экземпляр SQL	32767	32767
Server
Файловых групп на одну базу данных	32767	32767
Файлов на одну базу данных	32767	32767
Размер файла (данные)	16 ТБ	16 ТБ
Размер файла (журнал)	2 ТБ	2 ТБ
Ссылок на таблицы внешнего ключа для	253	253
таблицы4
Длина идентификатора (в символах)	128	128
Экземпляров на один компьютер	50 экземпляров на	50 экземпляров на
	отдельном сервере	отдельном сервере.
	для всех выпуском (у	25 экземпляров на
	выпуска Workgroup не	отказоустойчивом
	более 16 экземпляров)	кластере.
	25 экземпляров на
	отказоустойчивом
	кластере.
Длина строки, содержащей инструкции	65 536 * размер	65 536 * размер
SQL (размер пакета)1	сетевого пакета	сетевого пакета
Блокировок на соединение	Максимальное число	Максимальное число
	блокировок на сервер	блокировок на сервер
Блокировок на экземпляр SQL Server5	до 2 147 483 647	Ограничено только
		объемом памяти
Уровней вложенных хранимых	32	32
процедур6
Вложенных запросов	32	32
Уровней вложенных триггеров	32	32
Количество некластеризованных	999	999
индексов у таблицы
Количество уникальных выражений в	32	32
предложении GROUP BY, если
присутствует одна из следующих
конструкций: CUBE, ROLLUP,
GROUPING SETS, WITH CUBE, WITH
ROLLUP
		110