Вопрос : Зачем нужны триггеры, и как ими пользоваться?
Ответ: 1. Зачем нужны триггеры.
Повторю неоднократно слышимую мной фразу - триггеры нужны для поддержания целостности базы данных.
Триггер - это блок кода, который выполняется при попытке внесения изменений в таблицу (или представление), для которой он определен. И в нем может быть какой угодно T-SQL код. Обычно в нем пишут код для проверки заполнения полей, связей с другими таблицами. Есть триггеры для протоколирования изменений в таблицах, т.е. при изменении данных в служебную таблицу записываются старые и новые данные. И т.д.
2. Как пользоваться триггерами.
Несколько замечаний:
При внесении изменений в таблицу, триггеры выполняются в последнюю очередь, после проверки всякого рода связей и ограничений.
Триггер может быть привязан только к одной таблице.
У одной таблицы может быть много триггеров разных типов.
Триггеры могут быть на добавление, изменение и удаление (insert, update, delete) в любых вариациях.
Триггер вызывается на общую команду изменения данных в таблице, т.е. при вызове update для нескольких записей, он не будет выполняться для каждой записи отдельно, а всего один раз.
Если в триггере изменяются данные по таблице, к которой привязан триггер, то вся группа триггеров по этой таблице выполняется повторно за исключением этого триггера (если в опциях сервера стоит метка "allow nested triggers").
Писать триггеры надо так, чтобы не была важна последовательность их вызова.
В теле триггера можно использовать служебные таблицы deleted и inserted.
При выполнении INSERT таблица deleted будет пустая, а в inserted будут содержаться вставленные записи.
При выполнении DELETE таблица inserted будет пустая, а в deleted будут содержаться удаленные записи.
При выполнении UPDATE в deleted содержаться записи до изменения, а в inserted после изменения.
Для примеров будем использовать таблицы, созданные с помощью следующего скрипта:
CREATE TABLE Master1
(
Master1ID int IDENTITY (1,1) NOT NULL,
Name varchar(200),
CONSTRAINT PK_Master1 PRIMARY KEY CLUSTERED
(Master1ID)
)
insert Master1 (Name) values('Иванов')
insert Master1 (Name) values('Петров')
insert Master1 (Name) values('Сидоров')
GO
Создадим триггер на все виды операций и поэкспериментируем с ним:
CREATE TRIGGER TriggerMaster1 ON [Master1]
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
select 'inserted' as Kind, * from inserted
union all
select 'deleted' as Kind, * from deleted
print 'Выход из триггера'
GO
Несколько изменений в таблице:
insert Master1 (Name)
select 'Москвитин'
union
select 'Румянцев'
GO
update Master1 set Name='Кошкин' where Master1ID in (3,6,7)
GO
Обратите внимание на SET NOCOUNT ON в начале тела триггера. Это нужно для того, чтобы команды из тела триггера не возвращали информацию о количестве обработанных строк, и не перебивали тем самым информацию о количестве измененных строк.
В триггере можно использовать функции UPDATE и COLUMNS_UPDATED для определения полей, в которых изменяются данные. Даже если данные до изменения и после изменения были одни и те же, эти функции покажут, что данные в поле были изменены. Например, функция UPDATE(Field) возвратит True в запросе:
UPDATE Table SET Field=Field
В SQL Server 2000 появилась возможность определять триггеры для VIEW.
Еще в SQL Server 2000 появился новый тип триггеров INSTEAD OF. Данный тип триггеров предназначен в первую очередь для представлений (VIEW). Создадим триггер INSTEAD OF INSERT и внесем изменения в таблицу:
CREATE TRIGGER TriggerInsteadMaster1 ON [Master1]
INSTEAD OF INSERT
AS
SET NOCOUNT ON
print 'Триггер INSTEAD OF'
GO
insert Master1 (Name) values ('Фамилия')
GO
select * from Master1
GO
При выполнении вставки в таблицу записей никаких изменений в таблице не происходит. Хотя и выдается сообщение о том, что строка добавлена. В таком типе триггеров необходимо добавлять код на изменение данных в таблице. Вот примерная реализация:
ALTER TRIGGER TriggerInsteadMaster1 ON [Master1]
INSTEAD OF INSERT
AS
SET NOCOUNT ON
INSERT INTO Master1 (Name)
SELECT Name
FROM inserted
print 'Триггер INSTEAD OF'
GO
insert Master1 (Name)
select 'Васечкин'
union
select 'Петров'
GO
select * from Master1
GO
Результат:
Kind Master1ID Name
-------- ----------- ------------------------------
inserted 6 Васечкин
inserted 7 Петров
(2 row(s) affected)
Выход из триггера
Триггер INSTEAD OF
Master1ID Name
----------- ------------------------------
1 Иванов
2 Петров
3 Кошкин
6 Васечкин
7 Петров
(5 row(s) affected)
При работе только с таблицами можно вполне обходиться без этого типа триггеров, но иногда с их использованием T-SQL код упрощается.
На этом закончим. Удалить созданные объекты можно с помощью следующего кода:
DROP TRIGGER TriggerInsteadMaster1
DROP TRIGGER TriggerMaster1
DROP TABLE Master1
GO
Зачем нужны триггеры, и как ими пользоваться?
1. Зачем нужны триггеры.
Повторю неоднократно слышимую мной фразу - триггеры нужны для поддержания целостности базы данных.
Триггер - это блок кода, который выполняется при попытке внесения изменений в таблицу (или представление), для которой он определен. И в нем может быть какой угодно T-SQL код. Обычно в нем пишут код для проверки заполнения полей, связей с другими таблицами. Есть триггеры для протоколирования изменений в таблицах, т.е. при изменении данных в служебную таблицу записываются старые и новые данные. И т.д.
2. Как пользоваться триггерами.
Несколько замечаний:
При внесении изменений в таблицу, триггеры выполняются в последнюю очередь, после проверки всякого рода связей и ограничений.
Триггер может быть привязан только к одной таблице.
У одной таблицы может быть много триггеров разных типов.
Триггеры могут быть на добавление, изменение и удаление (insert, update, delete) в любых вариациях.
Триггер вызывается на общую команду изменения данных в таблице, т.е. при вызове update для нескольких записей, он не будет выполняться для каждой записи отдельно, а всего один раз.
Если в триггере изменяются данные по таблице, к которой привязан триггер, то вся группа триггеров по этой таблице выполняется повторно за исключением этого триггера (если в опциях сервера стоит метка "allow nested triggers").
Писать триггеры надо так, чтобы не была важна последовательность их вызова.
В теле триггера можно использовать служебные таблицы deleted и inserted.
При выполнении INSERT таблица deleted будет пустая, а в inserted будут содержаться вставленные записи.
При выполнении DELETE таблица inserted будет пустая, а в deleted будут содержаться удаленные записи.
При выполнении UPDATE в deleted содержаться записи до изменения, а в inserted после изменения.
Для примеров будем использовать таблицу, созданную с помощью следующего скрипта:
CREATE TABLE Master1
(
Master1ID int IDENTITY (1,1) NOT NULL,
Name varchar(200),
CONSTRAINT PK_Master1 PRIMARY KEY CLUSTERED
(Master1ID)
)
insert Master1 (Name) values('Иванов')
insert Master1 (Name) values('Петров')
insert Master1 (Name) values('Сидоров')
GO
Создадим триггер на все виды операций и поэкспериментируем с ним:
CREATE TRIGGER TriggerMaster1 ON [Master1]
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
select 'inserted' as Kind, * from inserted
union all
select 'deleted' as Kind, * from deleted
print 'Выход из триггера'
GO
Несколько изменений в таблице:
insert Master1 (Name)
select 'Москвитин'
union
select 'Румянцев'
GO
update Master1 set Name='Кошкин' where Master1ID in (3,6,7)
GO
Обратите внимание на SET NOCOUNT ON в начале тела триггера. Это нужно для того, чтобы команды из тела триггера не возвращали информацию о количестве обработанных строк, и не перебивали тем самым информацию о количестве измененных строк.
В триггере можно использовать функции UPDATE и COLUMNS_UPDATED для определения полей, в которых изменяются данные. Даже если данные до изменения и после изменения были одни и те же, эти функции покажут, что данные в поле были изменены. Например, функция UPDATE(Field) возвратит True при выполнении запроса:
UPDATE Table SET Field=Field
В SQL Server 2000 появилась возможность определять триггеры для VIEW.
Еще в SQL Server 2000 появился новый тип триггеров INSTEAD OF. Данный тип триггеров предназначен в первую очередь для представлений (VIEW). Создадим триггер INSTEAD OF INSERT и внесем изменения в таблицу:
CREATE TRIGGER TriggerInsteadMaster1 ON [Master1]
INSTEAD OF INSERT
AS
SET NOCOUNT ON
print 'Триггер INSTEAD OF'
GO
insert Master1 (Name) values ('Фамилия')
GO
select * from Master1
GO
При выполнении вставки в таблицу записей никаких изменений в таблице не происходит. Хотя и выдается сообщение о том, что строка добавлена. В таком типе триггеров необходимо добавлять код на изменение данных в таблице. Вот примерная реализация:
ALTER TRIGGER TriggerInsteadMaster1 ON [Master1]
INSTEAD OF INSERT
AS
SET NOCOUNT ON
INSERT INTO Master1 (Name)
SELECT Name
FROM inserted
print 'Триггер INSTEAD OF'
GO
insert Master1 (Name)
select 'Васечкин'
union
select 'Петров'
GO
select * from Master1
GO
Результат:
Kind Master1ID Name
-------- ----------- ------------------------------
inserted 6 Васечкин
inserted 7 Петров
(2 row(s) affected)
Выход из триггера
Триггер INSTEAD OF
Master1ID Name
----------- ------------------------------
1 Иванов
2 Петров
3 Кошкин
6 Васечкин
7 Петров
(5 row(s) affected)
При работе только с таблицами можно вполне обходиться без этого типа триггеров, но иногда с их использованием T-SQL код упрощается.
На этом закончим. Удалить созданные объекты можно с помощью следующего кода:
DROP TRIGGER TriggerInsteadMaster1
DROP TRIGGER TriggerMaster1
DROP TABLE Master1
GO
Надеюсь, Вы нашли эту информацию полезной. Вопросы, предложения и пожелания шлите на адрес sql@likor.ru С уважением, Сергей Кошкин.
