Delete a master record and all related detail records

Tips submitted by PHPMaker users

Delete a master record and all related detail records

Postby Laim71 » Wed Sep 13, 2017 12:04 am

Hi,
I'd like to know if there's a PhpMaker internal script like ew_Execute... with which I can simultaniously delete a master record and it's related detail records existing in 4 detail tables.

I'll use it for a senario of a empyee living the organization.

In other hand I want to move the deleted records in trash tables.

THANKS
Laim71
 
Posts: 44
Joined: Sun May 28, 2017 8:31 am

Re: Delete a master record and all related detail records

Postby kirondedshem » Wed Sep 13, 2017 2:25 am

you can do one of the folllowing if you really want to delete the detail records as well(NOT RECOOMENDED for your case):
1. enable the delete cascade option on all master/detail relationships when creating them in phpmaker that way when you ever delete a master record it will call all delete pages for all the detail records for which it has cascade delete.Now you just have to put code in row_deleting/rowdeleted event of the detail table such that you can insert the row being deleted($rs) into the trash table
2.you can use the row deleting event of the master table to loop through all its detail tables and delete each detail record using ew_Execute and log it to the trash table as well.

inserting a record from one table to the other of the same structure can be as follows:
insert into table2(select * from table1 where id = 4);

NOTE: deleting a record is not recommended especially if you still allow to recover it later since you will need to run some insert queries as well as update some related tables when you are restoring, also it messes up reports if a person asks for back dated reports of old data, so instead you should put a status column which indicates if a a record is active or deactive it can be int or enum, that way when an employee is leaving you just have to change it to deactivated such that you dont show him in lit pages etc and you can just change it back to active to use him again.
kirondedshem
 
Posts: 315
Joined: Sat Jan 28, 2017 7:13 pm

Re: Delete a master record and all related detail records

Postby Laim71 » Wed Sep 13, 2017 3:38 am

THANKS a lot kirondedshem,

The second tip is easier, but I'd like to make analysis on data in trash tables such us torn over rate, living reasons ..

So I think that the first solution is convenient.

I've yet succefully used the script of copying data from one table to another; so I thing it won't be a problem.

I try and make a feed-back.
Laim71
 
Posts: 44
Joined: Sun May 28, 2017 8:31 am


Return to User Submitted Tips (PHPMaker)