June 12, 2006 at 8:30 am
I have a database which the admin guy is complaining that when he is deleting a row out of a certain table in EM it is also deleting related rows out of other tables if he has them open at the same time. The thing is that the table he is talking about is a child table and he is saying the when he deletes the child record the parent record is being deleted.
I have checked that there are no cascade deletes on the foreign keys but I would like an sql script to run that will show me all the foreign key relationships I have in the db if pos. I have tried searching for one but to no avail.
I have tried to replicate the problem on my own PC and there doesn't seem to be a problem and Im sure that there isn't but Mr Admin is having none of it so I want to be sure that hes trippin out and not me.
Many thanks in advance
June 12, 2006 at 10:48 am
Even if there are no Cascades deleted, there might still exist triggers!
N 56°04'39.16"
E 12°55'05.25"
June 12, 2006 at 11:06 am
There are no triggers.
I wrote the DB so I know that there are no triggers.
And I also know that there aren't cascading deletes as I didn't set any up unless someone else did whilst my back is turned.
I just need some sql to display all the relationships between certain tables if possible so I can prove that he is doing something wrong as theres nothing in the code and he will not have it that its anything to do with him.
June 12, 2006 at 11:18 am
See http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=234068
N 56°04'39.16"
E 12°55'05.25"
June 13, 2006 at 2:25 am
Nice 1 cyril thats exactly what I needed.
Thanks for help!
June 13, 2006 at 6:45 am
What was the solution?
N 56°04'39.16"
E 12°55'05.25"
June 13, 2006 at 7:12 am
hi,
try this
select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
June 13, 2006 at 7:22 am
Yes thats even better.
I was trying to look for that view but whoever set the DB has hidden them and I couldn't find it.
That sql shows the update/delete action and therefore I could prove that there was no cascade delete happening as there are no triggers used.
He admits it must have been him now as I've sat with him on my box and stepped through exactly what he said he was doing and it wasn't deleting related records. I don't know what he was doing wrong but I can now show him that there is no hidden action happening behind the scenes so it must be human error.
Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply