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.
