16. Триггеры. Необходимость их применения. Синтаксис команды создания / изменения logoNтриггеров (подробно). Синтаксис команды удаления триггера (подробно). Пример триггера.

Триггер – это хранимая процедура, которая начинает свою работу в случае выполнения действия, на которое триггер настроен. Триггеры применяются для решения задач поддержания целостности (корректности) данных, когда по каким-либо причинам невозможно (неудобно) использовать ограничения FOREIGN KEY (или ограничений на значения столбцов), и безопасности, когда, например, недопустимы какие-либо изменения в данных. Триггеры бывают нескольких типов: DML, DDL и LOGON. DML триггеры могут срабатывать при выполнении (после выполнения или вместо выполнения) команд INSERT, UPDATE и DELETE для таблиц или представлений. Этот тип триггеров присутствовал и в MS SQL Server 2000. DDL триггеры могут срабатывать при выполнении после выполнения команд CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS и некоторых системных процедур. LOGON триггеры срабатывают после установки соединения с MS SQL Server. DDL и LOGON триггеры можно использовать только в MS SQL Server 2005

Создание:

LOGON триггер:

CREATE TRIGGER название_триггера

ON ALL SERVER

[WITH

{ ENCRYPTION |

EXEC[UTE] AS {CALLER | SELF ‘login_name’}

}[,]

]

{ FOR [AFTER]} LOGON

AS

{

Перечень_команд_SQL |

EXTERNAL NAME имя_сборки.имя_класса.название_метода [;]

}

Удаление:

DML триггер:

DROP TRIGGER пространство_имен.имя_триггера1[, пространство_имен.имя_триггера2…] [;]

DDL триггер:

DROP TRIGGER имя_триггера1[, имя_триггера2…]

ON {DATABASE | ALL SERVER}

[;]

LOGON триггер:

DROP TRIGGER имя_триггера1[, имя_триггера2…]

ON ALL SERVER

причем ключевое слово DATABASE показывает, что DDL триггер(ы) имеют уровень БД, а ключевое слово ALL SERVER показывает, что DDL триггер(ы) имеют уровень сервера.

Следует отметить, что:

4. Уничтожение таблицы уничтожает и все связанные с ней DML триггеры.

5. Когда триггер уничтожается, то информацию о нем из представлений sys.objects, sys.triggers, sys.sql_modules также удаляется.

6. Несколько DDL триггеров можно удалить одной командой DROP TRIGGER в том случае если все они имеют или уровень БД, или уровень сервера.

Для того чтобы переименовать триггер, необходимо выполнить соответствующие команды DROP TRIGGER и CREATE TRIGGER

17. Триггеры. Необходимость их применения. Разрешение / запрещение срабатывания различных типов триггеров (команды ENABLE/ DISABLEи команда ALTERTABLE, применяемая для разрешения / запрещения срабатывания триггеров). Особенности использования этих команд. Рекурсивное срабатывание триггеров и способы борьбы с этим явлением. Примеры.

Триггер – это хранимая процедура, которая начинает свою работу в случае выполнения действия, на которое триггер настроен. Триггеры применяются для решения задач поддержания целостности (корректности) данных, когда по каким-либо причинам невозможно (неудобно) использовать ограничения FOREIGN KEY (или ограничений на значения столбцов), и безопасности, когда, например, недопустимы какие-либо изменения в данных. Триггеры бывают нескольких типов: DML, DDL и LOGON. DML триггеры могут срабатывать при выполнении (после выполнения или вместо выполнения) команд INSERT, UPDATE и DELETE для таблиц или представлений. Этот тип триггеров присутствовал и в MS SQL Server 2000. DDL триггеры могут срабатывать при выполнении после выполнения команд CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS и некоторых системных процедур. LOGON триггеры срабатывают после установки соединения с MS SQL Server. DDL и LOGON триггеры можно использовать только в MS SQL Server 2005

Рассмотрим команды разрешения | запрещения срабатывания триггеров.

{ENABLE | DISABLE} TRIGGER

{[пространство_имен.]имя_триггера1[,[пространство_имен.]имя_триггера1…] | ALL}

ON {имя_объекта | DATABASE | ALL SERVER} [;]

причем:

1. Пространство_имен можно указать только для DML триггеров.

2. Ключевое слово ALL показывает, что все триггеры указанного типа (тип указывается после ключевого слова ON) будут разрешены или запрещены для выполнения.

3. После ключевого слова ON можно указать либо имя_таблицы|имя_представления для DML триггеров, либо ключевое слово DATABASE для DDL триггеров уровня БД, либо ключевое слово ALL SERVER для DDL триггеров уровня сервера или для LOGON триггеров.

4. Команда DISABLE TRIGGER, не может использоваться в теле триггера, состояние которого она должна изменить, если в нем происходит выполнение действия, приводящего к повторной активации триггера в явном виде (без использования команды EXECUTE или процедуры sp_executesql). Следует также учитывать, что команды {ENABLE | DISABLE} TRIGGER, должны быть первыми в пакете команд (находиться сразу после begin или go).

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

ALTER TABLE [ имя_БД.[пространство_имен]. | пространство_имен.] имя_таблицы

{ENABLE | DISABLE } TRIGGER

{ALL | имя_триггера1[, имя_триггера2…]

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

Команда ALTER TABLE имя_таблицы DISABLE TRIGGER {имя_триггера | ALL} и ALTER TABLE имя_таблицы ENABLE TRIGGER {имя_триггера | ALL} не могут одновременно использоваться в теле триггера, состояние которого они изменяют. Кроме этого команда ALTER TABLE имя_таблицы DISABLE TRIGGER {имя_триггера | ALL} не может быть применена в таких случаях, когда в теле DML триггера, отключение срабатывания которого она должна обеспечить, происходит выполнение действия, приводящего к повторной активации триггера в явном виде (без использования команды EXECUTE или процедуры sp_executesql).

18. Представления. Их назначение. Команда создания / изменения представления, ее синтаксис (подробное описание). Команда удаления представления. Особенности при модификации данных при работе с представлениями. Примеры.

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

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

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

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

Синтаксис

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]

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

AS select_statement

[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::=

{ [ ENCRYPTION ]

    [ SCHEMABINDING ]

    [ VIEW_METADATA ]     }

Обновляемые представления

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

• Любые изменения, в том числе инструкции UPDATE, INSERT и DELETE, должны ссылаться на столбцы только одной базовой таблицы.

• Изменяемые в представлении столбцы должны непосредственно ссылаться на данные столбцов базовой таблицы. Столбцы нельзя сформировать каким-либо другим образом, в том числе:

  • при помощи агрегатной функции: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR и VARP;

  • на основе вычисления. Столбец нельзя вычислить по выражению, включающему другие столбцы. Столбцы, сформированные при помощи операторов UNION, UNION ALL, CROSSJOIN, EXCEPT и INTERSECT, считаются вычисляемыми и также не являются обновляемыми.

• Предложения GROUP BY, HAVING и DISTINCT не влияют на изменяемые столбцы.

• Предложение TOP не используется нигде в инструкции select_statement представления вместе с предложением WITH CHECK OPTION.

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

Если вышеуказанные ограничения не позволяют изменить данные через представление напрямую, рассмотрите следующие варианты.

• Триггеры INSTEAD OF

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

• Секционированные представления

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

19. Хранимые процедуры. Их назначение. Создание / изменение хранимых процедур (подробное описание синтаксиса команд). Ограничения на команды, содержащиеся в теле хранимой процедуры. Команды удаления хранимой процедуры. Синтаксис команды запуска хранимой процедуры (подробно). Примеры.

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

sp_executesql [ @statement = ] statement

[

    { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }

     { , [ @param1 = ] 'value1' [ ,...n ] }

]

[ @statement = ] statement - Строка в Юникоде, содержащая инструкцию или пакет Transact-SQL.

[ @params = ] N'@parameter_namedata_type [ ,... n ] ' - Строка, содержащая определения всех параметров, внедренных в stmt. Строка должна представлять собой константу в Юникоде либо переменную в этом же формате. Каждое определение параметра состоит из имени параметра и типа данных. n — заполнитель, означающий определения дополнительных параметров. Каждый параметр, указанный в аргументе statement, должен быть определен в аргументе @params. Если инструкция или пакет инструкций языка Transact-SQL в аргументе stmt не содержат параметров, @params может отсутствовать. Этот аргумент по умолчанию принимает значение NULL.

[ @param1 = ] 'value1' - Значение для первого параметра, определенного в строке параметров. Это значение может быть константой или переменной в Юникоде. Каждому параметру, указанному в stmt, должно соответствовать значение. Если инструкция или пакет инструкций Transact-SQL в stmt не содержат параметров, список значений может отсутствовать.

[ OUT | OUTPUT ] - Показывает, что параметр процедуры является выходным. Параметры типов text, ntext и image могут быть выходными, если процедура не является процедурой CLR. Выходным параметром с ключевым словом OUTPUT может быть заполнитель курсора, если процедура не является процедурой CLR.

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

В следующем примере создается и выполняется простая инструкция SELECT, содержащая внедренный параметр с именем @level.

EXECUTE sp_executesql

N'SELECT * FROM AdventureWorks2008R2.HumanResources.Employee

WHERE BusinessEntityID = @level',

N'@level tinyint',

@level = 109;

Относительно пакетов инструкций, области имен и контекста базы данных процедура sp_executesql ведет себя аналогично инструкции EXECUTE. Инструкция или пакет инструкций Transact-SQL в параметре stmt процедуры sp_executesql не компилируются до выполнения инструкции sp_executesql. Содержимое stmt затем компилируется и выполняется в качестве отдельного плана выполнения, не зависящего от плана выполнения пакета, вызвавшего процедуру sp_executesql. Пакет, содержащийся в процедуре sp_executesql, не может ссылаться на переменные, объявленные в пакете, вызвавшем sp_executesql. Локальные курсоры или переменные в пакете sp_executesql недоступны пакету, вызвавшему sp_executesql. Изменения в контексте базы данных длятся только до завершения выполнения инструкции sp_executesql.

Процедура sp_executesql может использоваться вместо хранимых процедур для многократного выполнения инструкций Transact-SQL, где единственные различия между инструкциями — значения параметров. Так как инструкция Transact-SQL сама остается неизменной и меняются только значения параметров, оптимизатор запросов SQL Server, вероятнее всего, повторно использует план выполнения, сформированный перед первым выполнением.