Создадим "универсальную" триггерную функцию, которая описывает контекст, в котором она вызвана. Контекст передается в различных TG-переменных.
Затем создадим триггеры на различные события, и будем смотреть, какие триггеры вызываются при выполнении операций и в каком порядке.
=> CREATE OR REPLACE FUNCTION describe() RETURNS trigger AS $$ DECLARE rec record; str text := ''; BEGIN IF TG_LEVEL = 'ROW' THEN CASE TG_OP WHEN 'DELETE' THEN rec := OLD; str := OLD::text; WHEN 'UPDATE' THEN rec := NEW; str := OLD || ' -> ' || NEW; WHEN 'INSERT' THEN rec := NEW; str := NEW::text; END CASE; END IF; RAISE NOTICE '% % % %: %', TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, str; RETURN rec; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
Таблица:
=> CREATE TABLE t( id integer PRIMARY KEY, s text );
CREATE TABLE
Триггеры на уровне оператора:
=> CREATE TRIGGER t_before_stmt BEFORE INSERT OR UPDATE OR DELETE ON t FOR EACH STATEMENT EXECUTE PROCEDURE describe();
CREATE TRIGGER
=> CREATE TRIGGER t_after_stmt AFTER INSERT OR UPDATE OR DELETE ON t FOR EACH STATEMENT EXECUTE PROCEDURE describe();
CREATE TRIGGER
И на уровне строк:
=> CREATE TRIGGER t_before_row BEFORE INSERT OR UPDATE OR DELETE ON t FOR EACH ROW EXECUTE PROCEDURE describe();
CREATE TRIGGER
=> CREATE TRIGGER t_after_row AFTER INSERT OR UPDATE OR DELETE ON t FOR EACH ROW EXECUTE PROCEDURE describe();
CREATE TRIGGER
Пробуем вставку:
=> INSERT INTO t VALUES (1,'aaa');
NOTICE: t BEFORE INSERT STATEMENT: NOTICE: t BEFORE INSERT ROW: (1,aaa) NOTICE: t AFTER INSERT ROW: (1,aaa) NOTICE: t AFTER INSERT STATEMENT: INSERT 0 1
Обновление:
=> UPDATE t SET s = 'bbb';
NOTICE: t BEFORE UPDATE STATEMENT: NOTICE: t BEFORE UPDATE ROW: (1,aaa) -> (1,bbb) NOTICE: t AFTER UPDATE ROW: (1,aaa) -> (1,bbb) NOTICE: t AFTER UPDATE STATEMENT: UPDATE 1
Триггеры на уровне оператора сработают даже если команда не обработала ни одной строки:
=> UPDATE t SET s = 'ccc' where id = 0;
NOTICE: t BEFORE UPDATE STATEMENT: NOTICE: t AFTER UPDATE STATEMENT: UPDATE 0
Тонкий момент: оператор UPSERT приводит к тому, что срабатывают триггеры BEFORE ROW и на вставку, и на обновление:
=> INSERT INTO t VALUES (1,'ccc'),(3,'ddd') ON CONFLICT(id) DO UPDATE SET s = EXCLUDED.s;
NOTICE: t BEFORE INSERT STATEMENT: NOTICE: t BEFORE UPDATE STATEMENT: NOTICE: t BEFORE INSERT ROW: (1,ccc) NOTICE: t BEFORE UPDATE ROW: (1,bbb) -> (1,ccc) NOTICE: t BEFORE INSERT ROW: (3,ddd) NOTICE: t AFTER UPDATE ROW: (1,bbb) -> (1,ccc) NOTICE: t AFTER INSERT ROW: (3,ddd) NOTICE: t AFTER UPDATE STATEMENT: NOTICE: t AFTER INSERT STATEMENT: INSERT 0 2
И, наконец, удаление:
=> DELETE FROM t;
NOTICE: t BEFORE DELETE STATEMENT: NOTICE: t BEFORE DELETE ROW: (1,ccc) NOTICE: t BEFORE DELETE ROW: (3,ddd) NOTICE: t AFTER DELETE ROW: (1,ccc) NOTICE: t AFTER DELETE ROW: (3,ddd) NOTICE: t AFTER DELETE STATEMENT: DELETE 2
Пусть есть таблица, содержащая актуальные данные. Задача состоит в том, чтобы в отдельной таблице сохранять всю историю изменения строк основной таблицы.
Поддержку исторической таблицы можно было бы возложить на приложение, но тогда велика вероятность, что в каких-то случаях из-за ошибок история не будет сохраняться. Поэтому решим задачу с помощью триггера.
Основная таблица:
=> CREATE TABLE main( id serial PRIMARY KEY, s text );
CREATE TABLE
Историческая таблица:
=> CREATE TABLE main_history(LIKE t);
CREATE TABLE
=> ALTER TABLE main_history ADD start_date timestamp, ADD end_date timestamp;
ALTER TABLE
Одна триггерная функция будет обрабатывать вставку строк:
=> CREATE OR REPLACE FUNCTION history_insert() RETURNS trigger AS $$ BEGIN EXECUTE format('INSERT INTO %I SELECT ($1).*, current_timestamp, NULL', TG_TABLE_NAME||'_history') USING NEW; RETURN NEW; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
Другая функция будет обрабатывать удаление:
=> CREATE OR REPLACE FUNCTION history_delete() RETURNS trigger AS $$ BEGIN EXECUTE format('UPDATE %I SET end_date = current_timestamp WHERE id = $1 AND end_date IS NULL', TG_TABLE_NAME||'_history') USING OLD.id; RETURN OLD; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
Теперь создадим триггеры:
=> CREATE TRIGGER main_history_insert AFTER INSERT OR UPDATE ON main FOR EACH ROW EXECUTE PROCEDURE history_insert();
CREATE TRIGGER
=> CREATE TRIGGER main_history_delete AFTER UPDATE OR DELETE ON main FOR EACH ROW EXECUTE PROCEDURE history_delete();
CREATE TRIGGER
Важные моменты:
Проверим работу триггеров.
=> INSERT INTO main VALUES (1,'Первое значение');
INSERT 0 1
=> INSERT INTO main VALUES (2,'Второе значение');
INSERT 0 1
=> UPDATE main SET s = 'Первое значение (измененное)' WHERE id = 1;
UPDATE 1
Состояние на 2017-09-01 15:52:27.702569+03:
=> SELECT * FROM main ORDER BY id;
id | s ----+------------------------------ 1 | Первое значение (измененное) 2 | Второе значение (2 rows)
=> INSERT INTO main VALUES (2,'Второе значение (измененное)'),(3,'Третье значение') ON CONFLICT(id) DO UPDATE SET s = EXCLUDED.s;
INSERT 0 2
=> DELETE FROM main WHERE id = 1;
DELETE 1
=> SELECT * FROM main;
id | s ----+------------------------------ 2 | Второе значение (измененное) 3 | Третье значение (2 rows)
=> SELECT * FROM main_history ORDER BY id, start_date;
id | s | start_date | end_date ----+------------------------------+----------------------------+---------------------------- 1 | Первое значение | 2017-09-01 15:52:24.567469 | 2017-09-01 15:52:26.63169 1 | Первое значение (измененное) | 2017-09-01 15:52:26.63169 | 2017-09-01 15:52:29.824234 2 | Второе значение | 2017-09-01 15:52:24.592694 | 2017-09-01 15:52:27.776058 2 | Второе значение (измененное) | 2017-09-01 15:52:27.776058 | 3 | Третье значение | 2017-09-01 15:52:27.776058 | (5 rows)
Теперь по исторической таблице можно восстановить историю на любой момент времени (это напоминает работу механизма MVCC):
=> \set d '2017-09-01 15:52:27.702569+03'
=> SELECT id, s FROM main_history WHERE start_date <= :'d' AND :'d' < end_date ORDER BY id;
id | s ----+------------------------------ 1 | Первое значение (измененное) 2 | Второе значение (2 rows)
Пусть имеются две таблицы: позиции (items) и строки заказов (order_lines):
=> CREATE TABLE items( id serial PRIMARY KEY, description text NOT NULL, CONSTRAINT items_desc_unique UNIQUE(description) );
CREATE TABLE
=> CREATE TABLE order_lines( id serial PRIMARY KEY, item_id integer NOT NULL REFERENCES items(id), qty integer NOT NULL );
CREATE TABLE
Примерные данные:
=> INSERT INTO items(description) VALUES ('Болт');
INSERT 0 1
=> INSERT INTO items(description) VALUES ('Гайка');
INSERT 0 1
=> INSERT INTO items(description) VALUES ('Шуруп');
INSERT 0 1
=> INSERT INTO order_lines(item_id,qty) VALUES (2 /*Гайка*/,50);
INSERT 0 1
=> INSERT INTO order_lines(item_id,qty) VALUES (1 /*Болт*/, 100);
INSERT 0 1
Для удобства можно определить представление:
=> CREATE VIEW order_lines_v AS SELECT ol.id, i.description, ol.qty FROM order_lines ol JOIN items i ON ol.item_id = i.id;
CREATE VIEW
=> SELECT * FROM order_lines_v;
id | description | qty ----+-------------+----- 1 | Гайка | 50 2 | Болт | 100 (2 rows)
Но такое представление не допускает изменений:
=> UPDATE order_lines_v SET description = 'Шуруп' WHERE id = 1;
ERROR: cannot update view "order_lines_v" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
Однако мы можем определить триггер. Триггерная функция может выглядеть, например, так:
=> CREATE OR REPLACE FUNCTION view_update() RETURNS trigger AS $$ BEGIN UPDATE order_lines SET item_id = (SELECT id FROM items WHERE description = NEW.description), qty = NEW.qty WHERE id = OLD.id; RETURN NEW; EXCEPTION WHEN not_null_violation THEN RAISE EXCEPTION 'Указанной позиции "%" не существует',NEW.description; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
Изменение идентификатора игнорируется: мы предполагаем, что он всегда выдается из последовательности.
И сам триггер:
=> CREATE TRIGGER order_lines_v_upd INSTEAD OF UPDATE ON order_lines_v FOR EACH ROW EXECUTE PROCEDURE view_update();
CREATE TRIGGER
Проверим:
=> UPDATE order_lines_v SET qty = qty + 10 RETURNING *;
id | description | qty ----+-------------+----- 1 | Гайка | 60 2 | Болт | 110 (2 rows) UPDATE 2
В базовой таблице:
=> SELECT * FROM order_lines;
id | item_id | qty ----+---------+----- 1 | 2 | 60 2 | 1 | 110 (2 rows)
Обновление описания позиции:
=> UPDATE order_lines_v SET description = 'Шуруп' WHERE id = 1 RETURNING *;
id | description | qty ----+-------------+----- 1 | Шуруп | 60 (1 row) UPDATE 1
Проверим базовую таблицу:
=> SELECT * FROM order_lines;
id | item_id | qty ----+---------+----- 2 | 1 | 110 1 | 3 | 60 (2 rows)
А попытка изменить позицию на несуществующую завершится ошибкой:
=> UPDATE order_lines_v SET description = 'Винт' WHERE id = 1 RETURNING *;
ERROR: Указанной позиции "Винт" не существует CONTEXT: PL/pgSQL function view_update() line 10 at RAISE
Пример триггера для события DDL_COMMAND_END.
Создадим функцию, которая описывает контекст вызова:
=> CREATE OR REPLACE FUNCTION describe_ddl() RETURNS event_trigger AS $$ DECLARE r record; BEGIN -- Для события DDL_COMMAND_END контекст вызова в специальной функции FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP RAISE NOTICE '%. тип: %, OID: %, имя: % ', r.command_tag, r.object_type, r.objid, r.object_identity; END LOOP; -- Функции триггера событий не нужно возвращать значение END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
Сам триггер:
=> CREATE EVENT TRIGGER after_ddl ON ddl_command_end EXECUTE PROCEDURE describe_ddl();
CREATE EVENT TRIGGER
Создаем новую таблицу:
=> CREATE TABLE t3 (id serial primary key);
NOTICE: CREATE SEQUENCE. тип: sequence, OID: 103610, имя: public.t3_id_seq NOTICE: CREATE TABLE. тип: table, OID: 103612, имя: public.t3 NOTICE: CREATE INDEX. тип: index, OID: 103616, имя: public.t3_pkey NOTICE: ALTER SEQUENCE. тип: sequence, OID: 103610, имя: public.t3_id_seq CREATE TABLE
Создание таблицы может может привести к выполнению нескольких команд DDL, поэтому функция pg_event_trigger_ddl_commands возвращает множество строк.
Конец демонстрации.