Шпаргалка по "Информатике"

Типы триггеров DML

 

Триггер AFTER

Триггеры AFTER выполняются после выполнения действий инструкции INSERT, UPDATE, MERGE или DELETE. Триггеры AFTER никогда не выполняются, если происходит нарушение ограничения, поэтому эти триггеры нельзя использовать для какой-либо обработки, которая могла бы предотвратить нарушение ограничения. Для каждой из операций INSERT, UPDATE или DELETE в указанной инструкции MERGE соответствующий триггер вызывается для каждой операции DML.

Триггер INSTEAD OF

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

Функциональность триггеров AFTER и INSTEAD OF сравнивается в следующей таблице.

Функция	Триггер AFTER	Триггер INSTEAD OF
Сущности, к которым применяется триггер	Таблицы	Таблицы и представления
Количество триггеров на таблицу или представление	Несколько триггеров на одно запускающее триггеры действие (UPDATE, DELETE или INSERT).	Один триггер на одно запускающее триггеры действие (UPDATE, DELETE или INSERT).
Каскадные ссылки	Нет ограничений.	Триггеры INSTEAD OF UPDATE и DELETE нельзя определять для таблиц, на которые распространяются каскадные ограничения ссылочной целостности.
Выполнение	После следующих операций. Обработка ограничений. Декларативные ссылочные действия. Создание таблицinserted иdeleted. Действие, запускающее триггер.	Перед следующей операцией. Обработка ограничений. Вместо следующей операции. Действие, запускающее триггер. После следующих операций. Создание таблиц inserted иdeleted.
Порядок выполнения	Можно задать выполнение в первую и в последнюю очередь.	Неприменимо
Ссылки на столбцыvarchar(max),nvarchar(max) иvarbinary(max) в таблицах inserted иdeleted	Разрешены.	Разрешены.
Ссылки на столбцыtext, ntext и image в таблицах inserted иdeleted	Не допускается	Разрешены

Триггеры CLR

Триггер CLR может быть либо триггером AFTER, либо триггером INSTEAD OF. Триггер CLR может также являться триггером DDL. Вместо вызова хранимой процедуры на языке Transact-SQL триггер CLR вызывает один или несколько методов управляемого кода, являющихся членами сборки, созданной с помощью среды .NET Framework и загружены в SQL Server.

 

 

CREATE TRIGGER [ schema_name . ]trigger_name

ON { table | view }

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

{ FOR | AFTER | INSTEAD OF }

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

[ 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

 

8) Функции.

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

CREATE FUNCTION testF(@n1 int, @n2 as int)

RETURNS int

AS

BEGIN

Return (@n1*@n2)

END

----

Select dbo.testF(5, 8)

 

 

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

Типы функций

 

Функция	Описание
Функции, возвращающие наборы строк.	Возвращают объект, который можно использовать так же, как табличные ссылки в SQL-инструкции.
Агрегатные функции	Обрабатывают коллекцию значений и возвращают одно результирующее значение.
Ранжирующие функции	Возвращают ранжирующее значение для каждой строки в секции.
Скалярная функция (описывается далее)	Обрабатывают и возвращают одиночное значение. Скалярные функции можно применять везде, где выражение допустимо.

Скалярные функции

 

Категория функции	Описание
Функции конфигурации	Возвращают сведения о текущей конфигурации.
Функции преобразования	Поддержка приведения и преобразования типов данных.
Функции работы с курсорами	Возвращают сведения о курсорах.
Функции и типы данных даты и времени	Выполняют операции над исходными значениями даты и времени, возвращают строковые и числовые значения, а также значения даты и времени.
Логические функции	Выполнение логических операций.
Математические функции	Выполняют вычисления, основанные на числовых значениях, переданных функции в виде аргументов, и возвращают числовые значения.
Функции метаданных	Возвращают сведения о базах данных и объектах баз данных.
Функции безопасности	Возвращают данные о пользователях и ролях.
Строковые функции	Выполняют операции со строковым (char или varchar) исходным значением и возвращают строковое или числовое значение.
Системные функции	Выполняют операции над значениями, объектами и параметрами экземпляра SQL Server и возвращают сведения о них.
Системные статистические функции	Возвращают статистические сведения о системе.
Функции обработки текста и изображений	Выполняют операции над текстовыми или графическими исходными значениями или столбцами и возвращают сведения о значении.

Детерминизм функций

 

Различаются детерминированные и недетерминированные встроенные функции SQL Server.Функция является детерминированной, если для определенных входных значений она каждый раз возвращает один и тот же результат. Функция является недетерминированной, если она возвращает различные результаты даже для одних и тех же исходных значений. Дополнительные сведения см. в разделе Детерминированные и недетерминированные функции.  

Параметры сортировки функций

 

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

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

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

 

9)  Курсоры.

 

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

 

·           Результирующего множества курсора - множества (таблицы) строк, которое получено в результате запроса и с которым связывается курсор;

·           Позиции курсора - указателя на одну из строк результирующего множества. 

 

Позиция курсора  указывает на текущую строку курсора. Пользователь может непосредственно модифицировать или удалять эту строку операторами update или delete, используя конструкцию с названием курсора. Можно изменить текущую позицию курсора с помощью операции fetch (передвинуть и загрузить). Эта операция переводит курсор на одну или несколько строк ниже в результирующем множестве.

Курсор  аналогичен указателю на записи файла. Однако курсор можно сдвигать только вперед по результирующему множеству (последовательный доступ). Если несколько  строк уже пройдены, то нельзя вернуться назад и снова получить к ним доступ, переведя на них курсор. Этот процесс позволяет просматривать строки друг за другом.

После объявления курсора он может находиться в  двух состояниях: 

 

·           Закрытом - В этом случае не существует результирующего множества, поэтому нельзя считывать из него информацию. Первоначально курсор находится в этом состоянии. Чтобы использовать курсор, его необходимо явно открыть. После окончания работы курсор необходимо явно закрыть. SQLСервер может неявно закрыть курсор в некоторых случаях, которые будут далее перечислены;

·           Открытом - В этом случае с помощью курсора можно считывать и модифицировать строки. 

 

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

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

 

Как SQL Сервер обрабатывает курсоры 

 

Когда доступ к данным осуществляется через курсор, SQL Сервер разбивает процесс на следующие шаги: 

 

·           Объявление курсора;        

SQL Сервер создает структуру курсора и компилирует запрос, определенный для курсора. Он сохраняет скомпилированный план, но не выполняет его.

·           Открытие курсора;       

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

·           Передвижение курсора;        

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

·           Обновление или удаление данных через курсор;       

SQL Сервер обновляет или удаляет данные в указанной курсором строке результирующего множества (и в соответствующих базовых таблицах, откуда были выбраны данные). Этот шаг необязателен. 

·           Закрытие курсора;

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

·           Удаление курсора.        

SQL Сервер удаляет план выполнения курсора из памяти и все ссылки на структуру курсора. После этого нужно объявить курсор, чтобы снова использовать его. 

 

ОБЪЯВЛЕНИЕ КУРСОРОВ 

 

Пользователь должен объявить курсор прежде, чем можно будет его  использовать. В объявлении указывается  запрос, который определяет результирующее множество курсора. Пользователь может  явно объявить курсор для обновления или только для чтения с помощью ключевых слов for update (для обновления) или for read only (только для чтения). Если эти слова не указаны, то SQL Сервер определяет можно ли обновлять через курсор, основываясь на типе запроса, который формирует результирующее множество курсора. Нельзя использовать операторы update и delete в формирующем запросе для курсора, объявленного только для чтения. 

 

Синтаксис объявления курсора 

 

Синтаксис оператора declare cursor (объявление курсора) имеет следующий вид: 

 

declare название_курсора cursor    

for оператор_выбора   

[for {read only | update [of список_столбцов]}] 

 

Оператор declare cursor должен предшествовать любому оператору open (открыть) для этого курсора. Оператор declare cursor нельзя совмещать с другими операторами в одном Transact-SQL пакете за исключением случая, когда курсор используется в сохраненной процедуре. 

Оператор_выбора (select) является запросом, который определяет результирующее множество данного курсора. Вообще говоря, в этом операторе можно использовать весь синтаксис и семантику оператораselect, включая ключевое слово holdlock. Однако, в нем нельзя использовать конструкции compute, forbrowse и into.

Например, в следующем операторе  объявляется курсор authors_crsr на результирующем множестве, которое содержит всех авторов, которые не живут в Калифорнии: 

 

declare authors_crsr cursor

for select au_id, au_lname, au_fname

from authors

where state != 'CA' 

 

Оператор выбора в объявлении курсора  может содержать ссылки на названия параметров и локальные переменные. Однако эти параметры и локальные переменные должны быть определены в сохраненной процедуре, которая содержит оператор объявления курсора declare cursor. Если курсор используется в триггере, то в соответствующем операторе выбора можно ссылаться на временные триггерные таблицыinserted и deleted. Более подробную информацию об операторе выбора можно посмотреть в главе 2 «Запросы: выбор данных из таблицы».  

 

Области действия курсора 

 

Курсор определяется своей областью (диапазоном) действия, которая  определяет временной промежуток (region), в течении которого курсор существует. Вне этого промежутка курсор перестает существовать. Области действия курсора определяются следующим образом: 

 

·           Сессия - В этом случае область действия курсора начинается в момент регистрации клиента SQLСервера и заканчивается, когда клиент заканчивает работу. Эта область действия отличается от областей, определяемых сохраненными процедурами и триггерами;

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

·           Триггер - В этом случае область действия курсора начинается с момента начала выполнения триггера и заканчивается, когда триггер заканчивает свою работу.