January 6, 2010 at 9:21 am
I have a Cascade delete foreign key set up on table A that references table B. I also have a proc that performs a delete on Table B. This is successfully cascaded down to Table A. Here's the problem. This delete proc is called from 2 different procs. In both cases, the record in Table B is deleted, but not in TableA. They both are calling the same procedure and there is only one parameter to pass in. There is no logic in the delete proc for it to fall through. It literally is delete from TableB where field = parameter. Any ideas?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 6, 2010 at 9:39 am
Mike01 (1/6/2010)
I have a Cascade delete foreign key set up on table A that references table B. I also have a proc that performs a delete on Table B. This is successfully cascaded down to Table A. Here's the problem. This delete proc is called from 2 different procs. In both cases, the record in Table B is deleted, but not in TableA. They both are calling the same procedure and there is only one parameter to pass in. There is no logic in the delete proc for it to fall through. It literally is delete from TableB where field = parameter. Any ideas?
The idea behind cascading deletes is that you delete from the key table (A) and any records relating to this key defined by the foreign keys will also be deleted in table B. This ensures you don't have orphaned records.
It won't work the other way as you're not cascading deletes down, more like trying to cascade deletes up. If you want to enforce this sort of behaviour i suggest you use a trigger.
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 6, 2010 at 9:44 am
I explained it wrong. I am deleting from the parent table and expecting a cascade delete on the child table. But this was a PICNIC (problem in chair not in computer). thanks anyway 🙂
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply