TR_CATEGORYDEFINITION_INSUPD_VALIDATE

CREATE trigger TR_CATEGORYDEFINITION_INSUPD_VALIDATE
on dbo.CATEGORYDEFINITION for insert, update
as
-- Check for overlapping categories

if update(TOCODE) or update(FROMCODE)
begin
  declare @COUNT int;
  begin try;
    with CTE (ID, FROMCODE, TOCODE)
    as (
    select
      coalesce(inserted.ID, CD.ID) as ID,
      coalesce(inserted.FROMCODE, CD.FROMCODE) as FROMCODE,
      coalesce(inserted.TOCODE, CD.TOCODE) as TOCODE
    from dbo.CATEGORYDEFINITION CD
    full outer join inserted on CD.ID = inserted.ID
    )
    select
      @COUNT = count(1)
    from
      inserted t1 inner join CTE t2
      on t1.ID != t2.ID and
      convert(bigint,nullif(t1.FROMCODE,'')) <= convert(bigint,nullif(t2.TOCODE,'')) and 
      convert(bigint,nullif(t1.TOCODE,'')) >= convert(bigint,nullif(t2.FROMCODE,''))
    ;
    if @COUNT > 0
      raiserror('ERR_CATEGORYDEFINITION_OVERLAP',13,1);
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
  end catch
end