Триггерные функции
PL/pgSQL можно использовать для определения триггерных функций при изменении
данных или событиях базы данных. Триггерная функция создается с помощью команды
CREATE FUNCTION, объявляющей ее как функцию без аргументов и возвращающую тип
trigger (для триггеров изменения данных) или event_trigger (для триггеров
событий базы данных). Для описания условия, провоцирующего вызов триггерной
функции, автоматически определяются специальные локальные переменные с именами
вида TG_некое_имя.
Триггеры при изменении данных
Триггер изменения данных объявляется как функция без аргументов и
возвращающая тип trigger. Обратите внимание, что функция должна быть объявлена
без аргументов, даже если ожидается, что она будет получать некоторые аргументы,
указанные в CREATE TRIGGER, — такие аргументы передаются через TG_ARGV,
как описано ниже.
Когда в качестве триггера вызывается функция PL/pgSQL, в блоке верхнего уровня автоматически создается несколько специальных переменных:
NEW record
новая строка базы данных для операций INSERT/UPDATE в триггерах уровня строки.
Эта переменная равна NULL в триггерах уровня оператора и для операций DELETE.
OLD record
старая строка базы данных для операций UPDATE/DELETE в триггерах уровня
строки. Эта переменная равна NULL в триггерах уровня оператора и для операций
INSERT.
TG_NAME name
имя сработавшего триггера.
TG_WHEN text
BEFORE, AFTER или INSTEAD OF, в зависимости от определения триггера.
TG_LEVEL text
ROW или STATEMENT, в зависимости от определения триггера.
TG_OP text
операция, для которой сработал триггер: INSERT, UPDATE, DELETE или TRUNCATE.
TG_RELID oid
идентификатор объекта таблицы, для которой сработал триггер.
TG_RELNAME name
таблица, для которой сработал триггер. Сейчас эта переменная устарела и в будущих
релизах может исчезнуть. Используйте вместо нее TG_TABLE_NAME.
TG_TABLE_NAME name
таблица, для которой сработал триггер.
TG_TABLE_SCHEMA name
схема таблицы, для которой сработал триггер.
TG_NARGS integer
количество аргументов, переданных триггерной функции в операторе CREATE TRIGGER.
TG_ARGV text[]
аргументы из оператора CREATE TRIGGER. Индекс массива начинается с 0. При
недопустимых индексах (меньше 0 либо больше или равных tg_nargs) возвращается
значение NULL.
Триггерная функция должна возвращать либо NULL, либо значение записи/строки, точно соответствующее структуре таблицы, для которой был запущен триггер.
Триггеры BEFORE уровня строки могут вернуть NULL, чтобы указать менеджеру
триггеров пропустить оставшуюся часть операции для этой строки (т. е. последующие
триггеры не запускаются, и INSERT/UPDATE/DELETE для этой строки не
выполняется). Если возвращается значение, отличное от NULL, то операция продолжается
с этим значением строки. Возвращение значения строки, отличного от исходного
значения NEW, изменяет строку, которая будет добавлена или изменена. Таким
образом, если триггерной функции нужно, чтобы инициирующее действие успешно
выполнялось без изменения значения строки, необходимо вернуть NEW (или
эквивалентное ей значение). Чтобы изменить сохраняемую строку, можно заменить
отдельные значения непосредственно в NEW и вернуть измененную NEW или
создать и вернуть полностью новую запись/строку. В случае триггера BEFORE для
DELETE возвращаемое значение не имеет прямого эффекта, но оно должно быть
отличным от NULL, чтобы триггер мог продолжить обработку. Обратите внимание, что
в триггерах DELETE NEW равна NULL, поэтому обычно возвращать ее не имеет
смысла. Обычной идиомой в триггерах DELETE является возврат OLD.
Триггеры INSTEAD OF (которые всегда являются триггерами уровня строки и могут
использоваться только в представлениях) могут возвращать NULL, показывая, что они
не выполняли никаких изменений и что оставшуюся часть операции для этой строки
следует пропустить (т. е. последующие триггеры не срабатывают, и строка не
учитывается в счетчике затронутых строк для окружающей команды INSERT/UPDATE/
DELETE). В противном случае должно быть возвращено значение, отличное от NULL,
показывающее, что триггер выполнил запрошенную операцию. Для операций INSERT и
UPDATE возвращаемое значение должно быть NEW, которое триггерная функция
может изменить для передачи командам INSERT RETURNING и UPDATE RETURNING (это
также повлияет на значение строки, передаваемое любым последующим триггерам или
в специальный псевдоним для обращения EXCLUDED в операторе INSERT с
предложением ON CONFLICT DO UPDATE). Для операций DELETE возвращаемое
значение должно быть OLD.
Возвращаемое значение триггера AFTER уровня строки и триггера BEFORE или AFTER уровня оператора всегда игнорируется; оно тоже может быть равно NULL. Однако любой из этих типов триггеров все равно может прервать всю операцию, вызвав ошибку.
В Примере 3 показан пример триггерной функции в PL/pgSQL.
Пример 3. Триггерная функция PL/pgSQL
Этот пример триггера гарантирует, что каждый раз, когда в таблице добавляется или изменяется строка, в этой строке указываются имя текущего пользователя и метка времени. Также этот триггер проверяет, что имя сотрудника задано, а зарплата является положительным значением.
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Проверить, что указаны имя сотрудника и зарплата
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- Кто будет работать на нас, если им придется за это платить?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Запомнить, кто и когда изменил платежную ведомость
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE FUNCTION emp_stamp();
Другой способ регистрации изменений в таблице заключается в создании новой таблицы, содержащей строку для каждого случая добавления, изменения или удаления. Этот подход можно рассматривать как аудит изменений в таблице. В Примере 4 показан пример триггерной функции аудита в PL/pgSQL.
Пример 4. Триггерная функция PL/pgSQL для аудита
Этот пример триггера гарантирует, что любое добавление, изменение или удаление строки в таблице emp записано (т. е. аудировано) в таблице emp_audit. Текущее время и имя пользователя указываются в строке вместе с типом выполняемой операции.
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Создать строку в emp_audit для отображения операции, проведенной в emp,
-- использовать специальную переменную TG_OP для определения типа операции.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*;
END IF;
RETURN NULL; -- результат игнорируется, поскольку это триггер AFTER
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE FUNCTION process_emp_audit();
Разновидность предыдущего примера использует представление, соединяющее основную таблицу с таблицей аудита, для отображения времени последнего изменения каждой записи. При таком подходе по-прежнему записывается полный маршрут аудита изменений в таблице, но также предоставляется упрощенное представление маршрута аудита, отображая только временную метку последнего изменения, полученную из маршрута аудита для каждой записи. В Примере 5 показан пример триггера с представлением для аудита в PL/pgSQL.
Пример 5. Триггерная функция PL/pgSQL для аудита с представлением
В этом примере используется триггер с представлением, который делает это представление изменяемым и гарантирует, что любое добавление, изменение или удаление строки в представлении записывается (т. е. аудируется) в таблице emp_audit. Текущее время и имя пользователя записываются вместе с типом выполненной операции, и представление отображает время последнего изменения каждой строки.
CREATE TABLE emp (
empname text PRIMARY KEY,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer,
stamp timestamp NOT NULL
);
CREATE VIEW emp_view AS
SELECT e.empname,
e.salary,
max(ea.stamp) AS last_updated
FROM emp e
LEFT JOIN emp_audit ea ON ea.empname = e.empname
GROUP BY 1, 2;
CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
BEGIN
--
-- Выполнить требуемую операцию в emp и создать строку в emp_audit
-- для отображения изменения, совершенного в emp.
--
IF (TG_OP = 'DELETE') THEN
DELETE FROM emp WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
OLD.last_updated = now();
INSERT INTO emp_audit VALUES('D', current_user, OLD.*);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('U', current_user, NEW.*);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp VALUES(NEW.empname, NEW.salary);
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('I', current_user, NEW.*);
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
FOR EACH ROW EXECUTE FUNCTION update_emp_view();
Одним из применений триггеров является ведение сводной таблицы для другой таблицы. Результирующую сводку можно использовать вместо исходной таблицы для определенных запросов — зачастую со значительным сокращением времени выполнения. Этот метод обычно используется в хранилищах данных, где таблицы измеренных или наблюдаемых данных (так называемые таблицы фактов) могут быть чрезвычайно большими. В Примере 6 показан пример триггерной функции в PL/pgSQL, ведущей сводную таблицу для таблицы фактов в хранилище данных.
Пример 6. Триггерная функция PL/pgSQL для ведения сводной таблицы
Схема, подробно описанная здесь, частично основана на примере Grocery Store из книги «The Data Warehouse Toolkit» Ральфа Кимбалла (Ralph Kimball).
--
-- Основные таблицы - временные периоды и факты продажи.
--
CREATE TABLE time_dimension (
time_key integer NOT NULL,
day_of_week integer NOT NULL,
day_of_month integer NOT NULL,
month integer NOT NULL,
quarter integer NOT NULL,
year integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
store_key integer NOT NULL,
amount_sold numeric(12,2) NOT NULL,
units_sold integer NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
--
-- Сводная таблица - продажи по периодам.
--
CREATE TABLE sales_summary_bytime (
time_key integer NOT NULL,
amount_sold numeric(15,2) NOT NULL,
units_sold numeric(12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
--
-- Функция и триггер для корректировки столбцов сводки при выполнении команд
-- UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold numeric(12);
delta_amount_cost numeric(15,2);
BEGIN
-- Определить увеличение/уменьшение сумм(ы).
IF (TG_OP = 'DELETE') THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = 'UPDATE') THEN
-- запретить изменения, затрагивающие time_key -
-- (вероятно, это не слишком обременительно, поскольку большинство
-- изменений будет выполняться по схеме DELETE + INSERT).
IF ( OLD.time_key != NEW.time_key) THEN
RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
ELSIF (TG_OP = 'INSERT') THEN
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-- Внести новые значения, изменив строку сводки, или добавив новую.
<<insert_update>>
LOOP
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
EXIT insert_update WHEN found;
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXIT insert_update;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
-- ничего не делать
END;
END LOOP insert_update;
RETURN NULL;
END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime();
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;
Триггеры AFTER также могут использовать таблицы переходов для проверки
всего набора строк, измененных оператором, вызвавшим срабатывание триггера.
Команда CREATE TRIGGER присваивает имена одной или обеим таблицам переходов, а
затем функция может обращаться к ним по этим именам, как если бы они были временными
таблицами только для чтения. Это показано в Примере 7.
Пример 7. Аудит с таблицами переходов
Этот пример выдает те же результаты, что и Пример 4, но вместо использования триггера, срабатывающего для каждой строки, здесь используется триггер, срабатывающий один раз для каждого оператора, после сбора соответствующей информации в таблице переходов. Это может быть значительно быстрее способа с триггером строки, когда оператор, вызвавший срабатывание триггера, изменил много строк. Обратите внимание, что нужно сделать отдельное объявление триггера для каждого типа события, так как предложения REFERENCING для каждого случая должны быть разными. Но это не мешает при желании использовать одну функцию триггера. (Возможно, на практике было бы лучше использовать три отдельные функции и избегать проверок TG_OP во время выполнения.)
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Создать строки в emp_audit для отображения операций, проведенных в emp,
-- использовать специальную переменную TG_OP для определения типов операций.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit
SELECT 'D', now(), current_user, o.* FROM old_table o;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit
SELECT 'U', now(), current_user, n.* FROM new_table n;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit
SELECT 'I', now(), current_user, n.* FROM new_table n;
END IF;
RETURN NULL; -- результат игнорируется, поскольку это триггер AFTER
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit_ins
AFTER INSERT ON emp
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_upd
AFTER UPDATE ON emp
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_del
AFTER DELETE ON emp
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
Триггеры событий
PL/pgSQL можно использовать для определения триггеров событий. QHB требует, чтобы функция, вызываемая как триггер события, была объявлена как функция без аргументов и возвращающая тип event_trigger.
Когда функция PL/pgSQL вызывается как триггер события, в блоке верхнего уровня автоматически создается несколько специальных переменных:
TG_EVENT text
событие, для которого срабатывает триггер.
TG_TAG text
тег команды, для которой срабатывает триггер.
В Примере 8 показан пример функции триггера события в PL/pgSQL.
Пример 8. Триггерная функция события PL/pgSQL
Этот пример триггера просто вызывает сообщение NOTICE каждый раз, когда выполняется поддерживаемая команда.
CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();