September 4, 2001 at 4:33 am
Please try this:
1 - use Northwind
2 - Delete FK Constraints from orderdetails table reference products and orders tables
3 - Create view to have:
4 - orderdetails.OrderID, orderdetails.ProductID, orderdetails.ProductName
5 - Run this view
6 - Delete a record. Before delete write orderid and productid values on a paper
7 - Try to find the productID in the Products table and also OrderID in the Orders Table
YOU WILL NOT FIND ANY .....!!!!!!!
8 - Try all the 7 steps using Access and you will FIND the product record and the Order record each in its table.
Please Till me What is wrong with me or with sql server??
by the way, I tried MSDE , sql server 7, sql server 2000 and all of them give this nonsense.
ONLY Access gives me the right answer
Thanks,
Mohamed Maher
MCSD
Mohamed Maher
Mohamed Maher
September 4, 2001 at 5:19 am
How are you deleting, from the EM view pane, or with a TSQL delete statement?
September 4, 2001 at 10:55 am
quote:
How are you deleting, from the EM view pane, or with a TSQL delete statement?
[Reply]
I used EM view pane, but when you bind the sql statement generated from the view to a data grid using VB project the deletion happens to products table itself and also orders table dispite the fact there is still order details refrences the deleted product and also the deleted order. In Access everything is just fine.
[/Reply]
Edited by - Maher on 09/04/2001 11:11:24 AM
Mohamed Maher
September 4, 2001 at 3:02 pm
How are you getting the ProductName column? You'd have to do a join between Order Details table and the Product table; correct? Unless your view is in turn accessing another view...
Matthew Burr
September 5, 2001 at 4:25 am
quote:
How are you getting the ProductName column? You'd have to do a join between Order Details table and the Product table; correct? Unless your view is in turn accessing another view...Matthew Burr
The sql script generated from the view whcih i delete from it using EM is as follows:
SELECT dbo.[Order Details].OrderID, dbo.[Order Details].ProductID, dbo.Products.ProductName, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity
FROM dbo.[Order Details] INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID
So, from EM-view pane: create new view then add [order details] table and Products table (note the relation will be generated) then choose the fields in the above script then press the ! button and delete any row (means you delete one item from an order). after that find the the orderid in orders table and also the productid in the products table, they are gone!
Try exactly the same in Access and you will see everything is OK
Mohamed Maher
Mohamed Maher
September 5, 2001 at 2:23 pm
Well, here's the behaviour I'm seeing. I created a view, using the information that you provided. I then attempted to delete a single row from that view using Query Analyzer. I received an error indicating that I could not do this, since the view involved multiple tables. I then used Enterprise Manager to delete the row, and EM allowed me to delete it without any problem. I checked the Product table and found that the corresponding Product had also been deleted from that table, but when I checked the Order table I found that the corresponding Order was still in that table, so the behaviour that I saw was somewhat similar to what you're experiencing, but only in part. From this behaviour I conclude that EM is capable of resolving delete statements involving multiple tables in a view. In other words, Enterprise Manager was able to determine that since certain columns in your view come from the Product table, it was able to fulfill your delete request by deleting the corresponding row in the Product table. Other interfaces to your view, such as Query Analyzer or Access, do not have this functionality, and they behave in a fashion somewhat similar to what you were expecting.
The question remains, though, why did the Order also get deleted in the Order table?
I can't answer that, perhaps someone else can. A couple of things that come to mind that you may want to check:
Good luck,
Matthew Burr
September 8, 2001 at 4:15 am
quote:
Well, here's the behaviour I'm seeing. I created a view, using the information that you provided. I then attempted to delete a single row from that view using Query Analyzer. I received an error indicating that I could not do this, since the view involved multiple tables. I then used Enterprise Manager to delete the row, and EM allowed me to delete it without any problem. I checked the Product table and found that the corresponding Product had also been deleted from that table, but when I checked the Order table I found that the corresponding Order was still in that table, so the behaviour that I saw was somewhat similar to what you're experiencing, but only in part. From this behaviour I conclude that EM is capable of resolving delete statements involving multiple tables in a view. In other words, Enterprise Manager was able to determine that since certain columns in your view come from the Product table, it was able to fulfill your delete request by deleting the corresponding row in the Product table. Other interfaces to your view, such as Query Analyzer or Access, do not have this functionality, and they behave in a fashion somewhat similar to what you were expecting.The question remains, though, why did the Order also get deleted in the Order table?
I can't answer that, perhaps someone else can. A couple of things that come to mind that you may want to check:
- Are there any triggers on the Order Details table?
- Are there any triggers on the View?
Good luck,
Matthew Burr
Actually I used the famous Northwind without any modification from myself to be well known for discussion. There are no triggers on Orders, Order Details, Products tables.
Let me tell you about how I discovered this problem. I created an ADO recordset with Static cursor and Optimistic Locking, then I used just the same sql statement which I used in the view above, after that I make this recordset as a data source of a DBGrid. Finaly, I deleted a row from that Grid and I discovered this problem. All the previous steps were on my database not on the northwind database, so I desided to try the same on a standard databse like Northwind to be easily discussed with others.
Thank you all for your help, your earliest reply will be highly appreciated.
Mohamed Maher
Mohamed Maher
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply