Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Deleting record - UPDATE field or DELETE row according to foreign relations

Posted on 2006-11-17
18
Medium Priority
?
311 Views
Last Modified: 2008-02-01
I've a very simple database (3 table) with some foreign key relation. This is a typical Products-OrderDetails-Order porblem that is defined with the following (partial) code:

/** Products Table **/
CREATE TABLE Products (
      ProductID int IDENTITY(1,1) NOT NULL,
      ProductName nchar(10)  NULL,
      QuantityPerUnit int NULL,
      UnitPrice smallmoney NULL,
      Delete bit NULL)
CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (ProductID)

/** Orders Table **/
CREATE TABLE Orders(
      OrderID int NOT NULL,
      CustomerID int NULL,
      OrderDate datetime NULL,
      ShippedDate datetime NULL)
CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED(OrderID)

/** Order Details **/
CREATE TABLE OrderDetails(
      OrderID int NOT NULL,
      ProductID int NOT NULL,
      UnitPrice money NULL,
      Quantity int NULL)

CONSTRAINT PK_OrderDetails PRIMARY KEY CLUSTERED (OrderID, ProductID)
CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY(OrderID) REFERENCES Orders (OrderID)
CONSTRAINT FK_OrderDetails_Products FOREIGN KEY(ProductID) REFERENCES Products] (ProductID)

If I want to delete a product, I want to mark it as Deleted in Products if there is a reference for it in the OrderDetails table, otherwise I just what to remove it from the Products table.

I wander if there is a better solution than having a stored procedure that would compute something like

SELECT COUNT(*) as prodcount  FROM OrderDetails WHERE ProductID = @ProductID
if (prodcount > 0) {
 UPDATE Products SET Deleted = 1 WHERE ProductID = @ProductID
} else {
  DELETE FROM Products WHERE ProductID = @ProductID
}

I want to know if triggers or any other coding could help in term of speed, maintenance, ...

I'm doing this using C# CLR on SQL Server 2005

Thanks in advance for your help

Y Charbo
0
Comment
Question by:mavrj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
18 Comments
 
LVL 9

Expert Comment

by:dduser
ID: 17963962
You need to write two statements:-

Update Products set Deleted = 1
From OrderDetails where OrderDetails.ProductId= Products.ProductId and ProductId = @ProductId

Delete From Products where ProductId = @ProductId and (Select count(*) from OrderDetail where ProductId = @ProductId) = 0

Regards,

dduser
0
 
LVL 1

Author Comment

by:mavrj
ID: 17963997
I should run the two statments isn't it a little bit too consuming for large table?
0
 
LVL 39

Expert Comment

by:appari
ID: 17964011
you can try instead of triggers.

try something like this

create trigger trg_DeleteProducts
on Products
instead of DELETE
AS
BEGIN
      SET NOCOUNT ON;

if exists(  Select 1 FROM OrderDetails ,deleted WHERE OrderDetails.ProductID = deleted.ProductID)
begin
       UPDATE Products SET Deleted = 1 from deleted  WHERE products.ProductID = deleted.ProductID
END
else
        Delete P from Products P, deleted  WHERE P.ProductID = deleted.ProductID

end

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 39

Expert Comment

by:appari
ID: 17964026
in the sample trigger i posted above i assumed its deleting only one record at a time. if you want to  delete multiple rows you have to change the trigger and may be use cursor inside the trigger and for each product  check if data exists in child tables and either delete or update depending on data existance in child tables.
0
 
LVL 1

Author Comment

by:mavrj
ID: 17964413
What is the impact of using nested queries (in both method)?
What are the advantages of one method against the other one?
Which one will be less error prone when extending the database to more constraint tables.
Which one will provide better scalling up with data, partitioning or other advance techniques?
I heard that cursor are more "dangerous" (in term of implementation, locks, ...) than temporary table. Does this apply to this when deleting mutiple rows?
Try to provide arguments that could apply for production environment.
0
 
LVL 1

Author Comment

by:mavrj
ID: 17964945
dduser,

What do you think of performing the update for every request ie:

UPDATE Products SET Deleted = 1 WHERE ProductID = @ProductID

DELETE FROM Products WHERE ProductID = @ProductID AND (SELECT COUNT(*) FROM OrderDetails WHERE ProductID = @ProductID) = 0
0
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 17965015
or try like this

from your application Update the deleted to 1
and write a trigger on update and within the trigger delete the product if no orderdetails exists, something like this

create trigger trg_UpdProducts
on Products
for Update
AS
begin

Delete Products
from  Products, inserted where WHERE Products.ProductID = inserted.ProductID
and inserted.deleted = 1 and not exists (SELECT OrderDetails.ProductID FROM OrderDetails WHERE OrderDetails.ProductID = inserted.OrderDetails)


end
0
 
LVL 1

Author Comment

by:mavrj
ID: 17965057
Well... This seem to be sub-optimal as the trigger will be fired for every UPDATE event even if there is no reason for it (Data beeing juste updated)

Anyway you didn't argue for triggers vs stored procedure
0
 
LVL 39

Expert Comment

by:appari
ID: 17965150
>>This seem to be sub-optimal as the trigger will be fired for every UPDATE event even if there is no reason for it (Data beeing juste updated)

i dont think so, by using trigger you can guarantee the data consistancy. even if you write a Procedure there will be no guarantee that all users do execute the SP. if they update the products table directly delete statement is never executed. if you write it in trigger from whatever application or tool once deleted is set to 1 delete logic will be executed automatically.
0
 
LVL 1

Author Comment

by:mavrj
ID: 17965367
Good point!

The main point is to know what type of access you want to allow users/application to have on your data. You can always find a way to loose data consistency if the server is accessed directly ;-). You point a way to ensure data consistency with triggers but in order to have guarenteed consitency you must inplement both for UPDATE and INSTEAD OF DELETE triggers... right?
Is there any problem of circular triggers firing?
The coding impact could be relatively important if adding new constraint on the ProductID field.

By the way, I don't challenge your knowledge by my answers... but I've got some partial knowledge on this subject and I want to understand all the  issues of using triggers. The application I'm in charge would be quite bigger than this simple example and I don't want to make any choice I could regret later. This UPDATE or DELETE question will be one of the underlying problem of keeping the database consistent for achives and not populated with "dead" records
0
 
LVL 39

Expert Comment

by:appari
ID: 17965409
>> in order to have guarenteed consitency you must inplement both for UPDATE and INSTEAD OF DELETE triggers... right?
no need to have instead of trigger on delete.
Update trigger is enough. you already have FKey defined so as long as records in child tables exists delete from parent table will throw error.
0
 
LVL 1

Author Comment

by:mavrj
ID: 17965436
Do I have to put special constraint on FKey?
0
 
LVL 39

Expert Comment

by:appari
ID: 17965466

no need, constraints defined as in your first post are enough.
0
 
LVL 1

Author Comment

by:mavrj
ID: 17965500
One last question...

Do you have any Idea coding this using CLR?
0
 
LVL 39

Expert Comment

by:appari
ID: 17965524
No, not tried yet:)
I think i will get a chance to try this in our next project starting next month.
0
 
LVL 1

Author Comment

by:mavrj
ID: 17965579
Good Luck...
I've juste discovered CLR and I'm using Stored Procedure CLR.
They are quite interesting and allows powerfull refactoring for redundancy that append frequently in SQL scripts.
0
 
LVL 1

Author Comment

by:mavrj
ID: 17965824
For your information CLR implementation of the trigger is straight forward:

[Microsoft.SqlServer.Server.SqlTrigger(Name = "trg_UpdateProduct", Target = "Products", Event = "FOR UPDATE")]
    public static void trg_UpdateProduct()
    {
        using (SqlConnection connection = new SqlConnection("context connection = true"))
        {
            connection.Open();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = @"DELETE Products FROM  Products, inserted
                                    WHERE Products.ProductID = inserted.ProductID
                                        AND inserted.Deleted = 1
                                        AND NOT EXISTS (SELECT OrderDetails.ProductID
                                                        FROM OrderDetails
                                                        WHERE OrderDetails.ProductID = inserted.ProductID)";
            command.ExecuteScalar();            
        }
    }
0
 
LVL 39

Expert Comment

by:appari
ID: 17965843

thank you for the information.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question