Conditional FK Deletes

How would I use a Foreign Key to prevent deletions on the parent table?

For example, I have an Orders table and an OrderCancels table related by a FK on the iOrdID. When the app requests to delete a record on the Orders table, I need to check the OrderCancels table via the iOrdID FK for corresponding records, and if there are, not delete the order.I am a little confused as the foreign key prevents exactly that - deleting a parent record that has a child. If you application attempts to delete a parent record that has child records enforced using a foreign key constraint then the application will receive an error from sql server which will tell it that the delete was unsuccessful.|||Let me clarify - I know you can check 'Cascade deletes' in the SQL GUI so that if you delete a parent, the child gets deleted as well. This is how all of our current FKs work.

Are you saying that if I want to check for children and cancel the parental deletion if the children are found, all I have to do is uncheck 'Cascade Deletes' on the FK? If so, how do I capture this error and return it to the app.

Sorry if all this seems obvious, but I am a very green DBA...

TIA,

-Justin|||You never mentioned cascading - this is very important.

The answer is yes. SQL Server automatically sends this error to the calling application.

Take a look at the books online "Cascading Referential Integrity Constraints" article.

Conditional Deletion in Replication

I am developing a transactional replication plan. I have one table
that most of the time I do not want to replicate deletions. There is
an exception to this rule. If the status_id of another table is false,
then the deletion should be replicated.
I have been having difficulty trying to put this condition in place. I
have tried modifying the sp_MSdel_dboTable stored procedure. I have
removed the if @.@.rowcount check and replication errors with the
messages:
-- Error executing a batch of commands. Retrying individual commands.
Eventually, it will fail with the message: if @.@.trancount > 0 rollback
tran
Any ideas on how to get around this issue?
Publisher: SQL 2000
Distributor: On a separate server
Subscriber: SQL 2005
Thanks for any help I can get.
You need to replicate an indexed view as a table on the subscriber. The
indexed view would only "show" rows which match the status_id in the other
table.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"emreeves" <em_reeves@.lycos.com> wrote in message
news:1143754411.192208.291820@.i40g2000cwc.googlegr oups.com...
>I am developing a transactional replication plan. I have one table
> that most of the time I do not want to replicate deletions. There is
> an exception to this rule. If the status_id of another table is false,
> then the deletion should be replicated.
> I have been having difficulty trying to put this condition in place. I
> have tried modifying the sp_MSdel_dboTable stored procedure. I have
> removed the if @.@.rowcount check and replication errors with the
> messages:
> -- Error executing a batch of commands. Retrying individual commands.
> Eventually, it will fail with the message: if @.@.trancount > 0 rollback
> tran
> Any ideas on how to get around this issue?
>
> Publisher: SQL 2000
> Distributor: On a separate server
> Subscriber: SQL 2005
> Thanks for any help I can get.
>
|||Thanks! This sounds better than the next approach I was going to
try... creating a 1st Insert/Update trans replication, and a 2nd Delete
only with row filtering.
BTW - Thanks very much for your replication book. It has helped loads
this past few weeks.