USP_EVENT_VALIDATEEVENTDELETE

            CREATE procedure dbo.USP_EVENT_VALIDATEEVENTDELETE
            (
                @EVENTID uniqueidentifier
            )
            with execute as caller
            as            
                if @EVENTID is not null begin
                    --Events cannot be deleted if they have supporting events

                    if exists(select ID from dbo.EVENT where EVENT.MAINEVENTID = @EVENTID)
                        raiserror('The event cannot be deleted because it has one or more supporting events.',13,1);                

                    if exists(select 1 from dbo.EVENTHIERARCHY where EVENTHIERARCHY.ID = @EVENTID)
                    begin
                        declare @HIERARCHYPATH hierarchyid;
                        select
                            @HIERARCHYPATH = EVENTHIERARCHY.HIERARCHYPATH
                        from
                            dbo.EVENTHIERARCHY
                        where
                            EVENTHIERARCHY.ID = @EVENTID;

                        if exists(select 1 from dbo.EVENTHIERARCHY where EVENTHIERARCHY.HIERARCHYPATH.GetAncestor(1) = @HIERARCHYPATH)
                            raiserror('ERR_EVENT_CANNOTDELETE_HASCHILDREN',13,1);
                    end

                    --Supporting events must be removed from the registration package

                    if exists(select 
                                    REGISTRATIONPACKAGEPRICE.ID 
                                from 
                                    dbo.REGISTRATIONPACKAGEPRICE 
                                    inner join dbo.EVENTPRICE on EVENTPRICE.ID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
                                where 
                                    EVENTPRICE.EVENTID = @EVENTID)
                        raiserror('The event cannot be deleted because it exists on one or more registration packages.',13,1);        

                    --Ensure Phoenix events are not deleted by this record operation

                    if exists(select ID from dbo.EVENT where EVENT.ID = @EVENTID and PROGRAMID is not null)
                        raiserror('This event cannot be deleted by this action.',13,1);
                end