Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-11-17
18
Medium Priority
?
315 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

885 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