Create Trigger

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

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

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

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

Применение SQL триггеров связано с дополнительными затратами ресурсов сервера на операции добавления (trigger insert), обновления (trigger update) или удаления (trigger delete) данных в таблице.

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

Ссылки по странице :

• ms sql trigger
• postgres trigger
• oracle triggers
• oracle triggers отключение
• Пример триггера ms sql
• Пример триггера postgres
• Пример триггера oracle

CREATE TRIGGER

CREATE TRIGGER trigger_name
  [ BEFORE | AFTER ] [INSERT | UPDATE | DELETE]
    ON [schema_name.]table_name
	[REFERENCING <список_псевдонимов>]
        [FOR EACH { ROW | STATEMENT}]
  begin
    <trigger_body>
  end;

Момент запуска триггера определяется ключевыми словами BEFORE (триггер запускается перед выполнением связанного с ним событием; например, до добавления записи) или AFTER (после события). Если триггер вызывается до события, он может внести изменения в модифицируемую событием запись, если событие — не удаление записи. Некоторые СУБД накладывают ограничения на операторы, которые могут быть использованы в триггере (например, может быть запрещено вносить изменения в таблицу, с которой «связан» триггер, и т.п.).

Триггеры могут быть подключены не к таблице, а к представлению VIEW. В этом случае с их помощью реализуется механизм «обновляемого представления». При подключении триггера к представлению ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит.

Триггер может быть вызван для каждой строки (FOR EACH ROW), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT).

Обозначение <список_псевдонимов> относится к таким компонентам, как старая или новая строка (OLD / NEW) либо старая или новая таблица (OLD TABLE / NEW TABLE). Cтарые значения не применимы для событий вставки, а новые – для событий удаления.

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

Некорректно написанные триггеры могут привести к серьезным проблемам, связанным с появлением блокировок. Триггеры способны длительное время блокировать ресурсы, поэтому следует обратить особое внимание на сведение к минимуму конфликтов доступа.

MS SQL trigger

Синтаксис создания триггера в СУБД MS SQL имеет следующий вид :

CREATE TRIGGER [schema_name.]trigger_name
  ON [table_name | view_name]
{WITH ENCRYPTION}
  [FOR | AFTER | INSTEAD OF] [[DELETE] [,] [INSERT] [,] [UPDATE]]
  [ WITH APPEND ]
  [ NOT FOR REPLICATION ]
AS
{
  sql_statement
}

schema_name

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

trigger_name

Наименование триггера. Аргумент trigger_name должен соответствовать правилам для идентификаторов — за исключением того, что trigger_name не может начинаться с символов # или ##.

table_name | view_name

Таблица или представление, к которым подключен триггер.

Пример ms sql trigger

Для реализации триггера будут созданы две таблицы : test_table, test_log. К таблице test_table будет подключен триггер. При обновлении записей в таблице test_table триггер будет регистрировать в таблице test_log результаты изменений. Т.е. триггер будет вызываться по событию update.

Тестовая таблица test_table :

create table dbo.test_table (
    id       int not null,
    field1   varchar(255) null,
    field2   varchar(255) null,
  constraint pkTestTableID primary key (id)
);

Таблица журналирования test_log :

create table dbo.test_log (
    id           bigint identity(1,1) not null,
    table_name   varchar(50) not null,
    oper         varchar(15) not null,
    record_old   xml null,
    record_new   xml null,
    data         datetime null,
    constraint pkTestLogID primary key (id)
);

Триггер обновления данных :

-- trigger update
create trigger dbo.trg_test_table_update
  on dbo.test_table for UPDATE
as
  begin
  set nocount on
  -- переменные для хранения старых и новых данных
  declare @record_new xml;
  declare @record_old xml;
  -- в таблице deleted хранятся старые/удаленные данные
  set @record_old = (SELECT * FROM deleted FOR XML RAW, TYPE);
  -- в таблице inserted хранятся измененные (только что созданные) данные
  set @record_new = (SELECT * FROM inserted FOR XML RAW, TYPE);

  if (@record_new is not null) and (@record_old is not null)
      begin
	   insert into dbo.test_log (table_name, oper, record_old, record_new, data)
	      values ('test_table', 'update', @record_old, @record_new, GETDATE())
      end;
end;

Добавим несколько строк в тестовую таблицу, которые будем обновлять для тестирование триггера :

insert into dbo.test_table (id, field1, field2) values (1, 'Кофе', 'Nescafe');
insert into dbo.test_table (id, field1, field2) values (2, 'Чай' , 'Greenfield');

Проверяем работу триггера обновлением строк :

update dbo.test_table set field1 = 'Сахар', field2 = 'Рафинад' where id = 1;
update dbo.test_table set field1 = 'Хлеб', field2 = 'Бородинский' where id = 2;

Проверяем таблицу журналирования test_log. Результат должен выглядеть так, как это представлено на скриншоте :

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

PostgreSQL trigger

Синтаксис создания триггера

CREATE TRIGGER [schema_name.]trigger_name
   [BEFORE | AFTER ] [ событие [ OR событие ]] 
   ON table_name FOR EACH { ROW |  STATEMENT }
    EXECUTE PROCEDURE function_name ( аргументы )

CREATE TRIGGER

В аргументе указывается наименование создаваемого триггера. При необходимости может быть указано наименование схемы.

{ BEFORE | AFTER }

Ключевое слово BEFORE означает, что trigger before и функция должна выполняться перед выполнением соответствующего события. Ключевое слово AFTER означает, что trigger after и функция вызывается после завершения операции, приводящей в действие триггер.

{ событие [ OR событие ... ] }

В PostgreSQL поддерживаются следующие события [INSERT | DELETE | UPDATE]. При перечислении нескольких событий в качестве разделителя используется ключевое слово OR.

ON table_name

Наименование таблицы, модификация которой приводит к срабатыванию триггера.

FOR EACH { ROW | STATEMENT }

Ключевое слово, следующее за конструкцией FOR EACH и определяющее количество вызовов функции при наступлении указанного события. Использование ROW означает, что функция вызывается для каждой модифицируемой записи. Если функция должна вызываться всего один раз для всей команды, используется ключевое слово STATEMENT.

EXECUTE PROCEDURE function_name

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

Синтаксис определения триггерной функции

CREATE FUNCTION function_name () 
  RETURNS trigger AS
DECLARE
  -- объявления переменных
BEGIN
  -- тело триггерной функции
END;
LANGUAGE  plpgsql;

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

К отдельным полям записи NEW и OLD в триггерных процедурах обращаются следующим образом: NEW.names, OLD.rg.

Пример postgresql trigger

В примере реализована простая система логирования пользователей. Она следит за таблицей пользователей и все изменения регистрирует в таблице журналирования. Для примера будем создавать упрощенные таблицы.

Таблица пользователей :

CREATE TABLE "public".users (
  id    int not null,
  name  varchar (64),
  constraint pkUsersID primary key (id)
);

Таблица протоколирования

CREATE TABLE "public".logs (
  text  varchar(256),
  data  timestamp without time zone
);

Триггерная функция

CREATE OR REPLACE FUNCTION "public".add_to_log()
   RETURNS TRIGGER
AS $$
DECLARE
    v_action varchar(30);
    v_user   varchar(64);
    v_retstr varchar(256);
BEGIN
    IF TG_OP = 'INSERT' THEN
        v_user = NEW.name;
        v_action := 'Add new user ';
        v_retstr := v_action || v_user;
        INSERT INTO "public".logs(text, data) values (v_retstr, NOW());
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        v_user = NEW.name;
        v_action := 'Update user ';
        v_retstr := v_action || v_user;
        INSERT INTO "public".logs(text, data) values (v_retstr, NOW());
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        v_user = OLD.name;
        v_action := 'Remove user ';
        v_retstr := v_action || v_user;
        INSERT INTO "public".logs(text, data) values (v_retstr, NOW());
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

Триггерная функция без входящих параметров возвращает специальный тип TRIGGER. В функции в разделе DECLARE определены 3-и переменные. В теле функции выполняется проверка значения переменной TG_OP (внутренняя переменная триггера). В зависимости от транзакции определяем переменнаю v_user и формируется строка retstr, которая записывается в таблицу logs.

Переменные NEW и OLD - это собственно строки которые обрабатывает триггер. В случае INSERT переменная NEW будет содержать новую строку, а OLD будет пустая. В случае UPDATE обе переменные будут определены (соответствующими данными), а в случае DELETE переменная NEW будет пустая, OLD содержать удаляемую строку.

Сам триггер описывается на PL/pgSQL как :

-- trigger insert & trigger update & trigger delete
CREATE TRIGGER trg_user
 AFTER INSERT OR UPDATE OR DELETE 
   ON "public".users FOR EACH ROW 
     EXECUTE PROCEDURE add_to_log ();

Триггер trg_user будет выполняться после выполнения транзакций INSERT, UPDATE, DELETE для каждой строки и вызывать функцию add_to_log(). Теперь любые действия с таблицей users будут протоколироваться.

Следующие скрипты позволяют проверить работоспособность триггера :

-- Добавление записи в таблицу пользователей
insert into users (id, name) values (1, 'Киса Воробьянинов');

-- Обновление записи в таблице пользователей
update users set name = 'Остап Бендер' where id = 1

-- Чтение пользователей
select * from users

-- Чтение журнала протоколирования. Должно быть 2 записи
select * from logs

Oracle triggers

Синтаксис CREATE TRIGGER в Oracle имеет следующий вид :

-- trigger before
CREATE TRIGGER trigger_name 
  BEFORE DELETE OR INSERT OR UPDATE 
    ON table_name
    REFERENCING <список_псевдонимов>  
    FOR EACH ROW 
      WHEN (new.field_name > 0) 
DECLARE 
  -- переменные, константы, курсоры и т.п. 
BEGIN 
  -- блок PL/SQL 
END;

WHEN

В тексте создания триггера может быть включено необязательное ограничение триггера, путем определения булевского выражения SQL в фразе WHEN. Выражение в фразе WHEN проверяется для каждой строки, затрагиваемой триггером. Если результат выражения ИСТИНА, то тело триггера исполняется. Если выражение ЛОЖЬ или NULL, то тело триггера не исполняется. Выражение в фразе WHEN должно быть выражением SQL, но не выражением PL/SQL, и не может включать подзапрос.

REFERENCING

Опция REFERENCING может использоваться в теле триггера для того, чтобы избежать конфликтов между корреляционными именами и именами таблиц, в случае, если таблица имеет имя "OLD" или "NEW". Такая ситуация редка и эта опция почти никогда не применяется.

В качестве примера можно рассмотреть таблицу с именем new. Следующее определение CREATE TRIGGER показывает триггер, ассоциированный с таблицей new, который использует опцию REFERENCING, чтобы избежать конфликтов между корреляционными именами и именем таблицы:

-- trigger before
CREATE TRIGGER trg_dummy
   BEFORE UPDATE ON new
     REFERENCING new AS newest
     FOR EACH ROW
BEGIN
  :newest.field2 := TO_CHAR (:newest.field1);
END;

Оператор new переименован в newest с помощью опции REFERENCING, а затем использован в теле триггера.

Условные предикаты

Если триггер может быть вызван на исполнение более чем одним типом предложения DML (например, "INSERT OR DELETE OR UPDATE"), то в теле триггера можно использовать операторы INSERTING, DELETING и UPDATING, для выполнения различных участков кода в зависимости от условия. В коде внутри тела триггера вы можете использовать следующие условия :

IF INSERTING THEN . . . END IF;
IF UPDATING THEN . . . END IF;

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

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

IF UPDATING ('SAL') THEN 
  . . .
END IF;

Oracle triggers отключение, включение

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

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

Триггер по умолчанию включается в момент его создания. Чтобы отключить триггер, необходимо использовать команду ALTER TRIGGER с опцией DISABLE. Чтобы включить триггер, используйте команду ALTER TRIGGER с опцией ENABLE. Можно одновременно отключить все триггеры, ассоциированные с таблицей, с помощью команды ALTER TABLE с опцией DISABLE ALL TRIGGERS.

-- отключение триггера 
ALTER TRIGGER TRG_Orders_INS DISABLE;

-- подключение триггера 
ALTER TRIGGER TRG_Orders_INS ENABLE;

-- отключение всех триггеров таблицы 
ALTER TABLE Orders
  DISABLE ALL TRIGGERS;

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

Пример oracle trigger

-- генератор последовательностей
CREATE SEQUENCE seqID;

-- таблица пользователей
CREATE TABLE users (
  id     int PRIMARY KEY not null,
  name   varchar(50),
  phone  varchar(15),
  dt     date
);

-- trigger insert определяет идентификатор записи
CREATE OR REPLACE TRIGGER trgAutonumber
  BEFORE INSERT ON users   -- trigger before
     FOR EACH ROW
BEGIN
  select seqID.NEXTVAL
     into :new.id from dual;
END;

-- trigger insert определяет дату записи
CREATE OR REPLACE TRIGGER trgDate
  BEFORE INSERT ON users  trigger before
     FOR EACH ROW
BEGIN
  if :old.dt is null then
     :new.dt := current_date;
  end if;  
END trgDate;

В следующем примере триггер trgDepartmentst_del_cascade выполняет каскадное удаление записей TRIGGER DELETE CASCADE. Триггер, подключенный к таблице departments, реализует ссылочное действие DELETE CASCADE по первичному ключу таблицы deptID:

-- trigger after
CREATE OR REPLACE TRIGGER trgDepartmentst_del_cascade
  AFTER DELETE ON departments
   FOR EACH ROW
BEGIN
/* После удаления строки из таблицы Departments удалить из таблицы
   Employees все строки, имеющие такое же значение deptID. */
  DELETE FROM employees
    WHERE employees.deptID = :old.deptID;
END;

Примечание: обычно код для DELETE CASCADE объединяют вместе с кодом для UPDATE SET NULL или UPDATE SET DEFAULT, чтобы учесть как обновления, так и удаления в одном триггере.