Описание работы с триггерами

Vadichka
Дата: 17.06.2003 15:15:00
Большая просьба !
Если кто-нить где-нить видел толковое описание работы с триггерами в PostgreSQL (предпочтительно на русском), очень огромное пожалуйста пробросьте URL. Заранее спасибо.
Stellar.
Дата: 17.06.2003 17:50:12
На примитивном уровне примерно так:

CREATE TABLE abc(
id SERIAL,
field1 int,
primary key(id)
);

CREATE OR REPLACE FUNCTION trig_function () RETURNS OPAQUE AS '
BEGIN
-- Если процедура вызывается BEFORE или AFTER INSERT, то данные, которые
-- передаются в нее, содержатся в структуре NEW
-- NEW.имя_столбца

-- Если процедура вызывается BEFORE или AFTER DELETE, то данные, которые
-- передаются в нее, содержатся в структуре OLD
-- OLD.имя_столбца

-- Если процедура вызывается BEFORE или AFTER UPDATE, то данные, которые
-- передаются в нее, содержатся в структуре OLD
-- OLD.имя_столбца, а данные, которые будут обновлены - в структуре NEW
-- NEW.имя_столбца

END
'

language 'plpgsql';

CREATE TRIGGER tr_bu BEFORE UPDATE ON abc FOR EACH ROW EXECUTE PROCEDURE trig_function ();

CREATE TRIGGER tr_au AFTER UPDATE ON abc FOR EACH ROW EXECUTE PROCEDURE trig_function ();

CREATE TRIGGER tr_db BEFORE DELETE ON abc FOR EACH ROW EXECUTE PROCEDURE trig_function ();

CREATE TRIGGER tr_ad AFTER DELETE ON abc FOR EACH ROW EXECUTE PROCEDURE trig_function ();

CREATE TRIGGER tr_bi BEFORE INSERT ON abc FOR EACH ROW EXECUTE PROCEDURE trig_function ();

CREATE TRIGGER tr_ai AFTER INSERT ON abc FOR EACH ROW EXECUTE PROCEDURE trig_function ();

Vadichka
Дата: 18.06.2003 13:40:01
Огромное спасибо за уделенное внимание моей проблеме, но на самом деле она у меня глубже чем могло показаться. Я для того и просил описание потому как я в этом новичок и пока мне не ясен механизм работы - триггер - функция.
А точнее можно ли внутри функции использовать select, insert, update других таблиц ? На сколько силен математический апарат в этих функциях и что он умеет ? Я понимаю, что это вопросы по синтаксису языка используемого внутри функций, но если не затруднит ответ на такие вопросы буду очень признателен. Если нет описания вышеупомянутых вещей, то не мог бы кто-нибудь дать простой примерчик, когда внутри функции беруться данные из разных таблиц, сравниваются и, в зависимости от сравнения заносяться/не заносяться в некую следующую таблицу. Зарание огромное спасибо......
Stellar.
Дата: 18.06.2003 15:36:33
Вот пример того, как на триггерах можно построить гостевую книгу, в которой в статистике пользователя есть количество его сообщений.


CREATE TABLE users(
id SERIAL,
firstname VARCHAR(64),
lastname VARCHAR(64),
email VARCHAR(128),
posts int NOT NULL DEFAULT 0, -- количество сообщений

primary key(id)
);

CREATE TABLE gusetbook (
id SERIAL,
users_id int NOT NULL REFERENCES users(id),
sublect VARCHAR(255),
message TEXT,
primary key(id)
);

CREATE OR REPLACE FUNCTION set_posts_bi() RETURNS OPAQUE AS'
BEGIN
UPDATE users SET posts = posts + 1 WHERE id = NEW.users_id;
RETURN NEW;
END;
'

language 'plpgsql';
CREATE TRIGGER gusetbook_bi BEFORE INSERT ON gusetbook FOR EACH ROW EXECUTE PROCEDURE set_posts_bi();

CREATE OR REPLACE FUNCTION set_posts_ad() RETURNS OPAQUE AS'
BEGIN
UPDATE users SET posts = posts - 1 WHERE id = OLD.users_id;
RETURN NEW;
END;
'

language 'plpgsql';
CREATE TRIGGER gusetbook_ad AFTER DELETE ON gusetbook FOR EACH ROW EXECUTE PROCEDURE set_posts_ad();

Stellar.
Дата: 18.06.2003 15:38:41
В прошлом посте ошибка:


CREATE OR REPLACE FUNCTION set_posts_ad() RETURNS OPAQUE AS'
BEGIN
UPDATE users SET posts = posts - 1 WHERE id = OLD.users_id;
RETURN OLD; -- здесь возвращаем OLD
-- Для отката транзакции возвращаем NULL
END;
'

language 'plpgsql';
CREATE TRIGGER gusetbook_ad AFTER DELETE ON gusetbook FOR EACH ROW EXECUTE PROCEDURE set_posts_ad();


Vadichka
Дата: 18.06.2003 16:16:38
Огромное спасибо за этот пример. Но, если не затруднит, не могли бы немного его дополнить. Допустим в таблице users еще существует поле member - флаг, принимающий значение 0 - не зарегистрированный пользователь 1- зарегистрированный. И если пользователь зарегистрирован, то количество его сообщений считается, если не зарегистрирован, то нет.
Может в плане права на жизнь это не очень удачный пример, но интересует в нем следующее: Есть ли в синтаксисе, используемом в функциях аналог if then else ? Можно ли внутри функции результат select-а сравнить с некой величиной и в зависимости от этого сделать update или не делать оного ? Да и есть ли возможность как-нибудь отпарсить данные, таким образом, что бы сравнить часть строки NEW.имя столбца с частью строки полученной в результате select-а из какой-то таблицы и по результату произвести то или иное действие ?
Stellar.
Дата: 18.06.2003 16:40:05

ALTER TABLE users ADD COLUMN is_registered BOOL;

CREATE OR REPLACE FUNCTION set_posts_bi() RETURNS OPAQUE AS'
DECLARE
bool bRegistered := false;
BEGIN

SELECT is_registered INTO bRegistered FROM users WHERE id = NEW.users_id;
IF bRegistered THEN
UPDATE users SET posts = posts + 1 WHERE id = NEW.users_id;
ELSE
RAISE NOTICE '
'user is not registered'';
END IF;

RETURN NEW;
END;
'

language 'plpgsql';

Vadichka
Дата: 18.06.2003 17:21:04
Еще раз огромное спасибо, теперь я смогу чего-нить да придумать. Если опять зайду в тупик, обращусь за помощью. Да, если не секрет, откуда все это узнаеться опытными людьми?
Stellar.
Дата: 18.06.2003 17:30:12
http://www.postgresql.org/docs/pdf/7.3/admin-7.3.2-A4.pdf
http://www.postgresql.org/docs/pdf/7.3/catalogs-7.3.2-A4.pdf
http://www.postgresql.org/docs/pdf/7.3/connections-7.3.2-A4.pdf
http://www.postgresql.org/docs/pdf/7.3/developer-7.3.2-A4.pdf
http://www.postgresql.org/docs/pdf/7.3/programmer-7.3.2-A4.pdf
http://www.postgresql.org/docs/pdf/7.3/reference-7.3.2-A4.pdf
http://www.postgresql.org/docs/pdf/7.3/tutorial-7.3.2-A4.pdf
http://www.postgresql.org/docs/pdf/7.3/user-7.3.2-A4.pdf
OdbAlex
Дата: 21.06.2003 00:38:29
Вопрос to Stellar:
А зачем Вы в триггере After Delete возвращаете OLD? Насколько я понимаю, возвращаемое значение актуально только для триггеров Before, где возврат NULL отменяет соответствующее действие, или я не прав?