41.10. Функции триггеров

41.10. Функции триггеров
Назад	Наверх	Глава 41. PL/pgSQL — SQL Процедурный Язык	Начало	Далее

---

41.10. Функции триггеров

41.10.1. Триггеры на изменение данных

41.10.2. Триггеры на события

Возможно использовать PL/pgSQL для определения функций-триггеров на изменения данных или события базы данных. Функция-триггер создается с помощью команды CREATE FUNCTION, объявляя ее как функцию без аргументов и с возвращаемым типом trigger (для триггеров изменения данных) или event_trigger (для триггеров событий базы данных). Специальные локальные переменные с именами TG_something автоматически определены для описания условия, вызвавшего вызов.

41.10.1. Триггеры на изменение данных

A триггер изменения данных объявляется как функция без аргументов и с типом возвращаемого значения 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 не выполняется для этой строки). Если возвращается ненулевое значение, то операция продолжается с этим значением строки. Возвращение значения строки, отличного от исходного значения NEW, изменяет строку, которая будет вставлена или обновлена. Таким образом, если функция триггера хочет, чтобы действие триггера успешно выполнилось без изменения значения строки, необходимо вернуть NEW (или значение, равное ему). Чтобы изменить строку для сохранения, можно заменить отдельные значения непосредственно в NEW и вернуть измененный NEW, или построить полностью новую запись/строку для возврата. В случае триггера перед DELETE возвращаемое значение не имеет прямого эффекта, но оно должно быть ненулевым, чтобы разрешить выполнение действия триггера. Обратите внимание, что NEW равно null в триггерах 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. Однако любой из этих типов триггеров все равно может прервать всю операцию, вызвав ошибку.

Пример 41.3 показывает пример функции триггера на языке PL/pgSQL.

Пример 41.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
        -- 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();

Другой способ регистрации изменений в таблице заключается в создании новой таблицы, которая содержит строку для каждой операции вставки, обновления или удаления. Этот подход можно рассматривать как аудит изменений в таблице. Пример 41.4 показывает пример функции триггера аудита в PL/pgSQL.

Пример 41.4. A 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
        --
        -- 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();

Вариация предыдущего примера использует представление, объединяющее основную таблицу с таблицей аудита, чтобы показать, когда была последний раз изменена каждая запись. Этот подход все еще записывает полный журнал изменений таблицы, но также представляет упрощенный вид журнала аудита, показывая только последнюю отметку времени изменения, полученную из журнала аудита для каждой записи. Пример триггера аудита на представлении в PL/pgSQL показан в Пример 41.5.

Пример 41.5. A 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
        --
        -- 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, поддерживающей сводную таблицу для факт-таблицы в хранилище данных, показан в разделе Пример 41.6.

Пример 41.6. A 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 назначает имена одной или обеим таблицам перехода, а затем функция может ссылаться на эти имена, как если бы они были временными таблицами только для чтения. Пример показан в Пример 41.7.

Пример 41.7. Аудит с помощью таблиц переходов

Этот пример дает те же результаты, что и Пример 41.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
        --
        -- 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();

41.10.2. Триггеры на события

PL/pgSQL может использоваться для определения триггеров событий. Tantor SE требует, чтобы функция, которая должна быть вызвана в качестве триггера события, была объявлена как функция без аргументов и с возвращаемым типом event_trigger.

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

TG_EVENT

Тип данных text; строка, представляющая событие, для которого срабатывает триггер.

TG_TAG

Тип данных text; переменная, которая содержит тег команды, для которого срабатывает триггер.

Пример 41.8 показывает пример функции триггера события на языке PL/pgSQL.

Пример 41.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();

---

Назад	Наверх	Далее
41.9. Ошибки и сообщения	Начало	41.11. PL/pgSQL под капотом