From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | "snake84(at)inwind(dot)it" <snake84(at)inwind(dot)it> |
Cc: | pgsql-novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: assertion and company |
Date: | 2006-09-23 15:59:15 |
Message-ID: | 20060923083404.H32061@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Sat, 23 Sep 2006, snake84(at)inwind(dot)it wrote:
> My problem is:
> i have 2 tables,"editoriale" and "ColaboratoreFisso", in the first i
> have the foreign key of the second and in this last table i have an
> attribute named MembroCR that can assume only boolean values...
>
> Now, i have to be sure that every row in "ColaboratoreFisso", that has
> the key also inside "editoriale", has the attribute MembroCR=TRUE.
>
> So i thought to resolve this with an Assertion, but postgresql doesn't
> implement it... in what way i can solve my matter?
You can probably check the condition in a set of after triggers.
I think you'd need an after insert and update trigger on editoriale to
make sure the new value doesn't line up with a MembroCR = false
collaboratoreFisso and I think an after update trigger on
collaboratoreFisso to prevent MembroCR from being set to false on a row
that is referenced.
I think something in the general vein of the following untested, might
work as a starting point. You'll probably need to do some more work on
them.
create or replace function cfe1() returns trigger as $$
begin
perform 1 from CollaboratoreFisso where CollaboratoreFisso.CodiceFiscaleF
= NEW.MembroComitatoRedazione and CollaboratoreFisso.MembroCR = false for
read only;
if found then raise exception '...'; end if;
return NEW;
end;$$
language 'plpgsql';
create trigger cfet1 after insert or update on editoriale for each row
execute procedure cfe1();
create or replace function cfe2() returns trigger as $$
begin
if (NEW.MembroCR = false) then
perform 1 from editoriale where editoriale.MembroComitatoRedazione =
NEW.CodiceFiscaleF for read only;
if found then raise exception '...'; end if;
end if;
return NEW;
end;$$
language 'plpgsql';
create trigger cfet2 after update on CollaboratoreFisso for each row
execute procedure cfe2();
> ANOTHER QUESTION:
>
> I have some problem with my redundancies:
>
> In what way i can implement them? With triggers? I have tried but my
> code doesn't work, i don't know how to use them :(
I'm not sure what you mean by redundancies here.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-09-25 03:44:14 | Re: pass date type data to PQexecparams |
Previous Message | snake84@inwind.it | 2006-09-23 15:17:23 | please :P |