Hi,

What is the best way to delete detail records when deleting a master record.

Say i have a master table offices and a detail table departments. Now i want to delete a master office (and the detail departments). But i want to be able to undo the delete action with api_datasource.executequery, so i need to only remove the records from the office and department datasources.

Deleting the master is not the problem, api_datasource.deleteCurrentRow will do that, but how about the detail department records? What will be the best way to do that? Can you give an example?

Thanks, Michiel

asked 27 Jun '12, 05:00

Michiel%20A's gravatar image

Michiel A
5161544
accept rate: 13%


In Formspider version 1.1 and later: (The development version we sent you includes this feature)

In the action code of the delete button, delete the detail rows before deleting the master row. Then, Formspider commits changes in the order they have occurred.

In Formspider version 1.0 and earlier:

Use the datasource commit order property. Assign a smaller order number to the master datasource, so that the changes to the master rows are applied first during the commit operation. This works fine for the insert operation. However, for the delete operation, Formspider tries to delete master rows before the detail rows. To prevent the integrity constraint error, delete the details in the "Before Delete" trigger of the master table. Please note that you may consider unchecking the Delete Allowed checkbox property of the detail Datasource Definition at this point because when Formspider issues changes for this datasource during a commit, the rows will be already deleted.

link

answered 27 Jun '12, 08:02

Ugur%20Kocak's gravatar image

Ugur Kocak ♦
5616
accept rate: 23%

Hi Ugur,

That does not seem to work in my version. On the delete action i do delete first the details, then the master:

-- delete details api_datasource.deleteRow(in_datasourcename_tx => 'aut_usergroups_v1' ,in_row_id => api_datasource.ALL_ROWS );

-- delete master api_datasource.deleteCurrentRow(in_datasourcename_tx => 'aut_usertable_v1');

But on doCommit, it still gives me the FK violation.. Are you sure that feature is in my update? If it is, then in this case it does not work as expected.

Thanks, Michiel

(28 Jun '12, 00:45) Michiel A

Hi Michiel,

Which commit api do you use, api_application.docommit or api_datasource.doCommit(DS) or api_datasource.doCommit(DSLIST)?

Are the commit order attributes set on the datasources?

(29 Jun '12, 04:45) Ugur Kocak ♦

I use api_application.docommit and i have set the master table commit order to 1 and detail table commit order to 2.

(29 Jun '12, 06:00) Michiel A

If you set commitOrder, it guaranties that the first datasource is always committed before. So, could you set the commit orders to null?

(29 Jun '12, 06:05) Ugur Kocak ♦

I set all datasource commit orders to null, but no difference, still the FK violation on docommit.

(29 Jun '12, 06:35) Michiel A

If master datasource had one row, it would work. However, if you use grid component for the master datasource(it has multiple rows), it may cause errors. Because, you run executeQuery for the detail datasouce when current row of the master is changed. Also when you delete the master current row, currentRowChanged event is triggered. So, an executeQuery is run on the detail datasouce and it overrides all the deleted detail rows. On the commit, just the master row is deleted from your table and you get the integrity error. In this situation, you need to handle the details. What is your use case?

(29 Jun '12, 09:13) Ugur Kocak ♦

OK, that solves it. I delete the master record from a grid. It indeed automatically does an execute query on the detail datasource when master current row changes, and thus overrides the deleted rows.. As i always use views with instead of triggers, i will delete the detail rows when i delete a master row in the instead of trigger of the master view. Thanks Ugur.

(02 Jul '12, 02:21) Michiel A
showing 5 of 7 show 2 more comments

Hi Michiel,

You can delete all the details with ALL_ROWS option:

api_datasource.deleteRow(detailDatasource, api_datasource.ALL_ROWS)

http://theformspider.com/API/api_datasource.html#deleterow(varchar2,number)

link

answered 27 Jun '12, 05:44

Ugur%20Kocak's gravatar image

Ugur Kocak ♦
5616
accept rate: 23%

Hi Ugur,

OK, that works, but when i commit, i get the error:

Error while committing Datasource "aut_usertable_v1": ORA-02292: integrity constraint (AUT_SYS.AUT_UGP_UTE_FK) violated - child record found

Formspider probably tries to commit the master table before the details table. How to work around this?

Thanks, Michiel

(27 Jun '12, 06:27) Michiel A
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×6
×5
×1

Asked: 27 Jun '12, 05:00

Seen: 1,344 times

Last updated: 02 Jul '12, 02:21


© Copyright Gerger 2017