Re: partial "on-delete set null" constraint

W dniu 03.02.2015 o 04:44, Jim Nasby pisze:
> On 1/3/15 2:49 AM, Rafal Pietrak wrote:
>>
[-----------------------]
>> But an application could do
>> ---------a successfull scenario with expected result-------------------
>> testvm=# UPDATE mailboxes SET username = null;
>> UPDATE 1
>> testvm=# DELETE FROM mailusers ;
>> DELETE 1
>> -----------------------------------------------------------
>> Which works just fine.
>>
>> So I add a TRIGER BEFORE, to have the above first statement get executed
>> just like in the above example: before the actual DELETE:
>> ----------------------
>> CREATE or replace FUNCTION prepare_null () returns trigger language
>> plpgsql as $$ begin old.username=null; return old; end; $$;
>> CREATE TRIGGER prepare_null BEFORE DELETE On mailusers for each row
>> execute procedure prepare_null();
>> -----------------------------
>
> That trigger function is NOT doing the same thing as above. What you
> want is something that does UPDATE mailboxes SET username = null WHERE
> username = OLD.username. You'd need to make sure that trigger ran
> before the RI trigger did.

Oh. yes. It actually does work. Silly me making such obvious mistakes.

Nonetheless I stand my grounds on the FK internal functionality opinion,
in that if FK constraint internal triggers would SET NULL (when cascaded
set null is declared) only those columns of the target, that are
actually NULL-able, then the coding (SQL application programmer's life
like myown) would get easier in such usage scenarios.

thenx anyway!

-R