Solved

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

Posted on 2006-11-17
18
284 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
  • 9
  • 8
18 Comments
 
LVL 9

Expert Comment

by:dduser
Comment Utility
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
Comment Utility
I should run the two statments isn't it a little bit too consuming for large table?
0
 
LVL 39

Expert Comment

by:appari
Comment Utility
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
 
LVL 39

Expert Comment

by:appari
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:mavrj
Comment Utility
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
Comment Utility
>> 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
Comment Utility
Do I have to put special constraint on FKey?
0
 
LVL 39

Expert Comment

by:appari
Comment Utility

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

Author Comment

by:mavrj
Comment Utility
One last question...

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

Expert Comment

by:appari
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

thank you for the information.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now