update statement in sql server 2005

Question

Consider the following Dig, Assume that all the three tables have a column Is_Deleted by default it is set to 0... I want to update Is_Deleted=1 field of Customers table where CustId=2 only when the rows containing CustId=2 and Is_Deleted=1 in Orders and OrderItems Tables... I dont want to use Cascade option.. Any suggestion

Answer 1

Ok sounds fine... Do we only set the deleted flag when all the orders and all the order items associated with that customer are deleted, or only if at least 1 item is deleted.

Answer 2

Easiest way is EXISTS. I assume you want to check both Orders and OrderItems. This also means you only filter on CustID once.

UPDATE
   C
SET
   IsDeleted = 1
FROM
   Customers C
WHERE
   C.CustID = 2
   AND
   EXISTS (SELECT *
        FROM
            Orders O
        WHERE
            O.CustID = C.CustID AND O.IsDeleted = 1)
   AND
   EXISTS (SELECT *
        FROM
            Orders O
            JOIN
            OrderItems OI ON O.OrderID = OI.OrderID
        WHERE
            O.CustID = C.CustID AND OI.IsDeleted = 1)

Answer 3

You can make the use of Triggers on Table - Customers

You can get details about triggers :

• http://msdn.microsoft.com/en-us/magazine/cc164047.aspx
• http://msdn.microsoft.com/en-us/library/aa258254%28SQL.80%29.aspx

With Trigger you can check the Value of Updated column and depending on it you can update the data of different tables.