Master/Detail delete dependencies- transaction scope

Scriptcase 9 Bugs
#1

When a record in a Master table is deleted SC gives two options for deleting the dependent records in the Detail table:

  1. Delete dependencies without warning
  2. Warn when there are dependencies

delete
delete442×600 29.9 KB

In case of 1) the deletion of the dependent records performed by SC is carried on a separate transaction than the deletion of the master record; this can break referential integrity of the database if something goes wrong with the deletion of the master record , because you are left with the master record intact but the details record are gone.

#2

I don’t think this is a bug in case the records are deleted in a separate transactions than deleting the master records from the app.
That option is bound to the app.
To guarantee referential integrity the best way is tlet the DB engine manage it (foreign indexes and cascade deletes)

#3

I dont think you got the point.

Lets suppose you have a table M as master and table D as details with D pointing to M via a foreign key with a cascade delete.
It means that if you want to delete a single record in M the database automatically cascade the delete onto all records in D having the foreign key equal to the primary key of the record in M you want to delete.

This delete trasaction is atomic: either you delete the record in M and all linked records in D or you dont delete anything.

This is how the logic of cascade delete works in all relational databases .

SC does not allow this db logic to be used because in a Master/Details form you just have two possibilities:

  1. Warning you about the existence of some details record which halts the delete process altogether.

OR

  1. Try to delete the details record first and afterwards the master record but in two separate transactions. Basically SC is trying to emulate a cascade delete but not in an atomic way.

If you opt for 2 you risk to have the records in D deleted and the record in M not deleted if something goes wrong in deleting the M record.

If you opt for 1 you have to manually delete records in D first and then delete the record in M otherwise SC keeps complaining about the existence of dependent records in D.

My suggestion is to have a third option where basically SC does not do anything and let the database do its job in an atomic way.

#4

I still don’t get why the third option is needed.
What would be the benefit of an option in SC that is set so that SC doesn’t do anything?
SC allows the DB logic: just don’t create any dependency in SC.
I.e. the third option you’re asking for is achieved by to not using (or deleting) the dependency in SC.
That’s what I do and I don’t have any problems.
I use the DB foreign indexes with cascade delete.o

#5

Hy Roby
May be I am missing something

Can you please show me how to generate a form with Master and Details with a Delete button in the Master that:

  1. does not issue any warning if there are dependecies records in Detail
  2. does not delete details and master in two separate transactions
  3. does not force you to write any PHP/SQL customized code in any event.
  4. delete the master record only letting the db to cascade the delete onto Details

Thanks for contributing to this discussion.
I wish you an Happy Easter.

#6

Do you have any dependency set in teh master app in Form settins > Dependecies?

Because that’s what I’m telling you to remove.

But I never use editable grids as details, so I dont’ know if in that case the setting in your screenshot doesn’t depend from the setting I mentioned above. If that’s the case probably you’re right in requesting a third option.

#7

Now I have understood what you mean.
I have no dependencies in the Form settings-Dependencies.
I have the Dependent app in the Form settings->Master/Detail Form.

Thanks anyway for the discussion

#8

If you can’t remove that setting in SC, can’t you set it to delete the detail records without warning and then in the DB set to cascade delete?
So that even if SC fails to delete, the DB will take care of deleting reliably.

#9

When you press the delete button in a master/detail form SC works like this

  1. issue an sql delete on all details records
  2. commit
  3. issue an sql delete on the master record
  4. commit

If something goes wrong in 3) the 1st transaction has been already commited and you are left with a zombie master with no details any more.

The DB with cascade delete works differently.
When you issue a delete on the master record the db acts as following :
A) cascade the delete on the details
B) delete the master
C) commit

Please note you dont need to send a specific sql delete on the details. It is the db engine that does it for you as soon as it realizes there are details records to be deleted before the master record.
If something goes wrong with a) or b) the transaction is rolled back and both master and details are preserved intact.

This db logic is bypassed by SC because SC issue a specific SQL statement to delete the details first then commit then delete the master and commit again.

This is why I am suggesting to Netmake to add the possibility in a master detail form to let the db engine cascade the deletes onto the details.
They simply have to skip 1) and 2) and let the db engine do its work properly.

#10

You can create a new button personalized for delete yours records.