Re: [ODBC] ODBC - Mailing list pgsql-general

  I'm having problems when I remove a tuple that have been referenced from
another from other table. I've cretated a foreign key, set on delete no
action and when I try to remove that tuple, the system says me it's not
possible, ok, but then, there are no more responses, i.e the following
queries come without resoults! Is that a bug or I'm doing something wrong
somewhere?

  I'd like some help....

                 Andre Lyra.


----Original Message Follows----
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Alan Wayne <alanjwayne@yahoo.com>
CC: <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] [ODBC] ODBC
Date: Sun, 17 Mar 2002 13:37:19 -0800 (PST)


On Sun, 17 Mar 2002, Alan Wayne wrote:

 > I'm not yet familiar with "before triggers"...give me
 > time. As far as the default "restrict" behavior on

As for triggers, basically there're two "times" a trigger
can run, before the action happens or after.  Before
triggers (ie ones before the action happens) can actually
change what the action is going to do, for example you
can have a trigger like (untested, so I'm probably going
to screw up the syntax):
create function beftrig1() returns opaque as '
begin
  NEW.foo:=upper(NEW.foo);
  return NEW;
end;' language 'plpgsql';
create trigger tr before insert or update on footable
  for each row execute procedure beftrig1();

Which should make the actual foo value inserted the upper
case of the value provided.

After triggers are useful for things like integrity
constraints and such (in fact that's the mechanism
behind the foreign key constriants).

 > removing a parent that has children, my documentation
 > is not very clear. Whan I've read seems to imply that
 > the default behavior on delete when the child table
 > uses "REFERENCES..." is to perform "no action" on the
 > child, but there is no mention as to what is allowed
 > on the parent. Clarification on this point would be
 > most appreciated.

Okay, the foreign key constraint must be satisified
whenever it is checked (at end of statement or end
of transaction depending on the deferrability of the
constraint).  That means that at that point there must
not be records in the referencing table that do not
match a record in the referenced table.  The actions
allow something to happen before that check is made that
would normally fix the situation (setting the values
in the referencing table to null or deleting the row,
etc...).  Logically you can think of "no action" as
being no action is performed and then the check makes
sure there isn't a row in the referencing table that
doesn't have a match in the referenced table is run
which will fail if you've just deleted a referenced
row in the referenced table.


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org


_________________________________________________________________
Converse com amigos on-line, experimente o MSN Messenger:
http://messenger.msn.com.br