Re: TRIGGER BEFORE INSERT

On Thu, Jan 11, 2007 at 07:26:32PM +0100, Rafal Pietrak wrote:
> Well. I were, but probably I'm doing something wrong with 'deferring the
> trigger'. When I put:
>
> "SET CONSTRAINTS ALL DEFERRED ; "
>
> *before* the UPDATE statement *within* the trigger function (just after
> BEGIN statement there).

1. Doing it within a function has no effect.
2. By default foreign key checks are not deferrable. Did you make yours
deferrable?

> So may be "SET CONSTRAINTS .... DEFERRED " should be used somehow
> differently? I've never had any use for that construct, may be I miss
> something?

Only at the beginning of a transaction and it only works on foreign
keys marked deferrable.

> I cannot see *any* way to reorder the events in the triger function. The
> function is short anough 'not to allow' :) for reordering - it just
> makes an UPDATE to some other table (where it puts a reference to the
> 'fresh ROW') and stores the result of that update in the newly created
> ROW.

A BEFORE trigger cannot see the row, nor can anything called by that
trigger. If you want to call functions that want to see that row, use
an AFTER trigger.

> And the problem is, that UPDATE puts a reference to the fresh ROW and
> that the UPDATE statement does NOT SEE the 'freshly created ROW' - may
> be this is not a case of 'too early constraint check', but rather a
> problem of 'visibility' of data (new data) within a single transaction
> (an UPDATE is launched within the trigger transaction - should see
> already created ROW, shouldn't it?).

BEFORE trigger, no. AFTER trigger, yes. That's the difference between
the two types...

> But as this is the 'second round' of my 'call for help' - I get an
> impression, that there may actually not be a solution. Too bad.

It's possible, by making your foreign key INITIALLY DEFERRED.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.