TR_FundRaiserSolicitors_CASCDE_DEL_1

                  CREATE  TRIGGER [dbo].[TR_FundRaiserSolicitors_CASCDE_DEL_1] ON [dbo].[FundRaiserSolicitors]
    INSTEAD OF DELETE
    AS
    SET NOCOUNT ON
    DECLARE @Count integer
    Select @Count=Count(*) from deleted
    if @Count>0
    BEGIN

        --solicitors reporting to solicitors currently not supported
        --DELETE FROM FundRaiserSolicitors WHERE ReportsToID IN (SELECT ID FROM deleted)

        DELETE FROM UserPersonalPages WHERE ParentID IN (SELECT ID FROM deleted)

        DELETE FROM FundRaiserEventRegistrations WHERE SolicitorID IN (SELECT ID FROM deleted)

        DELETE FundRaiserDonations WHERE SolicitorID IN (SELECT [ID] FROM deleted)

        DELETE FundraiserSolicitorEmail WHERE FundRaiserSolicitorsID IN (SELECT [ID] FROM deleted)

        DELETE FundraiserSolicitorContact WHERE FundraiserSolicitorsID IN (SELECT [ID] FROM deleted)

        DELETE Comments WHERE SolicitorID IN (SELECT [ID] FROM deleted)

        UPDATE FundRaiserTeams SET
            CaptainID = NULL 
            WHERE CaptainID IN
            (SELECT ID FROM deleted)    

        UPDATE FundRaiserSolicitors SET
            RecruiterID = NULL 
            WHERE RecruiterID IN
            (SELECT ID FROM deleted)    

        -- reissue the delete
        DELETE FROM FundRaiserSolicitors
            WHERE ID IN 
            (SELECT ID FROM deleted)

    END