Триггерные функции
Эта страница переведена при помощи нейросети GigaChat.
PL/pgSQL может использоваться для определения функций триггеров при изменении данных или событий базы данных. Функция триггера создается с помощью команды CREATE FUNCTION, объявляющей ее как функцию без аргументов и типом возврата trigger (для триггеров изменения данных) или event_trigger (для триггеров событий базы данных). Специальные локальные переменные с именем TG_something автоматически определяются для описания условия, которое повлекло вызов.
Триггеры при изменении данных
Триггер изменения данных объявлен как функция без аргументов и типа возврата trigger. Обратите внимание, что функция должна быть объявлена без аргументов, даже если она ожидает получить некоторые аргументы, указанные в CREATE TRIGGER – такие аргументы передаются через TG_ARGV, как описано ниже.
Когда функция PL/pgSQL вызывается в качестве триггера, автоматически создаются несколько специальных переменных в верхнем блоке:
NEW record
: новая строка базы данных для операций INSERT/UPDATE на уровне строковых триггеров. Эта переменная равна нулю в триггерах уровня операторов и для операций DELETE.
OLD record
: старая строка базы данных для операций UPDATE/DELETE на уровне строковых триггеров. Эта переменная равна нулю в триггерах уровня операторов и для операций 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 (ссылки на pg_class.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 , либо создать полную новую запись/строку для возврата. В случае триггера перед DELETE , возвращаемое значение не имеет прямого эффекта, но оно должно быть ненулевым, чтобы разрешить выполнение действия триггера. Обратите внимание, что NEW является нулем в триггерах DELETE , поэтому возврат этого обычно не имеет смысла. Обычный идиом в триггерах DELETE заключается в том, чтобы вернуть OLD.
INSTEAD OF триггеры (которые всегда являются триггерами уровня строки и могут использоваться только для представлений) могут возвращать NULL для указания того, что они не выполняли никаких обновлений и что остальная часть операции для этой строки должна быть пропущена, т.е. последующие триггеры не запускаются, а строка не учитывается в статусе затронутых строк для окружающего INSERT/UPDATE/DELETE. В противном случае следует вернуть ненулевое значение, указывая, что триггер выполнил запрошенную операцию. Для операций INSERT и UPDATE возвращаемым значением должно быть NEW, которое функция триггера может изменять для поддержки INSERT RETURNING и UPDATE RETURNING. Это также повлияет на значение строки, передаваемой любым последующим триггерам или передаваемую специальной ссылке EXCLUDED псевдоним внутри оператора INSERT с предложением ON CONFLICT DO UPDATE. Для операций DELETE возвращаемым значением должно быть OLD.
Возвращаемое значение триггера уровня строки, который сработал AFTER, или триггера уровня инструкции, который сработал BEFORE или AFTER , всегда игнорируется. Это может быть и NULL. Однако любой из этих типов триггеров все еще может прервать всю операцию, вызвав ошибку.
Пример. Функция триггера 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
-- Check that empname and salary are given
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;
-- Who works for us when they must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
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();
Еще один способ регистрации изменений в таблице заключается в создании новой таблицы, содержащей строку для каждой вставки, обновления или удаления, которое происходит. Этот подход можно рассматривать как аудит изменений в таблице. В примере ниже показано использование триггерной функции аудита в PL/pgSQL.
Пример. Функция триггера PL/pgSQL для аудита
Этот пример триггера гарантирует, что любая вставка, обновление или удаление строки в таблице записывается (т.е. проверяется) в таблицу. Текущее время и имя пользователя фиксируются в строке вместе с типом операции, выполненной над ней.
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
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- making use of the special variable TG_OP to work out the operation.
--
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; -- result is ignored since this is an AFTER trigger
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();
Вариация предыдущего примера использует представление, объединяющее основную таблицу с таблицей аудита, чтобы показать, когда каждая запись была изменена последний раз. Этот подход все еще записывает полный аудит изменений таблицы, но также представляет упрощенное представление аудита, показывая только последнюю метку времени изменения, полученную из истории аудита для каждой записи. Пример 43.5 показывает пример триггера аудита на представлении в PL/pgSQL.
Пример. Функция триггера представления PL/pgSQL для аудита
В этом примере используется триггер на представлении, чтобы сделать его обновляемым и гарантировать, что любая вставка, обновление или удаление строки в представлении регистрируется (т.е. проверяется) в таблице. Текущее время и имя пользователя фиксируются вместе с типом выполненной операции, а представление отображает последнее время изменения каждой строки.
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
--
-- Perform the required operation on emp, and create a row in emp_audit
-- to reflect the change made to 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();
Одно из применений триггеров – это поддержание сводной таблицы другой таблицы. Полученная сводка может использоваться вместо исходной таблицы для определенных запросов – часто со значительно сокращенным временем выполнения. Эта техника обычно используется при хранении данных, где таблицы измеренных или наблюдаемых данных (называемых таблицами фактов) могут быть чрезвычайно большими. Пример «Функция триггера PL/pgSQL для поддержания сводной таблицы» показывает пример функции триггера в PL/pgSQL, которая поддерживает сводную таблицу для таблицы фактов в хранилище данных.
Пример. Функция триггера PL/pgSQL для поддержания сводной таблицы
Схема, описанная здесь, частично основана на примере «Grocery Store» из книги «The Data Warehouse Toolkit» Ральфа Кимбалла.
--
-- Main tables - time dimension and sales fact.
--
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);
--
-- Summary table - sales by time.
--
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);
--
-- Function and trigger to amend summarized column(s) on 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
-- Work out the increment/decrement amount(s).
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
-- forbid updates that change the time_key -
-- (probably not too onerous, as DELETE + INSERT is how most
-- changes will be made).
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 or update the summary row with the new values.
<<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
-- do nothing
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 назначает имена одной или обеим переходным таблицам, а затем функция может ссылаться на эти имена так, как если бы они были временными таблицами только для чтения. Это отображено в примере «Аудит с использованием переходных таблиц».
Пример. Аудит с использованием переходных таблиц
Этот пример дает те же результаты, что и пример «Функция триггера PL/pgSQL для аудита», но вместо использования триггера, который срабатывает для каждой строки, он использует триггер, который срабатывает один раз за оператор после сбора соответствующей информации в переходной таблице. Это может быть значительно быстрее подхода с триггером строки при изменении вызывающего оператора большого количества строк. Обратите внимание, что нужно сделать отдельное объявление триггера для каждого типа события, поскольку предложения 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
--
-- Create rows in emp_audit to reflect the operations performed on emp,
-- making use of the special variable TG_OP to work out the operation.
--
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; -- result is ignored since this is an AFTER trigger
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 может использоваться для определения триггеров событий. PostgreSQL требует, чтобы функция, которая должна вызываться как триггер события, была объявлена как функция без аргументов и с типом возврата event_trigger.
Когда функция PL/pgSQL вызывается как триггер события, автоматически создаются несколько специальных переменных в верхнем блоке. Это:
TG_EVENT text
: событие, по которому сработал триггер.
TG_TAG text
: метка команды, по которой сработал триггер.
Пример. Функция триггера события PL/pgSQL
Этот пример триггера просто выдает сообщение каждый раз, когда выполняется поддерживаемая команда.
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();