Re: Conditional rule? - Mailing list pgsql-sql

On Fri, 28 Jul 2000, Andr� N�ss wrote:
> 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;
>   END IF;
> 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...


OLD.* is available during TG_OP = ''DELETE'' in a trigger.

There are sometimes some referential integrity problems on DELETE when using
triggers to do things on tables that have a RI relationship.  Like, if you have
a table that REFERENCES another table ON DELETE SET NULL, and there is a DELETE
proc on the referenced table that also does some other update on those records,
then the regular trigger might happen before the contraint trigger.  In this
case, an update would have a RI problem when it updates some attribute and the
contraint is checked again - it would fail since the referenced primary key is
deleted already but foreign key has not been SET NULL yet by the contraint
trigger. I'm not sure what the rules are on the order of contraint trigger and
other trigger execution but sometimes the order isn't what you want and then
you get the problem on delete.  If it happens, like it happened to me, you
might quit using foreign keys and just program your triggers as much as you can
to do the same checks that the contraint triggers are doing but by having your
own triggers do it, you have control of the order of how things happen on
delete.

> 
> 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
--         - Robert