Re: Conditional rule? - Mailing list pgsql-sql
From | André Næss |
---|---|
Subject | Re: Conditional rule? |
Date | |
Msg-id | 020f01bff95f$ac6bd2a0$74b5f8c2@wkst6 Whole thread Raw |
In response to | Aggregates and Primary Keys (Itai Zukerman <zukerman@math-hat.com>) |
Responses |
Re: Conditional rule?
|
List | pgsql-sql |
----- Original Message ----- > > 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. Indeed it is, I just didn't read the manual good enough :) > 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. 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 nice. Thanks André Næss