TR_ORGANIZATIONDATA_INSERTUPDATE

    CREATE trigger TR_ORGANIZATIONDATA_INSERTUPDATE on dbo.ORGANIZATIONDATA for update, insert
    as begin
        set nocount on

        -- 9/11/12 CEV - constituent relation merge, do not change history

        -- Previously had disabled trigger in merge but this can cause a schema lock (WI 231248)

        begin try
            declare @MERGEWELLKNOWNGUID uniqueidentifier = '3DB5F72B-2BA0-45A0-890F-24359E3F42A8';
            if cast(CONTEXT_INFO() as uniqueidentifier) = @MERGEWELLKNOWNGUID
                return;
        end try
        begin catch
        end catch

        if update(PARENTCORPID)
        begin
            declare @CURRENTDATE datetime = getdate()
            declare @CHANGEAGENTID uniqueidentifier
            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

            declare @CONTEXTCACHE varbinary(128)
            set @CONTEXTCACHE = CONTEXT_INFO()

            if @CHANGEAGENTID is not null 
                set CONTEXT_INFO @CHANGEAGENTID

            delete OPH
            from dbo.ORGANIZATIONPARENTHISTORY OPH
                inner join inserted i on OPH.CHILDCORPID = i.ID
                left join deleted d on i.PARENTCORPID = d.PARENTCORPID
            where OPH.STARTDATE = cast(@CURRENTDATE as date)
                and d.PARENTCORPID is null

            if not @CONTEXTCACHE is null 
                set CONTEXT_INFO @CONTEXTCACHE

            update OPH
            set OPH.ENDDATE = DateAdd("d", -1, @CURRENTDATE),
                OPH.DATECHANGED = @CURRENTDATE,
                OPH.CHANGEDBYID = @CHANGEAGENTID
            from dbo.ORGANIZATIONPARENTHISTORY OPH
                inner join deleted d on d.ID = OPH.CHILDCORPID and d.PARENTCORPID = OPH.PARENTCORPID
                left join inserted i on d.PARENTCORPID = i.PARENTCORPID
            where i.PARENTCORPID is null
                and (not d.PARENTCORPID is null)
                and OPH.ENDDATE is null

            insert into dbo.ORGANIZATIONPARENTHISTORY(ID, CHILDCORPID, PARENTCORPID, STARTDATE, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
            (select newid(),
                    i.ID,
                    i.PARENTCORPID,
                    cast(@CURRENTDATE as date),
                    @CURRENTDATE,
                    @CURRENTDATE,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID
             from inserted i
                left join deleted d on i.ID = d.id
             where (d.PARENTCORPID <> i.PARENTCORPID
                    or d.PARENTCORPID is null)
                and (not i.PARENTCORPID is null))
        end
    end