如何从父表中删除记录，以及如何在删除父表时首先删除与子表关联的记录. [英] how to delete records from parent table and while deleting it should first delete records associated in child table first.

问题描述

请帮我创建一个过程，其中有两个表，一个是父表，另一个是子表.因此，如果我从父表中删除内容，则在删除内容时应先删除与子表关联的记录，然后再删除父表.在过程中，我必须使用开始事务，回滚和提交，就好像子表中的记录之一(如果它处于编辑模式)，那么在删除记录时不能b删除，因为如果它不在编辑模式中应该回滚，那么它应该b提交.

并请帮助我如何测试它是否处于编辑模式..在此先感谢...

解决方案

而不是创建一个过程，我建议您可以在父级表上创建触发器. AFAIK，这是最可靠的方法，可确保在删除父行时删除子行.而且它默认情况下尊重交易:)

嗯....现在，您写道:
我制作了一个可以删除我想要的父记录和子记录的sp，但是现在的问题是测试sp，因为im无法锁定记录，请考虑以下方案:如果我锁定一个，则父记录现在在孩子中包含三个记录记录，然后删除事务回退，因此在这种情况下如何锁定子记录中的某一行，请为此提供帮助.

因此，据我了解(请cmiiw)，您已经创建了SP，但是如果SP的任何子级被锁定或正在编辑，则希望SP取消删除.对?好的.您如何锁定"子行?我认为最好在子级上添加一列-例如IsLocked(bit)-如果该行处于编辑模式"，则将其设置为1.如果这些应用程序尚未处于编辑模式，则这些应用程序应将该位更新为0.在删除SP上，您可以检查是否存在任何已打开IsLocked的子级.如果是，则跳过删除.如果不是(所有孩子的"IsLocked"均已关闭)，则SP可以进行删除.我的意思是这样的:

 如果 不 存在(选择 * 从子级其中 IsLocked =  1 )
   - -不存在；然后删除父母和孩子:
   开始
       删除 从父级删除
        其中 key_col =  @ Key_of_row_tobe_deleted 
    
       删除 从子级删除
        其中 foreign_key =  @ Key_of_row_tobe_deleted 
   结束

- -否则，都不删除，换句话说，跳过删除 

hth，
foxyland

使用级联删除.如果删除父表中的一行，则自动删除属于子表的行.

Hay vins555
您可以选择在删除父记录时删除子记录
在给出两个表之间的关系时很简单，您将关系表示为 cascade 即可.这就是当您删除父记录时它会自动删除子记录


希望对您有帮助......

hi please help me to create a procedure in which there are two tables one is parent table and other is child table. so if i delete content from parent table then while deleting it should first delete the records associated with child table first and then the parent table. in procedure i have to use begin transaction, rollback and commit as if one of the records from child table if it is in edit mode then while deleting the records can not b deleted as it should be rollback if its not in edit mode then it should b commit.

and please help me how to test the if it is in edited mode.. thanks in advance...

解决方案

Rather than creating a procedure, I''d suggest you create a trigger on parent''s table. AFAIK, that''s the most trusted way to ensure that the child rows would be deleted whenever the parent row is deleted. And it respect transaction by default :)

hmm.... Okay. Now, you wrote:
i made an sp that works delete both parent and child records i want but now the problem is testing the sp as i m unable to lock records lets consider a scenario in which record from parent contains three records in child now if i lock one of the recorfd then the delete transaction shud b rollback so in this case how to lock one of the row from child record please help me for that.

So, as far as I understand (please cmiiw), you already created the SP, but you want the SP to cancel the deletion if any of its child is locked or being edited. Right? Ok. How do you ''lock'' the child row? I think it''s better if you add one column on the child -- say, IsLocked (bit) -- and set it to 1 if the row is ''in edit mode''. If the the apps already not in the edit mode, then the apps should update the bit to 0. On the delete SP, you can check if there is/are any child that has IsLocked turn on. If it is, then skip the deletion. If not (all child''s IsLocked is off) then the SP can do the deletion. I mean something like this:

If not exists (Select * from child where IsLocked = 1)
   --- not exists; then delete the parent and the child:
   Begin
       delete from parent
        where key_col = @Key_of_row_tobe_deleted
    
       delete from child
        where foreign_key = @Key_of_row_tobe_deleted
   End

--- otherwise, delete neither, in other words skip the deletion

hth,
foxyland

USe cascading delete .if a row in parent table is deleted ,then autoamtically row belonging to child table deleted.

Hay vins555
you have the option to delete the child record when parent record is deleted
It simple while giving relation between two table you give the relation as cascade That''s all when u delete the parent record it automatically deletes the child record


Hope it will help you.....

这篇关于如何从父表中删除记录，以及如何在删除父表时首先删除与子表关联的记录.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！