Deletion from multiple tables...?

[Former Member]

I have 1 Master table and 10 child tables. I need to delete entries from these tables. I want to be sure that if there is some problem in the database then I should be able to roll back my deletion. I cannot check for sy-subrc after every delete as there may be some child tables which will not have corresponding entries. Any ideas how we can achieve the optimal design for the same, using commit work. The no of entries which needs to be deleted is huge around 5-6 millions of records need to be deleted. Should we use enqueue deque fn modules?

Loop at itab,

Delete child table1 where key = itab-key.

Delete child table2 where key = itab-key.

.

.

.

Delete child table 10 where key = itab-key.

Delete master table where key = itab-key.

Endloop.

Regards,

Ankur Bhandari

[Former Member]

I have 1 Master table and 10 child tables. I need to delete entries from these tables. I want to be sure that if there is some problem in the database then I should be able to roll back my deletion. I cannot check for sy-subrc after every delete as there may be some child tables which will not have corresponding entries. Any ideas how we can achieve the optimal design for the same, using commit work. The no of entries which needs to be deleted is huge around 5-6 millions of records need to be deleted. Should we use enqueue deque fn modules?

Loop at itab,

Delete child table1 where key = itab-key.

Delete child table2 where key = itab-key.

.

.

.

Delete child table 10 where key = itab-key.

Delete master table where key = itab-key.

Endloop.

Regards,

Ankur Bhandari

[Former Member]

Hi Ankur,

1. Exactly for such purposes,

there is the concept of UPDATE MODULE (Update FM)

2. Create a new FM.

In the atriburtes,

mark it as UPDATE MODULE (Start Immed)

3. Bunch all your sqls in this FM

4. When calling the FM

use the syntax

CALL FUNCTION func IN UPDATE TASK

I hope it helps.

Regards,

Amit M.

[Former Member]

Any sample code would be helpful.How will this ensure my objective.

Ankur Bhandari

[Former Member]

Where do I need to do a commit in this case ?

Will it still work if there are no entries in the child table.In that scenario we dont require a roll back?

Please clarify in detail.

Ankur bhandari

[Former Member]

Hi again,

1 Where do I need to do a commit in this case ?

in the FM itself.

write this statement of commit

as the last statement.

OR U CAN USE THIS STATEMENT

IN UR CALLING PROGRAM ALSO.

2. Will it still work if there are no entries in the child table.

If there are not entries in child table,

then no need to worry!

3. In that scenario we dont require a roll back?

Rollback not required.

If any error occurs in betwee,

the system will take care of rollback by itself.

(THE Update FM concept is designed for this purpose only)

I hope it helps.

regards,

amit m.

Message was edited by: Amit Mittal

[Former Member]

Is there a sample code which can be used as a guideline for the same.

What will be the exporting importing parameters for this function module.How will the exceptions be handled ?

Ankur

[Former Member]

Hi again,

1. What will be the exporting importing parameters for this function m

The IMPORTING parameter

will be just the PRIMARY KEY value

of the Main Table

Using this key in your sqls

u can delte the tables.

2 How will the exceptions be handled ?

Since it is Update FM,

it is not executed immediately but later.

so i don't think exceptiosn will be of any help.

(i may be wrong here)

regards,

amit m.

[Former Member]

Hi Ankur,

One way of achieveing this is,

While looping on itab, select the record from table1 into itab1. And then, use statement,

<b>DELETE table1 FROM TABLE itab1.</b> , To delete the entries from table1.

Similarly, for all tables(child), and at the last use same procedure to delete master table record.

Dont forget to refresh these internal table contents at each loop pass.

Regards,

Raj

[Former Member]

Hi please refer the following program which does the same functionality as ur requirement. COM_HIERARCHY_DELETE_ALL.

Just use commit work after the last delete statement in ur code. It will do the needed.

Satish

[Former Member]

Suppose I implement this functionality, How do I find out the total no of records which have been deleted. Earlier I could use sy-dbcnt after every delete statement.

*********************************************************

Loop at itab.

call function zfum in update task.

commit work.

endloop.

function module zfum.

delete child table 1

delete child table 2.

delete master table.

[Former Member]

Hi Ankur,

1. Since it is a update FM,

we cannot return / export back any parameter.

2. Hence, there is no direct way

to achieve what u require.

regards,

amit m.