Skip site navigation (1) Skip section navigation (2)

Re: Conditional rule?

From: André Næss <andre(dot)nass(at)student(dot)uib(dot)no>
To: "Robert B(dot) Easter" <reaster(at)comptechnews(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Conditional rule?
Date: 2000-07-29 13:19:49
Message-ID: 020f01bff95f$ac6bd2a0$74b5f8c2@wkst6 (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
----- Original Message -----
> > And this works as intended. There are however a few things that worries
> > 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
> > 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
> > sets the time value to null in b_news for each row that is affected by
> > delete. I understand that the OLD and NEW objects are accessible only
> > UPDATE or INSERT operations, so I can't quite see how to do this...
> OLD.* is available during TG_OP = ''DELETE'' in a trigger.

Indeed it is, I just didn't read the manual good enough :)

> There are sometimes some referential integrity problems on DELETE when
> triggers to do things on tables that have a RI relationship.  Like, if you
> a table that REFERENCES another table ON DELETE SET NULL, and there is a
> proc on the referenced table that also does some other update on those
> then the regular trigger might happen before the contraint trigger.  In
> case, an update would have a RI problem when it updates some attribute and
> 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
> other trigger execution but sometimes the order isn't what you want and
> 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
> own triggers do it, you have control of the order of how things happen on
> delete.

This seems ok to me, but I still don't like the invisibility as I pointed
out above. If someone else was to continue my work how would they be able to
see the triggers and procedures? They don't show up on "\d tablename", so it
is very tempting to simply do this in PHP (i.e. managing the
news_unpublished table with PHP.) I have the same issue with constraints, as
they are invisible too.

So, it comes down to something like this; I would really like to use
constraints and trigger procedures, but in order to do so I need to keep a
very close eye on my database, this means that I might get into trouble if I
need to do some changes to this system after several months -- will I
remember all these hidden behaviours? By doing it in PHP all the behaviour
will be there in code, all visible and (hopefully) understandable to both
myself and others.

Again, I'm a postgre newbie, so there might be something essential I've
simply overlooked, but some clarification concerning this topic would be


André Næss

In response to


pgsql-sql by date

Next:From: Itai ZukermanDate: 2000-07-29 13:29:44
Subject: Just 'sql'?
Previous:From: Robert B. EasterDate: 2000-07-29 01:35:06
Subject: Re: Conditional rule?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group