Tuesday, February 5, 2013

Delete Actions

The DeleteAction element helps maintain database consistency when a record is deleted.
Define delete actions to specify what should occur when data being deleted in the current table is related to data in another table.

Best Practices:
Have a delete action on every relation between two tables.
Use table delete actions instead of writing code to specify whether deletes are restricted or cascaded.

Types of Delete Actions:

1. None: Delete action disabled.

2. cascade: Deletes related records.
Ex: On the CustTable table, a cascading delete action has been defined for the CustBankAccount table. When a customer is deleted from the CustTable table, the delete method also ensures that the corresponding bank account information is automatically deleted.

3. restricted: Restricts deletion in the current table if data is present in related tables.
Ex: On the CustTable table, a restricted delete action has been defined for the CustTrans table. When a customer is deleted in the CustTable table, the validateDelete method ascertains whether transactions exist for the customer in the CustTrans table. If so, validateDelete returns false.


4. cascade+restricted: Cascade the delete, even though records exist on related tables.


Ex: we have three tables  Person ,Customer & Order
Now Person is a parent of Customer table, and Customer is a parent of Order table having (One-To-Many) relations.
If I set a Delete action property on Person table to “CASCADE” for customer table  and If I set a  Delete Action property on a CUSTOMER table for Order table to “CASCADE +RESTRICTED”.
So if I delete a record from Customer table then It will first check the record in the child table(order table) and if exist that warning prompt saying that first we need to delete a record from child table.
But if I delete a record from Person table it will automatically delete a record in Customer table and all records related to customer table in Order table would also be deleted.
The Cascade+Restricted delete action is used in the standard application for LedgerJournalTrans on LedgerJournalTable.

This type of delete action is useful when you prefer a total clean-up—when you delete a customer, you also delete all the transactions associated with that customer.


How to Create a Delete Actions:

http://msdn.microsoft.com/EN-US/library/bb315018.aspx

No comments:

Post a Comment

update_recordset with joins

 update_recordset with joins update_recordSet storeTransfer         setting      TransactionId = transfertable.TransferId     join transfert...