Хранимые процедуры, функции и триггеры

• Хранимая процедура — это объект базы данных, представляющий собой набор SQL- инструкций, который компилируется один раз и хранится на сервере.
• Хранимая функция отличается от хранимой пороцедуры, тем что хранимая функция всегда возвращает только скаляр(то есть единичное значение), а процедура возвращает набор значений.
• Триггер (trigger) — это хранимая процедура, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице, или изменением UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. В PostgreSQL триггеры создаются на основе существующих функции, т.е. сначала командой CREATE FUNCTION определяется триггерная функция, затем на ее основе командой CREATE TRIGGER определяется собственно триггер. Посмотреть существующие триггеры:

  select * from pg_trigger;

Есть ли в PostgreSQL хранимые процедуры? В чистом виде нет.

• Вместо CREATE RULE в 99% будет правильно использовать CREATE TRIGGER
• Пример функции(PL/pgSQL), которая выводит текущую дату

  CREATE OR REPLACE FUNCTION getDate() RETURNS text AS 
  $BODY$
  BEGIN
  RETURN CURRENT_TIMESTAMP;
  END
  $BODY$
  LANGUAGE 'plpgsql' ;

• Функция возвращающая много строк

  CREATE OR REPLACE FUNCTION g_peer() RETURNS SETOF text AS
  $BODY$
  DECLARE
  	r text;
  BEGIN
  FOR r IN SELECT DISTINCT "peer_id"
  	FROM "ratesheets"
  	ORDER BY peer_id
  LOOP
  RETURN NEXT r;
  END LOOP;
  RETURN;
  END
  $BODY$
  LANGUAGE 'plpgsql'

• Функция возвращает сведения о времени последнего процесa autovacuum, autoanalyze. Возвращающей столбец записей.

  CREATE OR REPLACE FUNCTION sql_last_v_a() RETURNS SETOF record AS'
  
  SELECT schemaname,relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze
  FROM pg_stat_all_tables
  ORDER BY schemaname, relname;
  
  'LANGUAGE sql;

Триггеры Движок БД MySQL. Поддержка для триггеров включена, начиная с MySQL 5.0.2.

Триггер представляет собой именованный объект базы данных (хранимая процедура), который связан с таблицей, и он будет активизирован, когда специфическое событие INSERT, UPDATE и DELETE (вставка, обновление строки или удаление) происходит для таблицы. Триггеры могут срабатывать как до BEFORE так и после AFTER изменения таблицы.

CREATE TRIGGER trigger_name trigger_time trigger_event
  ON tbl_name FOR EACH ROW trigger_stmt

где

trigger_name — название триггера
trigger_time — Время срабатывания триггера. BEFORE — перед событием. AFTER — после события.
trigger_event — Событие:
insert — событие возбуждается операторами insert, data load, replace
update — событие возбуждается оператором update
delete — событие возбуждается операторами delete, replace.
Операторы DROP TABLE и TRUNCATE не активируют выполнение триггера
tbl_name — название таблицы
trigger_stmt выражение, которое выполняется при активации триггера

• Просмотреть все триггеры в конкретной базе данных. Чтобы вывести все триггеры, определенных для базы данных dbname, нужно выполнить запрос к таблице INFORMATION_SCHEMA.TRIGGERS:

  SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, 
  ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='dbname';

  или

  SHOW TRIGGERS FROM dbname;

• Для удаления триггера нужно указывать схему

  mysql> DROP TRIGGER dbname.my_trigger;

Задача. Нужно после вставки (INSERT) строки в таблицу cdr, обновить (UPDATE) таблицу actions внеся в нее значение id новой записи таблицы cdr.

mysql> DELIMITER //
mysql> CREATE TRIGGER `ti_cdr` AFTER INSERT ON `mbillcc`.`cdr`
    -> FOR EACH ROW
    -> BEGIN
    -> UPDATE actions SET state = NEW.id WHERE action = 'cdrrow_end';
    -> END//
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> DELIMITER ;

NEW — для доступа к новым записям; OLD — для доступа к старым записям.

В данном примере процедура не принимает параметров и просто выполняет стандартный SELECT-запрос. Вы можете создавать процедуры с входными параметрами, обработкой ошибок, условиями, переменными — всё это позволяет реализовать сложную бизнес-логику на стороне сервера.

Процедура возвращает только указанные поля из таблицы users, что более явно и удобно для работы в консоли рабочей среды MySQL.

DELIMITER //
 
CREATE PROCEDURE GetUsersMainFields()
BEGIN
    SELECT id, name, email FROM users;
END//
 
DELIMITER ;

Для вызова этой процедуры используйте команду:

CALL GetUsersMainFields();

Хранимые процедуры, функции и триггеры — ключевые инструменты автоматизации и повышения безопасности работы с современными реляционными СУБД. Они позволяют выносить бизнес-логику на уровень базы данных, обеспечивать целостность данных, ускорять обработку запросов и минимизировать ошибки при выполнении часто повторяющихся операций.

В PostgreSQL и MySQL подходы к созданию и использованию хранимых объектов имеют свои особенности, но в обоих случаях они значительно расширяют возможности администрирования, обработки данных и интеграции между приложениями и базой. Правильное использование процедур и триггеров делает проект более удобным для поддержки, масштабирования и аудита.