Thanks for all the help so far. What I now have is the following structure:
create table b_news ( id serial primary key, title varchar(60), time timestamp
);
create table b_news_unpublished ( news_id int references news on delete cascade
);
CREATE FUNCTION b_news_trigproc() RETURNS OPAQUE AS '
BEGIN
IF TG_OP = ''INSERT'' THEN IF NEW.time NOTNULL THEN INSERT INTO b_news_unpublished VALUES (NEW.id); END IF;
END IF;
IF TG_OP = ''UPDATE'' THEN IF NEW.time NOTNULL AND OLD.time ISNULL THEN INSERT INTO b_news_unpublished VALUES
(NEW.id);END IF; IF NEW.time ISNULL AND OLD.time NOTNULL THEN DELETE FROM b_news_unpublished WHERE news_id=NEW.id;
ENDIF;
END IF;
RETURN null;
END;
' LANGUAGE 'plpgsql';
create trigger b_news_trigger
after insert or update on b_news
for each row execute procedure b_news_trigproc();
And this works as intended. There are however a few things that worries me.
First of all, I can't seem to find any way to list the trigger and the
function, they seem invisible. This is problematic because my work will be
continued by others, and allthough I will document everything I think it
should be possible to see the triggers and functions somehow...
Secondly, I miss one final idea, when a delete is performed on the
b_news_unpublished table, I would like to set up a rule or procedure that
sets the time value to null in b_news for each row that is affected by the
delete. I understand that the OLD and NEW objects are accessible only during
UPDATE or INSERT operations, so I can't quite see how to do this...
I also find it rather inelegant to use the constraint to handle DELETE
operations on news, whereas UPDATEs and INSERTs are handled by the trigger
procedure. Somehow I would like to either do all the tasks using the trigger
procedure, or using rules.
As for Itai Zukerman's comment: AOL. Good resources around triggers and
rules are very much needed!
Regards
André Næss
