How to prohibit delete of row in parent table if is refwrenced by child tables

[rafi farchi]

Hi
How i can define a requiers rule so that a row in parent table can be deleted only if it does not have any reference . if an attempt to delete and there are rows in child tables then the delete is blocked and a message pop up (same as in m/s access)- you can not delete it is referenced by child tables ...

[Anthony]

You can create a before_delete callback, as described here: http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#callbacks-on-record-insert--delete-and-update

If the record(s) being deleted have any references, just have the callback return True, which will cancel the delete. In that case, the .delete() call itself will return 0, so you can test for that value to determine that the delete failed.

Anthony

[rafi farchi]

Hi
Thanks , i assume it will apply on any ui Such as form grid etc . in this case what message the user will receive on attempt
To delete ?
The example in the book is very basic . can you please give an example of a pre delete call back for example prohibit delete of order header if it has order lines?

[Anthony]

On Monday, December 4, 2017 at 11:34:54 AM UTC-5, rafi farchi wrote:

Hi
Thanks , i assume it will apply on any ui Such as form grid etc . in this case what message the user will receive on attempt
To delete ?

The before_delete callback operates at the level of the DAL (i.e., when the .delete() method is called on a DAL Set object), so by default, no message will be displayed in the UI. You will be responsible for checking for the failed delete in your code and then displaying the appropriate message to the user.

If you are using SQLFORM.grid, you can make use of the "deletable" and/or "ondelete" arguments. The former can be a callable that takes the record to be deleted and returns a falsey value if it should not be deleted (though I do not think any message gets displayed in the UI if the delete fails). The latter can be a callable that takes the DAL table and the id of the record to be deleted -- the only way to prevent the delete using this approach is for the ondelete function to do a redirect or raise an HTTP error, which will abandon the current request (you could set a flag in the session before the redirect and add logic to the view to display an error message based on that flag).

 

The example in the book is very basic . can you please give an example of a pre delete call back for example prohibit delete of order header if it has order lines?

The before_delete callback takes a DAL Set object, such as db(db.mytable.id == some_id), so you can use that as you would any Set object to generate queries and retrieve records. To check whether any records in a set have any referencing records in another table, you could do something like:

def my_before_delete(dbset):
    return not dbset(db.mytable.id == db.othertable.mytable).isempty()

The above query does a join with the referencing table, and the function returns True if the join query is not empty (implying there are referencing records).

Anthony 

[Val K]

Also, you can set  ondelete = "NO ACTION"  at field definition that is referenced 

[Anthony]

On Monday, December 4, 2017 at 5:27:30 PM UTC-5, Val K wrote:

Also, you can set  ondelete = "NO ACTION"  at field definition that is referenced 

That will prevent the delete at the database level, but I believe it will result in an IntegrityError exception being thrown by the database driver, which will result in a web2py error ticket if you don't catch the error in the code. So, you would want to wrap the delete in a try/except.

Anthony

[rafi farchi]

Hi

I have tested the NO ACTION and it gives some king of solution . I have defined the following simple model

db.define_table('table_parent',Field('parent_name'))

db.define_table('table_child',Field('child_name'),

Field('parentid', 'reference table_parent',ondelete='NO ACTION',required=True ))

with smart grid , on attempt to delete from parent table if there are a child reference it display an error messgae 

"An error occurred , Please reload the Page "  . That means the Form continue to function without throwing a ticket .

But , Still it will be nice if a more significant message would be displayed .

Thks

Rafi

[Anthony]

Hi

I have tested the NO ACTION and it gives some king of solution . I have defined the following simple model

db.define_table('table_parent',Field('parent_name'))

db.define_table('table_child',Field('child_name'),

Field('parentid', 'reference table_parent',ondelete='NO ACTION',required=True ))

with smart grid , on attempt to delete from parent table if there are a child reference it display an error messgae 

"An error occurred , Please reload the Page "  . That means the Form continue to function without throwing a ticket .

No, the delete request is made via Ajax, and the message you see is the result of an error ticket being generated during an Ajax request. This is probably not the best approach. It's not a bad idea to set ondelete='NO ACTION', as that will enforce the desired behavior at the level of the database, but you should additionally implement code to catch such errors before they hit the database so you can catch them and display an appropriate message in the UI.

Note, if you use the "deletable" argument to the grid and specify a function to determine if a given record is deletable, then the "Delete" button will simply not be present for any non-deletable rows in the grid, which will prevent users from attempting illegal deletes to begin with. Note, though, that the query in the "deletable" function will be executed for each row shown in the grid.

Anthony

[rafi farchi]

Hi
Thks its a good solution . wlll test it .
Many thanks for your detailed expkanations