Теперь поговорим о триггерах.
Они есть и в Постгре, и в Мускуле. Что такое триггер? При появлении некоего событие, которое происходит в определенной таблице в БД, выполняется некий код.
Триггеры являются достаточно мощным средством, позволяющим делать многое не в кодах ПХП (или других языках), а непосредственно в БД.
Ну, например... При изменении каких-то данных в одной таблице надо пересчитать некие данные в другой таблице. Либо вести журнал изменений данных в какой-то таблице, чтобы потом можно было сделать "откат".
Триггеры есть на INSERT, DELETE, UPDATE. Они могут быть BEFORE и AFTER. Внутри триггеров доступны обрабатываемые данные, посредством специальных данных NEW (для вставки и апдейта) и OLD (для апдейта и удаления). В триггерах BEFORE можно даже изменить вставляемые/изменяемые данные!!!
Всё это дает широкий спектр применения, не только те примеры, что я привел чуть ранее.
А в чем же различия между триггерами в Постгре и в Мускуле?
1. Количество триггеров для каждой таблицы.
Мускуль позволяет создать только один триггер для каждой таблицы, для определенного события, на определенное время. То есть, для одной таблицы не может быть несколько триггеров, например, BEFORE UPDATE. Если понадобится дополнить функционал, то придется переписывать существующий триггер, дополняя его новыми возможностями.
Постгре не ограничивает количество. Триггеров может быть столько, сколько пожелает пользователь. Они будут выполнены в алфавитном порядке по своим названиям, благо дело, у них есть уникальные названия (в отличие от Мускуля).
Назначение триггера.
Мускуль (триггер используется для создания самопальной репликации, т.к. штатными средствами Мускуля тут не получается реплицировать).
Свернутый текст
create trigger site_channels_add after insert on site_channels
for each row
insert into replica ( `table_src`, `table_dest`, `action`, `id_data`) values ( 'site_channels', 'rsdb_site_channels', 'add', new.id);
Постгре (для VIEW, см. чуть ниже).
Свернутый текст
CREATE TRIGGER on_insert_to_people_info
INSTEAD OF INSERT
ON people_info
FOR EACH ROW
EXECUTE PROCEDURE insert_people_info_trigger();
2. Содержимое триггера.
В Мускуле мы назначаем триггеру, по сути, безымянную функцию. Да, мы можем вызвать изнутри триггера другие функции, в т.ч. созданные нами, но это будут "просто функции".
Постгре требует создания специальной триггерной функции, имеющей уникальное название. Такая функция, в целом, аналогична другим пользовательским функциям Постгре, с некоторыми дополнениями/ограничениями.
3. Доступ данных из триггера.
Как я уже говорил, изнутри триггера доступны данные старой записи OLD (для обновления и удаления) и новой записи NEW (для вставки и обновления).
В Постгре, плюс к этому, доступны данные о таблице, к которой относится эта триггерная функция.
Вот описалово для всех этих данных:
Свернутый текст
| Цитата |
| NEW Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETE operations. OLD Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers and for INSERT operations. TG_NAME Data type name; variable that contains the name of the trigger actually fired. TG_WHEN Data type text; a string of BEFORE, AFTER, or INSTEAD OF, depending on the trigger's definition. TG_LEVEL Data type text; a string of either ROW or STATEMENT depending on the trigger's definition. TG_OP Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired. TG_RELID Data type oid; the object ID of the table that caused the trigger invocation. TG_RELNAME Data type name; the name of the table that caused the trigger invocation. This is now deprecated, and could disappear in a future release. Use TG_TABLE_NAME instead. TG_TABLE_NAME Data type name; the name of the table that caused the trigger invocation. TG_TABLE_SCHEMA Data type name; the name of the schema of the table that caused the trigger invocation. TG_NARGS Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement. TG_ARGV[] Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value. |
4. Что можно сделать внутри триггера.
В обеих СУБД можно изменить данные в триггере BEFORE. Но вот что нельзя "по человечески" сделать в Мускуле - это отказаться от изменений. Там есть какие-то извратные способы прерывания триггера, которые сбрасывают всю транзакцию. Но это не есть то, что нужно, потому что это изврат полный!
Предположим, что мы грузим кучу данных из текстового файла, посредством LOAD DATA INFILE. Там много тысяч строк, из которых нужно несколько отсеять. В Мускуле можно долго "танцевать с бубном"... А вот в Постгре всё просто :) Достаточно только назначить NEW=NULL и выйти из триггера BEFORE, и вставка/изменение именно этой строки не будет сделана.
5. Для каких объектов срабатывает триггер.
FOR EACH ROW - оба, и Мускуль, и Постгре (для каждой записи)
FOR EACH STATEMENT - только Постгре (для каждой операции, вне зависимости, сколько строк обработано, в т.ч. может быть и если нисколько не обработано).
6. Куда можно навесить триггер?
"Странный вопрос, конечно только на таблицу!", скажет любой апологет Мускуля. И будет не прав. Потому что Постгре позволяет повесить триггер также и на VIEW.
Зачем, говорите? Ну представим, что нам нужно постоянно загружать, допустим, расписание движения поездов. Чаще всего это будут одни и те же поезда и населенные пункты. Но иногда могут добавиться новые. Можно всё это грузить, например, через скрипт ПХП. Загрузили список поездов и населенных пунктов, сравнили, добавили, проапдейтили расписание... По ходу дела сделали 100500 запросов к БД.
А можно просто запустить команду "COPY ... FROM" (аналог LOAD DATA INFILE из Мускуля). Загрузить во VIEW. И при загрузке всё проанализировать, создать нужные строки в таблицах городов и поездов, обновить/создать записи в расписании. А на выходе из триггера просто назначить NEW=NULL и всё будет ОК, никаких сбросов-сбоев.
Вот пример такого триггера на вьюху, вставляет данные о пользователе (распихивает их в разные таблицы). Функция взята из обучающего проекта.
Также хочу обратить внимание на то, что некоторые команды создаются динамически, формируются как символьная строка, а потом выполняются уже как команды.
Также тут используются эксепшены, которые тут же внутри и обрабатываются.
Свернутый текст
CREATE OR REPLACE FUNCTION insert_people_info_trigger()
RETURNS trigger AS
$BODY$
declare
id_st integer;
id_ph integer;
id_p integer;
id_t integer;
id_m integer;
insert_phone varchar(50);
insert_mail varchar(150);
insert_info varchar(1000);
update_phone varchar(50);
update_mail varchar(150);
update_info varchar(1000);
field_phone varchar(20);
field_mail varchar(20);
field_info varchar(20);
new_station integer;
begin
/* проверяю наличие города и получаю айди города */
select id_town into id_t from town where town = new.town;
if id_t is null and new.town <> '' then
insert into town (town) values (new.town) returning id_town into id_t;
end if;
/* получаю айди станции в переменную id_st */
new_station := 0;
select id_station into id_st from station where station = new.station;
if id_st is null and new.station <> '' then
new_station := 1;
raise notice 'station % is not found', new.station;
insert into station (station, id_town) values (new.station, id_t) returning id_station into id_st;
elsif id_st is null and new.station = '' then
raise SQLSTATE 'OWN01';
end if;
/* получаю айди телефона в переменную id_ph */
select id_phone into id_ph from phone where phone = new.phone;
if id_ph is null and new.phone <> '' then
raise notice 'phone % is not found', new.phone;
end if;
/* получаю айди мейла в переменную id_m */
select id_mail into id_m from mail where mail = new.mail;
if id_m is null and new.mail <> '' then
insert into mail (mail) values (new.mail) returning id_mail into id_m;
end if;
if new.info is null then
insert_info := '';
update_info := '';
field_info := '';
else
insert_info := new.info || ', ';
update_info := ' info = ''' || new.info || ''', ' ;
field_info := 'info, ';
end if;
if id_ph is null then
insert_phone := '';
update_phone := '';
field_phone := '';
else
insert_phone := id_ph || ', ';
update_phone := ' id_phone = ' || id_ph || ',' ;
field_phone := 'id_phone, ';
end if;
if id_m is null then
insert_mail := '';
update_mail := '';
field_mail := '';
else
insert_mail := id_m || ', ';
update_mail := ' id_mail = ' || id_m || ',' ;
field_mail := 'id_mail, ';
end if;
/* проверяю наличие человека */
if new_station = 0 then /* если существующая станция */
select id_people into id_p from people where id_station=id_st and full_name=new.full_name;
if id_p is null then
new_station := 1;
else /* апдейтим существующие данные о юзере */
execute 'update people set ' || update_phone || update_info || update_mail || ' modified=NOW() where id_people = ' || id_p;
end if;
end if;
if new_station = 1 then /* надо вносить данные в таблицу юзеров */
execute 'insert into people (id_station, full_name, ' || field_phone || field_info || field_mail || ' created, modified)
values ( ' || id_st || ', ''' || new.full_name || ''', ' || insert_phone || insert_info || insert_mail || ' NOW(), NOW() )';
end if;
RAISE NOTICE 'Info new: %, %, %, %, %', new.station, new.full_name, new.phone, new.mail, new.info;
return NULL;
EXCEPTION
WHEN OTHERS THEN
BEGIN
RAISE INFO 'Fail to save data %', NEW;
return NULL;
END;
END;
Примечание к триггерной функции: RAISE NOTICE и RAISE INFO просто выводят данные в текстовом виде, когда работаем в pgAdmin (там есть специальное окно для этого). Что очень удобно при отладке запроса: можно вывести любую промежуточную инфу, чтобы понять, что где сбоит, а где нормально работает.
Вывод такой. Я бы поставил счет примерно 6:3 в пользу Постгре :) Потому что Мускуль, конечно, позволяет работать с триггерами. Но в Постгре эти возможности куда как шире.
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
