Sql server триггеры примеры. Триггеры: создание и применение. Триггеры INSTEAD OF

НАЗВАНИЕ
rm - удаление файлов

СИНТАКСИС

Rm [-f] [-i] файл... rm -r [-f] [-i] каталог... [файл...]

ОПИСАНИЕ
Команда rm служит для удаления указанных имен файлов из каталога. Если заданное имя было последней ссылкой на файл, то файл уничтожается. Для удаления пользователь должен обладать правом записи в каталог; иметь право на чтение или запись файла не обязательно. Следует заметить, что при удалении файла в Linux, он удаляется навсегда. Здесь нет возможностей вроде "мусорной корзины" в windows 95/98/NT или команды undelete в DOS. Так что, если файл удален, то он удален!

Если нет права на запись в файл и стандартный ввод назначен на терминал, то выдается (в восьмеричном виде) режим доступа к файлу и запрашивается подтверждение; если оно начинается с буквы y, то файл удаляется, иначе - нет. Если стандартный ввод назначен не на терминал, команда rm ведет себя так же, как при наличии опции -f.

Допускаются следующие три опции:

ПРИМЕРЫ Опция -i часто используется совместно с -r. По команде:

Rm -ir dirname запрашивается подтверждение: directory dirname: ?

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

ДИАГНОСТИКА
Смысл всех сообщений самоочевиден.

Запрещено удалять файлы. и.., чтобы не нарушить целостность файловой системы. Команда

Rm -r .* приведет к выдаче сообщений об ошибках.

Команда rm пробует удалить файлы, указанные в качестве аргументов командной строки, не являющиеся директориями. Если права доступа не позволяют запись, а устройством ввода (stdin ), является терминал, у пользователю будет запрошено подтверждение удаления. Поддерживаемые опции: -d Попытаться удалить директории, как обычные файлы. -f Не запрашивать разрешение на удаление, вне зависимости от установленных прав доступа. В случае отсутствия файла, информация об этом выведена не будет, кроме того не будет изменен код об удачном завершении операции, для информирования о неудачном завершении. Так-же данная опция отменяет любую, ранее полученную, опцию -i . -i Интерактивный режим с запросом подтверждения удаления каждого файла, вне зависимости от установленных на файл прав доступа и является-ли терминал устройством stdin . Данная опция отменяет, ранее указанную опцию -f . -I Запрос, если удалению подлежит, более 3 файлов, или перед рекурсивным удалением директории. Данная опция обеспечивает почти такой-же уровень защиты от ошибки, как опция -i , при гораздо меньшем количестве запросов подтверждений. -P Перезаписывать файлы перед удалением. Сначала файлы перезаписываются значением 0xff , затем 0x00 , затем 0xff , после чего удаляются. Файлы с несколькими жёсткими ссылками не могут быть перезаписаны, ни удалены без указания опции -f , при попытке удаления, будет выведено предупреждение. Если с данной опцией попробовать удалить файл с правами доступа "только чтение", будет выведено сообщение об ошибке а выполнение команды rm будет прервано. Файл при этом, удален или перезаписан, не будет. -R Попытаться удалить дерево каталогов, корень которого указан аргументом файл . Данная опция автоматически включает опцию -d . Если так-же указана опция -i , будут выводиться запросы на удаление содержимого каждой директории. В случае, если пользователь не подтвердил удаление директории, удаление текущего каталога будет пропущено. -r Аналог опции -R . -v Выводит дополнительную информацию при удалении файлов. -W Попытаться восстановить файлы, переданые в качестве параметров. В данный момент эта опция может быть использована только для восстановления файлов, "замазанных штрихом" (covered by whiteouts ) в файловой системе union (см. undelete(2) ). Команда rm удаляет символические ссылки, но сами файлы, на которые эти ссылки указывают, при этом удалены не будут. При попытке удалить / , . или .. будет выведено сообщение об ошибке. В случае вызова команды rm , как unlink , может быть передан только один аргумент, который не может быть директорией. В данном режиме не могут быть переданы никакие опции, выполняется только unlink(2) с переданным аргументом. rm возвращает 0 в случае удачного удаления всех переданных ей каталогов и файлов. В противном случае, результат >0 . Команда rm использует getopt(3) для анализа переданных ей аргументов, что позволяет ей принимать опцию - ", которая вызовет остановку обработки переданных флагов там, где эта опция встретится. Это позволяет удалять файлы, имена которых начинаются с символа минус (- ). Например: rm -- -filename Того же результата можно добиться, задавая имя файла с указанием абсолютного или относительного пути. Например: rm /home/user/-filename rm ./-filename Если опция -P указывается совместно с опцией -f , то файл будет перезаписан и удалён даже если он имеет жёсткие ссылки. Смотри также chflags(1), rmdir(1), undelete(2), unlink(2), fts(3), getopt(3), symlink(7) Возможные проблемы Опция -P подразумевает, что нижележащая файловая система является файловой системой с фиксированными блоками (fixed-block file system ). UFS является такой системой, а LFS - нет. Кроме этого, перезаписываются только обычные файлы, файлы всех остальных типов - нет.

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

В этой статье мы рассмотрим самые часто используемые команды Linux для работы с файлами и каталогами, после их освоения терминал больше не будет казаться таким непонятным и объемлемым. Вот какие команды будут нас сегодня интересовать:

• ls - список файлов в директории;
• cd - переход между директориями;
• rm - удалить файл;
• rmdir - удалить папку;
• mv - переместить файл;
• cp - скопировать файл;
• mkdir - создать папку;
• ln - создать ссылку;
• chmod - изменить права файла;
• touch - создать пустой файл.

А теперь перейдем к подробному рассмотрению всех этих команд.

Команда ls позволяет вывести список файлов заданной папки, по умолчанию, будет выведен список файлов текущей папки:

Вы можете вывести список файлов из всех подкаталогов рекурсивно, для этого используйте опцию -R:

Чтобы вывести список файлов нужной папки, вы можете передать ее адрес утилите, например, /home:

Чтобы получить больше информации и вывести все имена файлов в виде списка используйте опцию -l:

2. cd - изменить папку

Команда cd позволяет изменить текущую папку на другую. По умолчанию, текущей считается домашняя папка, например, cd Desktop меняет папку на рабочий стол, если вы выполните ее из домашнего каталога:

Вы также можете указать полный путь к папке:

Команда cd .. переходит в папку, которая находится выше на одну в файловой системе:

Вы также можете вернуться в предыдущую рабочую папку:

3. rm - удалить файлы

Команда rm позволяет удалить файл. Будьте очень осторожны с ней, она не будет спрашивать подтверждения:

Например, rm file удалит файл с именем file, который находится в текущей папке. Как и в предыдущих вариантах, вы можете указать полный путь к файлу linux. Например:

rm /usr/share/file

Если вы хотите удалить папку, то нужно использовать опцию -r. Она включает рекурсивное удаление всех файлов и папок на всех уровнях вложенности:

rm -r /home/user/photo/

Будьте осторожны, потому что эта команда удаляет файлы безвозвратно.

4. rmdir - удалить папку

Команда rmdir позволяет удалить пустую папку. Например, команда rmdir directory удалит папку directory, которая находится в текущей папке:

Если вам нужно удалить папку с файлами, то нужно использовать утилиту rm вместе с опцией -r.

5. mv - перемещение файлов

Команда mv перемещает файл в новое место. Она также может использоваться для переименования файлов. Например, mv file newfile переименует файл file в newfile:

Чтобы переместить файл в другую папку нужно указать путь к ней, например, переместим файл file в папку /home/user/tmp/

mv file /home/user/tmp/

6. cp - копирование файлов

Эта cp и mv - это похожие команды linux для работы с файлами. Они работают аналогичным образом, только исходный файл остается на своем месте.

Вы также можете рекурсивно скопировать всю папку с помощью команды cp -r. Эта команда скопирует всю папку вместе со всеми файлами и вложенными папками в новое место. Например, скопируем папку /etc/:

cp -r /etc /etc_back

7. mkdir - создать папку

Команда mkdir позволяет создать новую папку mkdir test создаст папку в текущем каталоге:

Если нужно создать папку в другом каталоге, укажите полный путь к нему:

mkdir /home/user/test

8. ln - создание ссылок

Утилита ln позволяет создавать жесткие и символические ссылки на файлы или папки. Для создания символической ссылки используется опция -s. Например, вы создадим ссылку на папку с загрузками:

ln -s /home/user/Downloads/ /home/user/test/

ln /home/user/Downloads/ /home/user/test/

9. chmod - изменить права

chmod позволяет изменить права доступа к файлам. Например, chmod +x script.sh добавляет флаг исполняемости к файлу script.sh:

chmod +x script.sh

Чтобы убрать флаг исполняемый используйте опцию -x:

chmod -x script.sh

10. touch - создать файл

Команда touch создает пустой файл. Например, touch file создаст пустой файл в текущей папке с именем file:

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

11. mc

Работа с файлами Linux через терминал может выполняться не только с помощью обычных консольных команд, каждая из которых выполняет только определенное действие. Вы можете использовать полнофункциональный файловый менеджер с псевдографическим интерфейсом на основе ncurses. Midnight Commander - это файловый менеджер, который можно использовать прямо в терминале. Для выполните:

sudo apt install mc

После завершения установки достаточно выполнить команду mc:

Используйте клавиши со стрелками для перемещения по файлах, Tab для перехода на другую панель, Alt+1 для просмотра справки и Alt+2 для вывода меню:

Если терминальное окружение поддерживает мышь, то вы можете ее использовать в Midnight Commander.

Выводы

В этой статье мы рассмотрели как выполняется работа с файлами и каталогами в linux, с помощью терминала. Когда вы освоите все эти команды, вы сможете сделать большинство основных действий в терминале. А как часто вы пользуетесь терминалом? Используете его для работы с файлами? Напишите в комментариях!

Предшествующий синтаксис относится только к триггерам DML. Триггеры DDL имеют несколько иную форму синтаксиса, которая будет показана позже.

Здесь в параметре schema_name указывается имя схемы, к которой принадлежит триггер, а в параметре trigger_name - имя триггера. В параметре table_name задается имя таблицы, для которой создается триггер. (Также поддерживаются триггеры для представлений, на что указывает наличие параметра view_name.)

Также можно задать тип триггера с помощью двух дополнительных параметров: AFTER и INSTEAD OF. (Параметр FOR является синонимом параметра AFTER.) Триггеры типа AFTER вызываются после выполнения действия, запускающего триггер, а триггеры типа INSTEAD OF выполняются вместо действия, запускающего триггер. Триггеры AFTER можно создавать только для таблиц, а триггеры INSTEAD OF - как для таблиц, так и для представлений.

Параметры INSERT, UPDATE и DELETE задают действие триггера. Под действием триггера имеется в виду инструкция Transact-SQL, которая запускает триггер. Допускается любая комбинация этих трех инструкций. Инструкция DELETE не разрешается, если используется параметр IF UPDATE.

Как можно видеть в синтаксисе инструкции CREATE TRIGGER, действие (или действия) триггера указывается в спецификации AS sql_statement.

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

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

Изменение структуры триггера

Язык Transact-SQL также поддерживает инструкцию ALTER TRIGGER , которая модифицирует структуру триггера. Эта инструкция обычно применяется для изменения тела триггера. Все предложения и параметры инструкции ALTER TRIGGER имеют такое же значение, как и одноименные предложения и параметры инструкции CREATE TRIGGER.

Для удаления триггеров в текущей базе данных применяется инструкция DROP TRIGGER .

Использование виртуальных таблиц deleted и inserted

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

deleted - содержит копии строк, удаленных из таблицы;

inserted - содержит копии строк, вставленных в таблицу.

Структура этих таблиц эквивалентна структуре таблицы, для которой определен триггер.

Таблица deleted используется в том случае, если в инструкции CREATE TRIGGER указывается предложение DELETE или UPDATE, а если в этой инструкции указывается предложение INSERT или UPDATE, то используется таблица inserted . Это означает, что для каждой инструкции DELETE, выполненной в действии триггера, создается таблица deleted. Подобным образом для каждой инструкции INSERT, выполненной в действии триггера, создается таблица inserted.

Инструкция UPDATE рассматривается, как инструкция DELETE, за которой следует инструкция INSERT. Поэтому для каждой инструкции UPDATE, выполненной в действии триггера, создается как таблица deleted, так и таблица inserted (в указанной последовательности).

Таблицы inserted и deleted реализуются, используя управление версиями строк, которое рассматривалось в предыдущей статье. Когда для таблицы с соответствующими триггерами выполняется инструкция DML (INSERT, UPDATE или DELETE), для всех изменений в этой таблице всегда создаются версии строк. Когда триггеру требуется информация из таблицы deleted, он обращается к данным в хранилище версий строк. В случае таблицы inserted, триггер обращается к самым последним версиям строк.

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

Области применения DML-триггеров

Такие триггеры применяются для решения разнообразных задач. В этом разделе мы рассмотрим несколько областей применения триггеров DML, в частности триггеров AFTER и INSTEAD OF.

Триггеры AFTER

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

создания журнала логов действий в таблицах базы данных;

реализации бизнес-правил;

принудительного обеспечения ссылочной целостности.

Создание журнала логов

В SQL Server можно выполнять отслеживание изменения данных, используя систему перехвата изменения данных CDC (change data capture). Эту задачу можно также решить с помощью триггеров DML. В примере ниже показывается, как с помощью триггеров можно создать журнал логов действий в таблицах базы данных:

USE SampleDb; /* Таблица AuditBudget используется в качестве журнала логов действий в таблице Project */ GO CREATE TABLE AuditBudget (ProjectNumber CHAR(4) NULL, UserName CHAR(16) NULL, Date DATETIME NULL, BudgetOld FLOAT NULL, BudgetNew FLOAT NULL); GO CREATE TRIGGER trigger_ModifyBudget ON Project AFTER UPDATE AS IF UPDATE(budget) BEGIN DECLARE @budgetOld FLOAT DECLARE @budgetNew FLOAT DECLARE @projectNumber CHAR(4) SELECT @budgetOld = (SELECT Budget FROM deleted) SELECT @budgetNew = (SELECT Budget FROM inserted) SELECT @projectNumber = (SELECT Number FROM deleted) INSERT INTO AuditBudget VALUES (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew) END

В этом примере создается таблица AuditBudget, в которой сохраняются все изменения столбца Budget таблицы Project. Изменения этого столбца будут записываться в эту таблицу посредством триггера trigger_ModifyBudget.

Этот триггер активируется для каждого изменения столбца Budget с помощью инструкции UPDATE. При выполнении этого триггера значения строк таблиц deleted и inserted присваиваются соответствующим переменным @budgetOld, @budgetNew и @projectNumber. Эти присвоенные значения, совместно с именем пользователя и текущей датой, будут затем вставлены в таблицу AuditBudget.

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

то содержимое таблицы AuditBudget будет таким:

Реализация бизнес-правил

С помощью триггеров можно создавать бизнес-правила для приложений. Создание такого триггера показано в примере ниже:

USE SampleDb; -- Триггер trigger_TotalBudget является примером использования -- триггера для реализации бизнес-правила GO CREATE TRIGGER trigger_TotalBudget ON Project AFTER UPDATE AS IF UPDATE (Budget) BEGIN DECLARE @sum_old1 FLOAT DECLARE @sum_old2 FLOAT DECLARE @sum_new FLOAT SELECT @sum_new = (SELECT SUM(Budget) FROM inserted) SELECT @sum_old1 = (SELECT SUM(p.Budget) FROM project p WHERE p.Number NOT IN (SELECT d.Number FROM deleted d)) SELECT @sum_old2 = (SELECT SUM(Budget) FROM deleted) IF @sum_new > (@sum_old1 + @sum_old2) * 1.5 BEGIN PRINT "Бюджет не изменился" ROLLBACK TRANSACTION END ELSE PRINT "Изменение бюджета выполнено" END

Здесь создается правило для управления модификацией бюджетов проектов. Триггер trigger_TotalBudget проверяет каждое изменение бюджетов и выполняет только такие инструкции UPDATE, которые увеличивают сумму всех бюджетов не более чем на 50%. В противном случае для инструкции UPDATE выполняется откат посредством инструкции ROLLBACK TRANSACTION.

Принудительное обеспечение ограничений целостности

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

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

В примере ниже показано принудительное обеспечение ссылочной целостности посредством триггеров для таблиц Employee и Works_on:

USE SampleDb; GO CREATE TRIGGER trigger_WorksonIntegrity ON Works_on AFTER INSERT, UPDATE AS IF UPDATE(EmpId) BEGIN IF (SELECT Employee.Id FROM Employee, inserted WHERE Employee.Id = inserted.EmpId) IS NULL BEGIN ROLLBACK TRANSACTION PRINT "Строка не была вставлена/модифицирована" END ELSE PRINT "Строка была вставлена/модифицирована" END

Триггер trigger_WorksonIntegrity в этом примере проверяет ссылочную целостность для таблиц Employee и Works_on. Это означает, что проверяется каждое изменение столбца Id в ссылочной таблице Works_on, и при любом нарушении этого ограничения выполнение этой операции не допускается. (То же самое относится и к вставке в столбец Id новых значений.) Инструкция ROLLBACK TRANSACTION во втором блоке BEGIN выполняет откат инструкции INSERT или UPDATE в случае нарушения ограничения для обеспечения ссылочной целостности.

В этом примере триггер выполняет проверку на проблемы ссылочной целостности первого и второго случая между таблицами Employee и Works_on. А в примере ниже показан триггер, который выполняет проверку на проблемы ссылочной целостности третьего и четвертого случая между этими же таблицами (эти случаи обсуждались в статье "Transact-SQL - создание таблиц"):

USE SampleDb; GO CREATE TRIGGER trigger_RefintWorkson2 ON Employee AFTER DELETE, UPDATE AS IF UPDATE (Id) BEGIN IF (SELECT COUNT(*) FROM Works_on, deleted WHERE Works_on.EmpId = deleted.Id) > 0 BEGIN ROLLBACK TRANSACTION PRINT "Строка не была вставлена/модифицирована" END ELSE PRINT "Строка была вставлена/модифицирована" END

Триггеры INSTEAD OF

Триггер с предложением INSTEAD OF заменяет соответствующее действие, которое запустило его. Этот триггер выполняется после создания соответствующих таблиц inserted и deleted, но перед выполнением проверки ограничений целостности или каких-либо других действий.

Триггеры INSTEAD OF можно создавать как для таблиц, так и для представлений. Когда инструкция Transact-SQL ссылается на представление, для которого определен триггер INSTEAD OF, система баз данных выполняет этот триггер вместо выполнения любых действий с любой таблицей. Данный тип триггера всегда использует информацию в таблицах inserted и deleted, созданных для представления, чтобы создать любые инструкции, требуемые для создания запрошенного события.

Значения столбцов, предоставляемые триггером INSTEAD OF, должны удовлетворять определенным требованиям:

значения не могут задаваться для вычисляемых столбцов;

значения не могут задаваться для столбцов с типом данных timestamp;

значения не могут задаваться для столбцов со свойством IDENTITY, если только параметру IDENTITY_INSERT не присвоено значение ON.

Эти требования действительны только для инструкций INSERT и UPDATE, которые ссылаются на базовые таблицы. Инструкция INSERT, которая ссылается на представления с триггером INSTEAD OF, должна предоставлять значения для всех столбцов этого представления, не допускающих пустые значения NULL. (То же самое относится и к инструкции UPDATE. Инструкция UPDATE, ссылающаяся на представление с триггером INSTEAD OF, должна предоставить значения для всех столбцов представления, которое не допускает пустых значений и на которое осуществляется ссылка в предложении SET.)

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

USE SampleDb; CREATE TABLE Orders (OrderId INT NOT NULL, Price MONEY NOT NULL, Quantity INT NOT NULL, OrderDate DATETIME NOT NULL, Total AS Price * Quantity, ShippedDate AS DATEADD (DAY, 7, orderdate)); GO CREATE VIEW view_AllOrders AS SELECT * FROM Orders; GO CREATE TRIGGER trigger_orders ON view_AllOrders INSTEAD OF INSERT AS BEGIN INSERT INTO Orders SELECT OrderId, Price, Quantity, OrderDate FROM inserted END

В этом примере используется таблица Orders, содержащая два вычисляемых столбца. Представление view_AllOrders содержит все строки этой таблицы. Это представление используется для задания значения в его столбце, которое соотносится с вычисляемым столбцом в базовой таблице, на которой создано представление. Это позволяет использовать триггер INSTEAD OF, который в случае инструкции INSERT заменяется пакетом, который вставляет значения в базовую таблицу посредством представления view_AllOrders. (Инструкция INSERT, обращающаяся непосредственно к базовой таблице, не может задавать значение вычисляемому столбцу.)

Триггеры first и last

Компонент Database Engine позволяет создавать несколько триггеров для каждой таблицы или представления и для каждой операции (INSERT, UPDATE и DELETE) с ними. Кроме этого, можно указать порядок выполнения для нескольких триггеров, определенных для конкретной операции. С помощью системной процедуры sp_settriggerorder можно указать, что один из определенных для таблицы триггеров AFTER будет выполняться первым или последним для каждого обрабатываемого действия. Эта системная процедура имеет параметр @order, которому можно присвоить одно из трех значений:

first - указывает, что триггер является первым триггером AFTER, выполняющимся для модифицирования действия;

last - указывает, что данный триггер является последним триггером AFTER, выполняющимся для инициирования действия;

none - указывает, что для триггера отсутствует какой-либо определенный порядок выполнения. (Это значение обычно используется для того, чтобы выполнить сброс ранее установленного порядка выполнения триггера как первого или последнего.)

Изменение структуры триггера посредством инструкции ALTER TRIGGER отменяет порядок выполнения триггера (первый или последний). Применение системной процедуры sp_settriggerorder показано в примере ниже:

USE SampleDb; EXEC sp_settriggerorder @triggername = "trigger_ModifyBudget", @order = "first", @stmttype="update"

Для таблицы разрешается определить только один первый и только один последний триггер AFTER. Остальные триггеры AFTER выполняются в неопределенном порядке. Узнать порядок выполнения триггера можно с помощью системной процедуры sp_helptrigger или функции OBJECTPROPERTY.

Возвращаемый системной процедурой sp_helptrigger результирующий набор содержит столбец order, в котором указывается порядок выполнения указанного триггера. При вызове функции objectproperty в ее втором параметре указывается значение ExeclsFirstTrigger или ExeclsLastTrigger, а в первом параметре всегда указывается идентификационный номер объекта базы данных. Если указанное во втором параметре свойство имеет значение true, функция возвращает значение 1.

Поскольку триггер INSTEAD OF исполняется перед тем, как выполняются изменения в его таблице, для триггеров этого типа нельзя указать порядок выполнения "первым" или "последним".

Триггеры DDL и области их применения

Ранее мы рассмотрели триггеры DML, которые задают действие, предпринимаемое сервером при изменении таблицы инструкциями INSERT, UPDATE или DELETE. Компонент Database Engine также позволяет определять триггеры для инструкций DDL, таких как CREATE DATABASE, DROP TABLE и ALTER TABLE. Триггеры для инструкций DDL имеют следующий синтаксис:

CREATE TRIGGER trigger_name ON {ALL SERVER | DATABASE } {FOR | AFTER } { event_group | event_type | LOGON} AS {batch | EXTERNAL NAME method_name} Соглашения по синтаксису

Как можно видеть по их синтаксису, триггеры DDL создаются таким же способом, как и триггеры DML. А для изменения и удаления этих триггеров используются те же инструкции ALTER TRIGGER и DROP TRIGGER, что и для триггеров DML. Поэтому в этом разделе рассматриваются только те параметры инструкции CREATE TRIGGER, которые новые для синтаксиса триггеров DDL.

Первым делом при определении триггера DDL нужно указать его область действия. Предложение DATABASE указывает в качестве области действия триггера DDL текущую базу данных, а предложение ALL SERVER - текущий сервер.

После указания области действия триггера DDL нужно в ответ на выполнение одной или нескольких инструкций DDL указать способ запуска триггера. В параметре event_type указывается инструкция DDL, выполнение которой запускает триггер, а в альтернативном параметре event_group указывается группа событий языка Transact-SQL. Триггер DDL запускается после выполнения любого события языка Transact-SQL, указанного в параметре event_group. Ключевое слово LOGON указывает триггер входа.

Кроме сходства триггеров DML и DDL, между ними также есть несколько различий. Основным различием между этими двумя видами триггеров является то, что для триггера DDL можно задать в качестве его области действия всю базу данных или даже весь сервер, а не всего лишь отдельный объект. Кроме этого, триггеры DDL не поддерживают триггеров INSTEAD OF. Как вы, возможно, уже догадались, для триггеров DDL не требуются таблицы inserted и deleted, поскольку эти триггеры не изменяют содержимого таблиц.

В следующих подразделах подробно рассматриваются две формы триггеров DDL: триггеры уровня базы данных и триггеры уровня сервера.

Триггеры DDL уровня базы данных

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

USE SampleDb; GO CREATE TRIGGER trigger_PreventDrop ON DATABASE FOR DROP_TRIGGER AS PRINT "Перед тем, как удалить триггер, вы должны отключить "trigger_PreventDrop"" ROLLBACK

Триггер в этом примере предотвращает удаление любого триггера для базы данных SampleDb любым пользователем. Предложение DATABASE указывает, что триггер trigger_PreventDrop является триггером уровня базы данных. Ключевое слово DROP_TRIGGER указывает предопределенный тип события, запрещающий удаление любого триггера.

Триггеры DDL уровня сервера

Триггеры уровня сервера реагируют на серверные события. Триггер уровня сервера создается посредством использования предложения ALL SERVER в инструкции CREATE TRIGGER. В зависимости от выполняемого триггером действия, существует два разных типа триггеров уровня сервера: обычные триггеры DDL и триггеры входа. Запуск обычных триггеров DDL основан на событиях инструкций DDL, а запуск триггеров входа - на событиях входа.

В примере ниже демонстрируется создание триггера уровня сервера, который является триггером входа:

USE master; GO CREATE LOGIN loginTest WITH PASSWORD = "12345!", CHECK_EXPIRATION = ON; GO GRANT VIEW SERVER STATE TO loginTest; GO CREATE TRIGGER trigger_ConnectionLimit ON ALL SERVER WITH EXECUTE AS "loginTest" FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= "loginTest" AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = "loginTest") > 1 ROLLBACK; END;

Здесь сначала создается имя входа SQL Server loginTest, которое потом используется в триггере уровня сервера. По этой причине, для этого имени входа требуется разрешение VIEW SERVER STATE, которое и предоставляется ему посредством инструкции GRANT. После этого создается триггер trigger_ConnectionLimit. Этот триггер является триггером входа, что указывается ключевым словом LOGON.

С помощью представления sys.dm_exec_sessions выполняется проверка, был ли уже установлен сеанс с использованием имени входа loginTest. Если сеанс уже был установлен, выполняется инструкция ROLLBACK. Таким образом имя входа loginTest может одновременно установить только один сеанс.

Триггеры и среда CLR

Подобно хранимым процедурам и определяемым пользователем функциям, триггеры можно реализовать, используя общеязыковую среду выполнения (CLR - Common Language Runtime). Триггеры в среде CLR создаются в три этапа:

Создается исходный код триггера на языке C# или Visual Basic, который затем компилируется, используя соответствующий компилятор в объектный код.

Объектный код обрабатывается инструкцией CREATE ASSEMBLY, создавая соответствующий выполняемый файл.

Посредством инструкции CREATE TRIGGER создается триггер.

Выполнение всех этих трех этапов создания триггера CLR демонстрируется в последующих примерах. Ниже приводится пример исходного кода программы на языке C# для триггера из первого примера в статье. Прежде чем создавать триггер CLR в последующих примерах, сначала нужно удалить триггер trigger_PreventDrop, а затем удалить триггер trigger_ModifyBudget, используя в обоих случаях инструкцию DROP TRIGGER.

Using System; using System.Data.SqlClient; using Microsoft.SqlServer.Server; public class Triggers { public static void ModifyBudget() { SqlTriggerContext context = SqlContext.TriggerContext; if (context.IsUpdatedColumn(2)) // Столбец Budget { float budget_old; float budget_new; string project_number; SqlConnection conn = new SqlConnection("context connection=true"); conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT Budget FROM DELETED"; budget_old = (float)Convert.ToDouble(cmd.ExecuteScalar()); cmd.CommandText = "SELECT Budget FROM INSERTED"; budget_new = (float)Convert.ToDouble(cmd.ExecuteScalar()); cmd.CommandText = "SELECT Number FROM DELETED"; project_number = Convert.ToString(cmd.ExecuteScalar()); cmd.CommandText = @"INSERT INTO AuditBudget (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew)"; cmd.Parameters.AddWithValue("@projectNumber", project_number); cmd.Parameters.AddWithValue("@budgetOld", budget_old); cmd.Parameters.AddWithValue("@budgetNew", budget_new); cmd.ExecuteNonQuery(); } } }

Пространство имен Microsoft.SQLServer.Server содержит все классы клиентов, которые могут потребоваться программе C#. Классы SqlTriggerContext и SqlFunction являются членами этого пространства имен. Кроме этого, пространство имен System.Data.SqlClient содержит классы SqlConnection и SqlCommand, которые используются для установления соединения и взаимодействия между клиентом и сервером базы данных. Соединение устанавливается, используя строку соединения "context connection = true".

Затем определяется класс Triggers, который применяется для реализации триггеров. Метод ModifyBudget() реализует одноименный триггер. Экземпляр context класса SqlTriggerContext позволяет программе получить доступ к виртуальной таблице, создаваемой при выполнении триггера. В этой таблице сохраняются данные, вызвавшие срабатывание триггера. Метод IsUpdatedColumn() класса SqlTriggerContext позволяет узнать, был ли модифицирован указанный столбец таблицы.

Данная программа содержит два других важных класса: SqlConnection и SqlCommand. Экземпляр класса SqlConnection обычно применяется для установления соединения с базой данных, а экземпляр класса SqlCommand позволяет исполнять SQL-инструкции.

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

USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" WITH PERMISSION_SET = SAFE

Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, на основе которого создается триггер CLR. Предложение WITH PERMISSION_SET в примере указывает, что разрешениям доступа присвоено значение SAFE.

Наконец, в примере ниже посредством инструкции CREATE TRIGGER создается триггер trigger_modify_budget:

USE SampleDb; GO CREATE TRIGGER trigger_modify_budget ON Project AFTER UPDATE AS EXTERNAL NAME CLRStoredProcedures.Triggers.ModifyBudget

Инструкция CREATE TRIGGER в примере отличается от такой же инструкции в примерах ранее тем, что она содержит параметр EXTERNAL NAME . Этот параметр указывает, что код создается средой CLR. Имя в этом параметре состоит из трех частей. В первой части указывается имя соответствующей сборки (CLRStoredProcedures), во второй - имя открытого класса, определенного в примере выше (Triggers), а в третьей указывается имя метода, определенного в этом классе (ModifyBudget).