How to avoid deletion of master table data which is used in transaction table in sql server 2008

Question

0.00/5 (No votes)

See more:

, +

Hi Everybody,

please help me i am creation small ERP application that contains some masters like item,unit,category,item type each have their table and i am using this table reference data in my transaction like purchase module that contains item name,unit name,category name saving their ids in my purchase inquiry table but when i am going to delete an item in master table then i will modify the purchase inquiry data, it will shows me an error the data of item is not available for modification. i am also using the foreign key constraints but i will not work.

my main problem is to how to show user don't delete the data of master when it is used in transaction forms.

Posted 26-Sep-13 1:58am

GaneshKale22

Updated 26-Sep-13 3:44am

coded007

v2

Add a Solution

Comments

coded007 26-Sep-13 9:45am

pls post your code to help you

safzal1212 26-Sep-13 11:02am

When you have parent child relationship between two tables sql server generate an error if you try to delete data from master and it has referenced data in child table unless you do the foreign key with CASCADE DELETE. So do you want to prevent the deletion of data from Master table or want to do something else?

3 solutions

Solution 2

When creating the foreing key constraints you're usually able to select what should happend on 'child' table column values in case of 'master' table row deletion. I don't recall now in the case of SQLServer, but in MySql you can set the action to RESTRICT. This way it throws an error when trying to delete one row on the master table if rows referencing it exist on the child tables. That ensures database consistency.

For not showing nasty error messages you may modify your code so you make the checks before proceeding to delete/modify the master table rows.

Posted 26-Sep-13 4:58am

V.Lorz

Solution 3

When you have parent child relationship between two tables sql server generate an error if you try to delete data from master and it has referenced data in child table unless you do the foreign key with CASCADE DELETE. So do you want to prevent the deletion of data from Master table or want to do something else?
and you can display the message from try catch to ui .....

Posted 26-Sep-13 5:20am

Manoj Kumar Choubey

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Accepted Answer · 2013-09-26T04:38:00

Solution 1

Logic may be like below

Inside the Stored Procedure, before the DELETE operation on Master Table, check whether there are any data inside the Referenced Table PurchaseInquiry.

Something like below... (example for Item Table)

SQL

IF EXISTS(SELECT col1 
          FROM PurchaseInquiry 
          WHERE ItemName = 'ItemNameWhichUserIsDeleting')
BEGIN
    -- Return a message that User can't delete the Item.
END
ELSE
BEGIN
    -- DELETE the Item.
END

Posted 26-Sep-13 4:38am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Comments

GaneshKale22 26-Sep-13 23:54pm

Thank you!!!! friend
Tadit Dash it is working fine.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 27-Sep-13 1:27am

Most Welcome GaneshKale22. :)

Thanks,
Tadit