Re: Order of before_delete trigger in docs

[prev] [thread] [next] [Date index for 2005/06/27]

On Mon, Jun 27, 2005 at 03:03:23PM +0100, William Ross wrote:
> You (and Bill) are right: the documentation is out of date.
> 
> Triggers for a particular point are executed in order of declaration,  
> and one of the consequences of calling has_many(...) is to declare a  
> before_delete trigger to handle the cascading delete. If you want to  
> use a before_delete method to interfere with cascading deletion, you  
> have to declare it before you declare the relationship. I guess this  
> has been true ever since relationships were factored out.

Ok, thanks.

So if it is a reasonable use of a before_delete trigger to make sure
it's ok to delete that object, then I think the docs might need a
warning.

The problem is if you use a before_delete trigger like in my example
and then at a later time move to CDBI::Loader that trigger will
(may?) silently quit working.

Oh, Tony sorry -- my "Role" table is not my linking table like in the
Actor/Film example in the docs.  Role for me is the list of possible
roles a user (person) can have.  My link table is "person_role".  And
if I have a CRUD page for managing Role, I don't want to allow
deleting a role if it's actually in use.


So, It's basically this in the user_role table:

Foreign-key constraints:
    "$2" FOREIGN KEY (person) REFERENCES person(id) ON DELETE CASCADE
    "$1" FOREIGN KEY (role) REFERENCES role(id) ON DELETE RESTRICT

test=> delete from role where id = 10;
ERROR:  update or delete on "role" violates foreign key constraint "$1" on "person_role"
DETAIL:  Key (id)=(10) is still referenced from table "person_role".

Note that regardless of the order of how the before_delete triggers
are called, CDBI always seems to delete the person_role row before
deleting the role row, thus always bypassing the ON DELETE RESTRICT in the
database.

I can see at least three ways someone might want to implement this:

    1) Test in a Role before_delete trigger: 
        fails depending on order of triggers.

    2) Override Role::delete():
        works fine

    3) Disable the cascade and let the database generate the error:
        not supported (yet) and not database independent.


        -- 
        Bill Moseley
moseley@xxxx.xxx