Re: Any plans on allowing user-defined triggers to - Mailing list pgsql-general

Well, I guess the only moments when you can verify those conditions are when you insert or delete  a project, because it seems logical to me that, at the moment of a department insertion, you will not have any projects related to it, so the constraint you need will automatically be broken.
So, imho, what you can do is to prevent the system to delete a project when there are only two in a department, or to insert a new one when there are 8 projects already there.


On Tue, 2004-10-26 at 16:18, Mike Mascari wrote:

Valentin Militaru wrote:
> You can do that. But first you have to do some optimisations, like:
>     add a column id(bigserial) to the departamens table, after which you 
> will replace the column department with id_department in the projects 
> table. It is an optimisation, as you are dealing with integer, not text.

Well, that's an argument for surrogate keys, which will invoke a 
philosophical war amongst purists that I won't touch...

>     After that, what do you want to achieve? When you are inserting a
> department, should the server insert 2  to 8 blank records in the 
> projects table which contain the inserted department? Or do you want not 
> to be able to insert a department if there aren't already 2 to 8 
> projects containing that department in the projects table?

I want the database to enforce logical consistency by ensuring that if a 
department exists, there are at a minimum two projects and a maximum of 
eight projects associated with it. Date & Darwen attribute the 
enforcement of such business requirements to database constraints. 
PostgreSQL lacks database constraints, but the result can often be 
achieved through triggers. But I can't figure out how to enforce 
consistency without deferrable triggers and without relying on the 
application to maintain consistency, which is not its job.

Mike Mascari

Valentin Militaru

valentin.militaru@telcor.ro

SC Telcor Communications SRL

Tel. fix: 0316900015

Fax:     0316900001

Telefon mobil: 0741168267