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

  • Хранимая процедура — это объект базы данных, представляющий собой набор 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. Поддержка для триггеров включена, начиная с 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 — для доступа к старым записям

PQ VPS сервера в 38+ странах.

📌 Для тестирования скриптов, установщиков VPN, Python ботов рекомендуем использовать надежные VPS на короткий срок. Если вам нужна помощь с более сложными задачами, вы можете найти фрилансера, который поможет с настройкой. Узнайте больше о быстрой аренде VPS для экспериментов и о фриланс-бирже для настройки VPS, WordPress. 📌

💥 Подпишись в Телеграм 💥 и задай вопрос по сайтам и хостингам бесплатно!