Расширения языка Transact-SQL

Новые операторы языка определения данных (DDL)
Новые операторы языка манипулирования данными (DML)
Новые возможности в обработке ошибок
Новый тип триггеров - DDL-триггер
Резюме

В новой версии Microsoft SQL Server 2005 язык Transact-SQL переработан для обеспечения соответствия стандарту ANSI и расширения его функциональных возможностей. Он стал более логичным и завершенным.

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

Другое новшество относится к оператору TOP. В SQL Server 2000 для этого оператора использовались фиксированные константы - TOP 5 (верхние 5 строк). А в SQL 2005 к оператору TOP можно привязывать любое выражение в пределах правил Transact-SQL, включая использование переменного или скалярного подзапроса. Оператор TOP поддерживают предложения INSERT, UPDATE и DELETE.

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

Кроме того, значительной переработке подверглись инструкции DDL (Data Definition Language, язык определения данных). Например, для того чтобы создать любой объект, необходимо воспользоваться только операторами языка DDL, а не использовать хранимые процедуры, как было до MS SQL 2005.

Новые операторы языка определения данных (DDL)

В новой версии сервера решена проблема единства методов создания, модификации и удаления объектов.

Все объекты сервера создаются оператором CREATE, модифицируются оператором ALTER, а удаляются оператором DROP. Данный подход стандартизует методы управления объектами, что упрощает разработку приложений и администрирование системы.

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

Тип объекта Создание Модификация Удаление
Синоним CREATE SYNONYM DROP SYNONYM
Сборка CREATE ASSEMBLY ALTER ASSEMBLY DROP ASSEMBLY
Тип данных CREATE TYPE DROP TYPE
Пользователь CREATE USER ALTER USER DROP USER
Роль CREATE ROLE ALTER ROLE DROP ROLE
Схема CREATE SCHEMA ALTER SCHEMA DROP SCHEMA
Контракт CREATE CONTRACT ALTER CONTRACT DROP CONTRACT
Очередь CREATE QUEUE ALTER QUEUE DROP QUEUE
Служба CREATE SERVICE ALTER SERVICE DROP SERVICE
Маршрут CREATE ROUTE ALTER ROUTE DROP ROUTE
Логин CREATE LOGIN ALTER LOGIN DROP LOGIN
Точка доступа CREATE ENDPOINT DROP ENDPOINT
Сертификат CREATE CERTIFICATE ALTER CERTIFICATE DROP CERTIFICATE
Табл. 1.1. Перечень новых DDL-операторов

Новые операторы языка манипулирования данными (DML)

Язык манипулирования данными (Data Manipulation Language, DML) - самый используемый модуль команд языка Transact-SQL. В девяноста случаях из ста применяется именно он. В языке появилось много новых конструкций: CTE-выражения, PIVOT, UNPIVOT, TOP и многие другие. Основные новшества, появившиеся в языке, будут рассмотрены в этом разделе.

CTE-выражения

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

Для решения этих задач в новой версии сервера могут быть использованы CTE-выражения (common table expression). Эта новинка позволяет определить виртуальное представление, которое можно использовать в другом операторе (например, SELECT) языка манипулирования данными.

Описание

СТЕ - временный именованный результирующий набор, который конкретизируется запросом запуска с ключевым словом WITH, может служить заменой субзапросу или использоваться в View, его можно использовать с SELECT / INSERT / UPDATE / DELETE и для рекурсивных вычислений. CTE начинается с оператора WITH. Множественные CTE разделяются запятыми.

Синтаксис

 WITH  ::= expression_name [( column_name [ ,...n ])] AS ( CTE_query_definition )

Пример использования CTE-выражения для упрощения читаемости запросов показан в листинге 1.1.

Листинг 1.1. CTE-выражения для упрощения читаемости запросов

 USE AdventureWorks; WITH DirReps(ManagerID, DirectReports) AS ( 	 SELECT ManagerID, COUNT(*) 	 FROM HumanResources.Employee AS e 	 WHERE ManagerID IS NOT NULL 	 GROUP BY ManagerID ) SELECT * FROM DirReps ORDER BY ManagerID;

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

Листинг 1.2. Однократный вызов CTE

 USE AdventureWorks; WITH low AS ( SELECT ((max (UnitPrice*OrderQty))/3) AS v FROM Sales.SalesOrderDetail), high AS ( SELECT (2 *max(UnitPrice*OrderQty)/3) AS v FROM Sales.SalesOrderDetail) SELECT SalesOrderID, low.v, high.v FROM Sales.SalesOrderDetail as sod, low, high WHERE sod.UnitPrice*OrderQty > low.v AND sod.UnitPrice*OrderQty <= high.v;

Но, наверное, одно из самых полезных применений CTE, - выполнение рекурсивных вычислений, в том числе по древовидным структурам (рис. 1.1).


Рис. 1.1. Древовидная структура

Описание

Рекурсивное CTE содержит три части:

  • "якорь", за которым следует UNION ALL, - производит инициализацию;
  • рекурсивный элемент после UNION ALL - выполняет рекурсию;
  • внешний SELECT - отбирает результаты, которые должны быть возвращены процессору запросов.

Использование CTE для рекурсивного прохода по дереву, представленному в виде таблицы (рис. 1.2), показано в листинге 1.3.


Рис. 1.2. Таблица с данными

Листинг 1.3. Использование CTE для рекурсивного прохода по дереву

 USE AdventureWorks; WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS ( - "Якорь" SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel FROM HumanResources.Employee WHERE ManagerID IS NULL 	UNION ALL - Рекурсивный элемент SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1 FROM HumanResources.Employee e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID ) SELECT * FROM DirectReports; 

Запрос выводит идентификаторы начальника и подчиненного и уровень подчиненности (рис. 1.3).


Рис. 1.3. Результаты использования CTE для рекурсивного прохода по дереву

Операторы PIVOT и UNPIVOT

SQL Server 2005 представляет два новых реляционных оператора - PIVOT и UNPIVOT, которые могут использоваться в выражении FROM. Эти операторы выполняют определенные преобразования над входным набором данных и выдают результирующий набор.

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

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

Описание PIVOT

Для работы с оператором PIVOT необходимо определить:

  • столбец, который содержит значение для повернутой таблицы (FOR pivot_column);
  • столбцы, которые будут представлять повернутую таблицу (IN () );
  • агрегативную колонку (aggregate_function(value_column)).

Описание UNPIVOT

Для работы с оператором UNPIVOT необходимо определить:

  • столбец, который содержит агрегированное значение (value_column);
  • столбец, для которого будет осуществлен "поворот" (FOR pivot_column);
  • столбцы, которые будут представлять строки "повернутой" таблицы (IN ()).

Синтаксис PIVOT

 ::= table_source PIVOT  table_alias  ::= ( aggregate_function(value_column) FOR pivot_column IN ( ) )

Синтаксис UNPIVOT

 ::= table_source UNPIVOT  table_alias  ::= ( value_column FOR pivot_column IN (  ) )  ::= column_name [, ...] 

Предположим, надо посчитать, продажу какого количества продуктов с номерами 776, 777, 774 оформили сотрудники отдела продаж.

Рассмотрим пример применения операции PIVOT (листинг 1.4) к набору данных, показанному на рис. 1.4.


Рис. 1.4. Набор данных

Листинг 1.4. Применение оператора PIVOT

 USE AdventureWorks; SELECT [SalesPersonID], [776], [777], [774] INTO [Sales].[tmpUnpivot] 	 FROM ( SELECT [OrderQty] ,[ProductID],[SalesPersonID] 	 FROM [AdventureWorks].[Sales].[SalesOrderDetail] as od JOIN [AdventureWorks].[Sales].[SalesOrderHeader] as o ON o.[SalesOrderID] = od.[SalesOrderID] ) as p PIVOT ( COUNT ([OrderQty]) FOR [ProductID] IN ( [776], [777], [774]) ) AS pvt;

ПРИМЕЧАНИЕ

Обратите внимание, что при выполнении запроса, показанного в листинге 1.4, создается постоянная таблица [Sales].[tmpUnpivot]. Это необходимо для следующего запроса (листинг 1.5).

Результат выполнения оператора PIVOT показан на рис. 1.5.


Рис. 1.5. Результат выполнения оператора PIVOT

Рассмотрим пример применения оператора UNPIVOT (листинг 1.5) к набору данных, показанному на рис. 1.5.

Листинг 1.5. Применение оператора UNPIVOT

 USE AdventureWorks; SELECT [SalesPersonID], ProductID, Quantity FROM 	( SELECT [SalesPersonID],[776] ,[777] ,[774] 		 FROM [AdventureWorks].[Sales].[tmpUnpivot]) pvt UNPIVOT (Quantity FOR ProductID 		 IN ([776] ,[777] ,[774]) 	) unpvt;

Оператор UNPIVOT создал обратный транспонированный набор данных (рис. 1.6).


Рис. 1.6. Результат выполнения оператора UNPIVOT

Ключевое слово OUTPUT

В предыдущих версиях MS SQL Server в триггерах использовались специальные таблицы inserted и deleted. Подобная возможность добавлена в MS SQL Server 2005 для операторов языка DML, выполняющих модификацию данных. Чтобы этим воспользоваться, необходимо в DML-выражение добавить ключевое слово OUTPUT.

Описание

OUTPUT может использоваться в операторах INSERT, UPDATE, DELETE:

  • при использовании его в операторе INSERT создается временная таблица inserted;
  • при использовании его в операторе DELETE создается временная таблица deleted;
  • при использовании его в операторе UPDATE создаются временные таблицы inserted и deleted.

Доступ к данным в этих таблицах возможен через table_name.column_name.

Синтаксис

 ::= { 	OUTPUT  [ ,...n ] 	 INTO @table_variable }  ::= {  / scalar_expression }  ::= { DELETED / INSERTED / from_table_name } . { * / column_name }

Рассмотрим пример использования ключевого слова OUTPUT (листинги 1.6-1.8).

Листинг 1.6. Создание временной таблицы @MyTableVar

 USE AdventureWorks; DECLARE @MyTableVar TABLE (ScrapReasonID smallint, 	 Name varchar(50), 	 ModifiedDate datetime);

Листинг 1.7. Вставка данных в таблицу @MyTableVar

 USE AdventureWorks; INSERT INTO Production.ScrapReason 	OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar VALUES (N 'Operator error' , GETDATE());

Листинг 1.8. Просмотр содержимого таблицы @MyTableVar

 SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;

В результате создается временная служебная таблица inserted (рис. 1.7).


Рис. 1.7. Временная служебная таблица inserted

ПРИМЕЧАНИЕ

Поскольку поле ScrapReasonID представляет столбец таблицы Production.ScrapReason, для которого установлено свойство IDENTITY (автогенерация значений), то, во-первых, его можно не указывать в операции вставки, а во-вторых, его значение может отличаться от показанного на рис. 1.7.

Оператор APPLY

В MS SQL Server 2000 впервые появились пользовательские функции: скалярные, подставляемые табличные и многооператорные табличные.

Оператор APPLY позволяет ссылаться на табличную функцию в коррелированном подзапросе и вызывать определенную табличную функцию один раз для каждой строки внешнего табличного выражения. APPLY определяется в выражении FROM запроса аналогично реляционному оператору JOIN.

APPLY используется в двух вариантах: CROSS APPLY и OUTER APPLY.

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

OUTER APPLY похож на CROSS APPLY, но возвращает строки из внешнего набора, даже если табличная функция возвратила пустой набор. Возвращаются значения NULL как значения столбцов, соответствующих столбцам табличной функции.

Описание

CROSS APPLY - возвращает строки, присутствующие одновременно и в таблице, и в функции.

OUTER APPLY - возвращает все строки таблицы независимо от того, возвращаются или нет соответствующие им строки из функции.

Синтаксис

left_table_source { CROSS / OUTER } APPLY right_table_source

Применение операторов CROSS APPLY и OUTER APPLY показано в листингах 1.9-1.11.

Листинг 1.9. Создание функции

 USE AdventureWorks; GO CREATE FUNCTION Sales.MostRecOrders(@intCustomerID as int) RETURNS TABLE AS RETURN 	 SELECT TOP (2) SalesOrderID, OrderDate 	 FROM Sales.SalesOrderHeader WHERE CustomerID = @intCustomerID ORDER BY OrderDate DESC ;

Листинг 1.10. Запрос на CROSS-объединение

 USE AdventureWorks; SELECT Name, mro.* FROM Sales.Store CROSS APPLY Sales.MostRecOrders(CustomerID) as mro;

Результат запроса на CROSS-объединение показан на рис. 1.8.


Рис. 1.8. Результат запроса на CROSS-объединение

Листинг 1.11. Запрос на OUTER-объединение

 USE AdventureWorks; SELECT Name, mro.* FROM Sales.Store OUTER APPLY Sales.MostRecOrders(CustomerID) as mro;

Результат запроса на OUTER-объединение показан на рис. 1.9.


Рис. 1.9. Результат запроса на OUTER-объединение

В результирующем наборе появились значения NULL, значит, функция не возвратила значения, соответствующие значениям в таблице (подобно функции LEFT OUTER JOIN между таблицами).

Функции ранжирования

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

В новой версии сервера появились четыре функции ранжирования выводимых данных в запросах: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(). Ранжирование выполняется внутри "окна", определяемого предложением PARTITION BY.

Функции RANK()

Описание

Функция RANK() возвращает ранг каждой строки внутри "окна", определяемого предложением PARTITION BY. Ранг каждой строки равен рангу предыдущей, увеличенному на единицу.

Синтаксис

RANK ( ) OVER ( [ < partition_by_clause > ] <  > )

Рассмотрим на примере, как функция RANK() ранжирует множество продуктов по цене продукта внутри "окна", определяемого названием категории (листинг 1.12).

Листинг 1.12. Выполнение функции RANK()

 USE AdventureWorks; SELECT p.Name as Product, p.ListPrice, psc.Name as Category, RANK() OVER (PARTITION BY psc.Name ORDER BY p.ListPrice DESC ) as 'Price Rank' FROM Production.Product as p INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID;

Результат выполнения функции RANK() показан на рис. 1.10.


Рис. 1.10. Результат выполнения функции RANK()

Однако RANK() может давать некорректный результат; если несколько строк имеют один и тот же rank, то rank следующей строки назначается исходя из количества строк, попавших в "окно" (рис. 1.10). Для решения этой проблемы можно пользоваться функцией DENSE_RANK().

Функции DENSE_RANK()

Описание

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

Синтаксис

DENSE_RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

Эта проблема может быть решена с помощью функции DENSE_RANK() (листинг 1.13).

Листинг 1.13. Выполнение функции DENSE_RANK()

 USE AdventureWorks; SELECT p.Name as Product, p.ListPrice, psc.Name as Category, DENSE_RANK() OVER (PARTITION BY psc.Name ORDER BY p.ListPrice DESC ) as 'Price Rank' FROM Production.Product as p INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID;

Результат выполнения функции DENSE_RANK() показан на рис. 1.11.


Рис. 1.11. Результат выполнения функции DENSE_RANK()

Функции ROW_NUMBER()

Функции ROW_NUMBER() определяют место объекта внутри группы однородных объектов, упорядоченных по какому-либо признаку.

Описание

Функция ROW_NUMBER() возвращает последовательный номер строки внутри "окна", определяемого предложением PARTITION BY, начиная с единицы для каждой первой строки внутри каждого нового "окна".

Синтаксис

ROW_NUMBER ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

В листинге 1.14 решается задача определения места товара внутри категории по его цене.

Листинг 1.14. Определение места товара внутри категории по его цене

 USE AdventureWorks; SELECT ROW_NUMBER() OVER (PARTITION BY psc.Name ORDER BY p.ListPrice DESC ) as 'Price Rank' , psc.Name as Category, p.Name as Product, p.ListPrice FROM Production.Product as p INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID;

Результат выполнения функции ROW_NUMBER() показан на рис. 1.12.


Рис. 1.12. Результат выполнения функции ROW_NUMBER()

Функции NTILE()

Функция NTILE() выполняет автоматическое разделение множества объектов по группам (категориям) на основании какого-либо признака, например определяет количество ценовых групп товаров по категориям и принадлежность каждого товара к группе.

Описание

Функция NTILE() делит "окно" (группу), определяемое предложением PARTITION BY, на указанное количество рангов, причем функция "старается", чтобы количество строк в каждом из рангов было одинаковым. Для каждой строки возвращается номер ранга, которому она принадлежит (листинг 1.15).

Синтаксис

NTILE (integer_expression) OVER ([< partition_by_clause>])

Листинг 1.15. Использование функции NTILE()

 USE AdventureWorks; SELECT NTILE(3) OVER (PARTITION BY psc.Name ORDER BY p.ListPrice DESC ) as 'Price Band' , psc.Name as Category, p.Name as Product, p.ListPrice FROM Production.Product as p INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID;

Результат выполнения функции NTILE() показан на рис. 1.13.


Рис. 1.13. Результат выполнения функции NTILE()

Оператор TOP

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

Описание

Оператор TOP возвращает "верхние" строки набора в соответствии с порядком сортировки, указанным в предложении ORDER BY.

Синтаксис

[ 	 TOP (expression) [ PERCENT ] [ WITH TIES ] ]

Листинг 1.16. Использование оператора TOP

 USE AdventureWorks; DECLARE @var1 AS int, @var2 AS int; SET @var1 = 4; SET @var2 = 2; SELECT TOP (@var1*@var2) * FROM HumanResources.Employee;

Результатом выполнения оператора TOP будет набор из 8 (4 х 2) строк, показанный на рис. 1.14.


Рис. 1.14. Результат выполнения оператора TOP

Предложение TABLESAMPLE

С помощью предложения TABLESAMPLE можно выбирать множество строк, в отличие от оператора TOP не "верхние" строки, а произвольные. Структура набора сохраняется до тех пор, пока не произойдут изменения в строках. Указав номер набора в операторе REPEATABLE, можно повторно возвращать один и тот же набор.

Синтаксис

 ::= 	TABLESAMPLE SYSTEM (sample_number [ PERCENT / ROWS ]) REPEATABLE (seed)

Описание

  • sample_number - количество строк, отбираемых из набора, либо в процентах (PERCENT) от общего числа строк в наборе, либо в строках (ROWS);
  • SYSTEM - указывает, что расчет процента возвращаемых строк будет произведен на основании единиц размещения СУБД, то есть страниц; при этом все строки страницы попадают в результат.

В листинге 1.17 приведен пример использования предложения TABLESAMPLE.

Листинг 1.17. Использование предложения TABLESAMPLE

 USE AdventureWorks; SELECT * FROM HumanResources.Employee SYSTEM TABLESAMPLE (10 PERCENT );

ПРИМЕЧАНИЕ

В результате вместо ожидаемых 29 строк (в таблице HumanResources.Employee 291 строка) мы можем увидеть 41, 42 и даже 82 строки в зависимости от того, с каких страниц производилась выборка.

Новые возможности в обработке ошибок

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

Работа с ошибками была утомительна еще из-за переменной @@ERROR, устанавливаемой на каждый оператор и проверяемой после выполнения каждой операции.

Блок TRY…CATCH

В новую версию сервера добавлен блок TRY…CATCH для обработки исключительных ситуаций (ошибок).

Описание

Обработчик состоит из блока TRY, где находится выполняемый код, и блока CATCH, в котором происходит перехват ошибки.

Синтаксис

 BEGIN TRY 	{ sql_statement / statement_block } END TRY BEGIN CATCH 	{ sql_statement / statement_block } END CATCH [ ; ]

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

Листинг 1.18. Создание хранимой процедуры

 CREATE PROCEDURE usp_someproc AS 	 BEGIN 		 BEGIN TRY 			 SELECT * FROM titles; 		 END TRY 		 BEGIN CATCH 			 SELECT ERROR_NUMBER(); 		 END CATCH 	 END ; GO

Внутри блока-"ловушки" доступны новые функции для обработки исключений:

  • ERROR_NUMBER() - номер ошибки;
  • ERROR_SEVERITY() - уровень серьезности;
  • ERROR_STATE() - номер состояния ошибки;
  • ERROR_MESSAGE() - полный текст сообщения об ошибке.

Таким образом, можно получить не только номер ошибки (как при использовании @@ERROR), а всю доступную информацию для обработки и отправки на клиентское приложение.

Функция XACT_STATE()

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

Пример создания хранимой процедуры с использованием функции, возвращающей состояние транзакции, приведен в листинге 1.19.

Листинг 1.19. Создание хранимой процедуры

 CREATE PROCEDURE usp_someproc AS BEGIN TRY 	 SELECT * FROM authors; END TRY BEGIN CATCH 	 IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION ; 	 IF (XACT_STATE()) = 1 COMMIT TRANSACTION ; END CATCH; GO

Состояние XACT_STATE() = 0 обозначает, что нет открытых транзакций.

Новый тип триггеров - DDL-триггер

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

DDL-триггеры, в отличие от стандартных DML-триггеров, "срабатывают" на события, порождаемые операциями создания (CREATE), модификации (ALTER) и удаления (DROP) объектов SQL Server. Полный перечень событий, на которые реагируют триггеры, можно найти в справочной системе по MS SQL Server 2005.

Синтаксис

 CREATE TRIGGER trigger_name ON { ALL SERVER / DATABASE } { FOR / AFTER } { event_type / event_group } [ ,...n ] AS { sql_statement [ ...n ] / EXTERNAL NAME < method specifier > } [ ; ]  ::= assembly_name.class_name[.method_name]

Пример использования DDL-триггера приведен в листингах 1.20-1.22.

Листинг 1.20. Создание пробной таблицы

 USE AdventureWorks; CREATE TABLE [Sales].[testDDLTrigger]( 	[ID] int NULL , 	[Description] varchar(100) NULL );

Листинг 1.21. Создание триггера

 CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS 	 PRINT N 'Таблицы не могут быть модифицированы или удалены' 	 ROLLBACK TRAN ;

Листинг 1.22. Инициализация срабатывания триггера

 DROP TABLE [Sales].[testDDLTrigger];

Полученное в результате сообщение об ошибке показано на рис. 1.15.


Рис. 1.15. Сообщение об ошибке

Резюме

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

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

 


Страница сайта http://silicontaiga.ru
Оригинал находится по адресу http://silicontaiga.ru/home.asp?artId=5041