ALTER TRIGGER "tdb_mt_kanne" before delete order 1 on
DBA.mt_kanne
referencing old as old_name
for each row
begin
  declare li_count integer;
  declare li_isdoknr integer;
  declare li_olddoknr integer;
  declare li_objnr integer;
  declare li_skoop integer;
  update "tm_id" set "id" = if "tm_id"."id" > old_name."id" then old_name."id" else "tm_id"."id" endif where "tm_id"."tabel"='mt_kanne' and "tm_id"."aasta" = old_name.aasta;
  select mt_obj.doknr into li_isdoknr from mt_obj where mt_obj.objnr = old_name.objnr;
  if li_isdoknr = 1 then
    select count(*) into li_count from mt_kanne where mt_kanne.doknr = old_name.doknr and mt_kanne.aasta = old_name.aasta and mt_kanne.objnr = old_name.objnr and mt_kanne.sisse = old_name.sisse;
    if li_count = 1 then
      set temporary option CONVERSION_ERROR = 'OFF';
      set li_olddoknr=convert(integer,old_name.doknr);
      set temporary option CONVERSION_ERROR = 'ON';
      if li_olddoknr > 0 then
        set li_objnr = old_name.objnr;
        select if old_name.sisse = 1 then mt_obj.mindoknrs else mt_obj.mindoknrv endif into li_isdoknr from mt_obj where mt_obj.objnr = li_objnr;
        if li_isdoknr >= 0 then
          select mt_yksus.skoop into li_skoop from mt_yksus where mt_yksus.yksusnr = old_name.yksusnr;
          if li_isdoknr = 0 then
            set li_objnr = 0;
            select if mt_konfm.dok_auku=1 then 0 else li_skoop endif into li_skoop from mt_konfm where mt_konfm."id" = 1
          end if;
          update tm_dok set doknr = li_olddoknr where tm_dok.tabel='mt_kanne' and tm_dok.aasta = old_name.aasta and tm_dok.skoop = li_skoop and tm_dok.objnr = li_objnr and tm_dok.sisse = old_name.sisse and tm_dok.doknr > li_olddoknr
        end if
      end if
    end if
  end if;
  if years(old_name.kuup)>2020 then
    call pr_kanne_add(month(old_name.kuup),years(old_name.kuup),old_name.yksusnr,7);
  end if;
end