使用触发器实现级联删除

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER  TRIGGER [RemoveUserAndPerson] ON [dbo].[rc_user]
    INSTEAD OF DELETE
    AS
   
    /* 定义触发器使用的变量 */
    DECLARE
    @uid int,
    @uidCount Int
   
   
   
    /* 把传送的需要删除的uID键值赋值给@uID变量 */
    /* 开始事务 */
    BEGIN TRAN Remove_UserPerson
    Set @uid = (Select u_id From deleted)
    /*Set @fTopName = (Select fTopName From deleted)*/
   
    /* 保存删除前保存点，防止出错 */
    Save Tran my_Save1
   
    /* 首先判断子类表rc_person中是否有所属内容 */
    Set @uidCount = (Select Count(*) From rc_person Where rc_person.u_id = @uid)
    If @uidCount > 0
  
    Begin
    Delete From rc_person Where u_ID = @uID
    Delete From rc_user Where u_ID = @uID
    End
   
    Else
    Begin
    Delete From rc_user Where u_ID = @uID
    End
   
    If @@Error = 0
    Commit Transaction
    Else
    Begin
    Rollback Transaction my_Save1
    Raiserror('删除出现错误，记录：%s及其所属内容没有被删除。',16,1,@uid)
    End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

转载于:https://www.cnblogs.com/cnkenny/archive/2008/01/24/1051457.html