USP_EDUCATIONALHISTORY_DELETE

Executes the "Educational History: Delete" record operation.
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the ID of the record being deleted.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the delete.
Definition

Copy


                    CREATE procedure dbo.USP_EDUCATIONALHISTORY_DELETE
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier
                    )
                    as
                        set nocount on;

                        if (select
                                count(NONPRIMARY.ID)
                            from
                                dbo.EDUCATIONALHISTORY as TODELETE
                            left join dbo.EDUCATIONALHISTORY as NONPRIMARY on TODELETE.CONSTITUENTID = NONPRIMARY.CONSTITUENTID
                            where
                                TODELETE.ID = @ID
                                and TODELETE.ISPRIMARYRECORD = 1
                                and NONPRIMARY.ISPRIMARYRECORD = 0 ) > 0
                        begin
                            raiserror ('ERR_PRIMARY_EDUCATIONALHISTORY_DELETE',13,1);
                            return 0;
                        end

                        declare @EDUCATIONALINSTITUTIONID uniqueidentifier
                        declare @CONSTITUENTID uniqueidentifier

                        select  @CONSTITUENTID = CONSTITUENTID,
                                @EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTIONID
                        from dbo.EDUCATIONALHISTORY where ID = @ID

                        if (select
                                COUNT(1)
                            from
                                dbo.[DESIGNATIONLEVELRECIPIENT]
                            where
                                [DESIGNATIONLEVELRECIPIENT].[CONSTITUENTID] = @CONSTITUENTID
                                and [DESIGNATIONLEVELRECIPIENT].[EDUCATIONALHISTORYID] = @ID) > 0
                        begin
                            raiserror ('ERR_EDUCATIONALHISTORY_DESIGNATIONRECIPIENT_DELETE',13,1);
                            return 0;
                        end

                        --If the record deleted is the only reference to an educationalinstitution used in an involvement, update the involvements educationalinstitution field to null

                        if (select count(EDUCATIONALINSTITUTIONID) from dbo.EDUCATIONALHISTORY 
                            where CONSTITUENTID = @CONSTITUENTID 
                            and   EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID) = 1 and exists (select ID from dbo.EDUCATIONALINVOLVEMENT where CONSTITUENTID = @CONSTITUENTID and EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID)
                        begin

                          update dbo.EDUCATIONALINVOLVEMENT set EDUCATIONALINSTITUTIONID = null
                          where EDUCATIONALINSTITUTIONID =  @EDUCATIONALINSTITUTIONID 

                        end

                        exec dbo.USP_EDUCATIONALHISTORY_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
                        return 0;