Триггеры в PostgreSQL

Захотелось посвятить одну запись триггерам. Люблю я их за то, что делают некоторую работу без моего участия, это так ценно 🙂

Как создать триггер?

Сначала напишем триггерную функцию, которая и будет выполнять некоторые действия при модификации той или иной таблицы. Предположим у нас есть таблица products, хранящая некоторые сведения о продукции на складе:

[pastacode lang=»sql» manual=»CREATE%20TABLE%20products%0A(%0A%20%20product_id%20character%20varying(15)%20NOT%20NULL%2C%0A%20%20product_name%20character%20varying(400)%20NOT%20NULL%2C%0A%20%20amount%20integer%20DEFAULT%200%2C%0A%20%20CONSTRAINT%20products_pkey%20PRIMARY%20KEY%20(product_id)%0A)» message=»» highlight=»» provider=»manual»/]

Поле amount отвечает за то количество продукции, что хранится на складе. Все поступления или расход продукции фиксируются в другой таблице — products_acts:

[pastacode lang=»sql» manual=»CREATE%20TABLE%20products_acts%20(%0Aid%20serial%20not%20null%2C%0Aproduct_id%20character%20varying(15)%20not%20null%2C%0Adate_time%20timestamp%20without%20time%20zone%20DEFAULT%20now()%2C%0Aact_type%20integer%20not%20null%2C%0ACONSTRAINT%20products_acts_pkey%20PRIMARY%20KEY%20(id)%2C%0ACONSTRAINT%20products_acts_products_fkey%20FOREIGN%20KEY%20(product_id)%0A%20%20%20%20%20%20REFERENCES%20products%20(product_id)%20MATCH%20SIMPLE%0A%20%20%20%20%20%20ON%20UPDATE%20CASCADE%20ON%20DELETE%20RESTRICT%0A)» message=»» highlight=»» provider=»manual»/]

Если поле act_type = 0, значит происходит добавление продукции, если 1 — удаление.

Теперь перейдем к самой триггерной функции. Она будет автоматически подсчитывать актуальное количество продукции на складе, которое будет отображаться в поле products.amount:

[pastacode lang=»sql» manual=»CREATE%20OR%20REPLACE%20FUNCTION%20update_products_amount()%0A%20%20RETURNS%20trigger%20AS%0A%24BODY%24begin%0A—%D0%B5%D1%81%D0%BB%D0%B8%20%D1%83%D0%B4%D0%B0%D0%BB%D0%B5%D0%BD%D0%B8%D0%B5%3A%0Aif%20(TG_OP%20%3D%20’DELETE’%20or%20TG_OP%20%3D%20’UPDATE’)%20then%0A—%D0%B5%D1%81%D0%BB%D0%B8%20%D1%83%D0%B4%D0%B0%D0%BB%D1%8F%D0%B5%D1%82%D1%81%D1%8F%20%D0%B7%D0%B0%D0%BF%D0%B8%D1%81%D1%8C%20%D0%BE%20%D0%BF%D0%BE%D1%81%D1%82%D1%83%D0%BF%D0%BB%D0%B5%D0%BD%D0%B8%D0%B8%20%D0%BF%D1%80%D0%BE%D0%B4%D1%83%D0%BA%D1%86%D0%B8%D0%B8%0A%09if%20(OLD.act_type%20%3D%200)%20then%20update%20products%20set%20amount%20%3D%20amount%20-%20OLD.product_count%20where%20product_id%20%3D%20OLD.product_id%3B%0A%09—%D0%B5%D1%81%D0%BB%D0%B8%20%D1%83%D0%B4%D0%B0%D0%BB%D1%8F%D0%B5%D1%82%D1%81%D1%8F%20%D0%B7%D0%B0%D0%BF%D0%B8%D1%81%D1%8C%20%D0%BE%20%D1%81%D0%B1%D1%8B%D1%82%D0%B5%20%D0%BF%D1%80%D0%BE%D0%B4%D1%83%D0%BA%D1%86%D0%B8%D0%B8%0A%09else%20update%20products%20set%20amount%20%3D%20amount%20%2B%20OLD.product_count%20where%20product_id%20%3D%20OLD.product_id%3B%0A%09return%20OLD%3B%0A%09—%D0%B4%D0%BE%D0%B1%D0%B0%D0%B2%D0%BB%D0%B5%D0%BD%D0%B8%D0%B5%20%D0%BF%D1%80%D0%BE%D0%B4%D1%83%D0%BA%D1%86%D0%B8%D0%B8%20%D0%BD%D0%B0%20%D1%81%D0%BA%D0%BB%D0%B0%D0%B4%D0%B5%0Aif%20(TG_OP%20%3D%20’INSERT’%20or%20TG_OP%20%3D%20’UPDATE’)%20then%0A%09—%D0%B5%D1%81%D0%BB%D0%B8%20%D0%B4%D0%BE%D0%B1%D0%B0%D0%B2%D0%BB%D1%8F%D0%B5%D1%82%D1%81%D1%8F%20%D0%B7%D0%B0%D0%BF%D0%B8%D1%81%D1%8C%20%D0%BE%20%D0%BF%D0%BE%D1%81%D1%82%D1%83%D0%BF%D0%BB%D0%B5%D0%BD%D0%B8%D0%B8%20%D0%BF%D1%80%D0%BE%D0%B4%D1%83%D0%BA%D1%86%D0%B8%D0%B8%0A%09if%20(OLD.act_type%20%3D%200)%20then%20update%20products%20set%20amount%20%3D%20amount%20%2B%20NEW.product_count%20where%20product_id%20%3D%20NEW.product_id%3B%0A%09—%D0%B5%D1%81%D0%BB%D0%B8%20%D0%B4%D0%BE%D0%B1%D0%B0%D0%B2%D0%BB%D1%8F%D0%B5%D1%82%D1%81%D1%8F%20%D0%B7%D0%B0%D0%BF%D0%B8%D1%81%D1%8C%20%D0%BE%20%D1%81%D0%B1%D1%8B%D1%82%D0%B5%20%D0%BF%D1%80%D0%BE%D0%B4%D1%83%D0%BA%D1%86%D0%B8%D0%B8%0A%09else%20update%20products%20set%20amount%20%3D%20amount%20-%20NEW.product_count%20where%20product_id%20%3D%20NEW.product_id%3B%0A%09return%20NEW%3B%0A—%D0%BF%D1%80%D0%B8%20%D1%80%D0%B5%D0%B4%D0%B0%D0%BA%D1%82%D0%B8%D1%80%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D0%B8%20%D1%81%D0%BD%D0%B0%D1%87%D0%B0%D0%BB%D0%B0%20%D0%BD%D1%83%D0%B6%D0%BD%D0%BE%20%D0%B2%D0%B5%D1%80%D0%BD%D1%83%D1%82%D1%8C%20%D0%BF%D1%80%D0%B5%D0%B4%D1%8B%D0%B4%D1%83%D1%89%D0%B8%D0%B5%20%D0%B7%D0%BD%D0%B0%D1%87%D0%B5%D0%BD%D0%B8%D1%8F%2C%20%D0%B0%20%D0%B7%D0%B0%D1%82%D0%B5%D0%BC%20%D0%BF%D1%80%D0%B8%D1%81%D0%B2%D0%BE%D0%B8%D1%82%D1%8C%20%D0%BD%D0%BE%D0%B2%D1%8B%D0%B5%0Aend%20if%3B%0Aend%3B%24BODY%24%0A%20%20LANGUAGE%20plpgsql%20VOLATILE%0A%20%20COST%20100%3B» message=»» highlight=»» provider=»manual»/]

Осталось создать триггер для таблицы products_acts:

[pastacode lang=»sql» manual=»CREATE%20TRIGGER%20update_amount%0A%20%20AFTER%20INSERT%20OR%20UPDATE%20OR%20DELETE%0A%20%20ON%20products_acts%0A%20%20FOR%20EACH%20ROW%0A%20%20EXECUTE%20PROCEDURE%20update_products_amount()%3B» message=»» highlight=»» provider=»manual»/]