"localhost:/firebird/data/ILICHEVSK2.FDB". Триггеры

IT_SETPARTYID

Описание

Установка ID партии операции

Определение

CREATE TRIGGER IT_SETPARTYID FOR IT
ACTIVE BEFORE INSERT OR UPDATE POSITION 100
as
declare CROPID integer;
declare CLASSID integer;
declare SORTID integer;
declare MULTIPARTY integer;
declare HARVESTYEAR integer;

begin
  if (((new.STORAGEID is not null) and ((new.SAMPLERQUALITYID is not null) or (new.LABQUALITYID is not null)))
      and (((new.PARTYID is null) or (exists(select * from PARTIES where (PARTIES.STATE = 1) and (PARTIES.PARTYID = new.PARTYID))))
           or ((old.PARTYID = -1) /*and exists(select * from Quality where Quality.qualityid = old.samplerqualityid and Quality.cropid is null
    and Quality.classid is null)*/)
          )
     ) then begin
    select coalesce(STORAGE.MULTIPARTY,0)
      from STORAGE
      where (STORAGE.STORAGEID = new.STORAGEID)
    into :MULTIPARTY;

    if (:MULTIPARTY = 1) then begin
      if (not (new.OPERATIONID in (4,5,9,17,22,45))) then begin

        select QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID
          from QUALITY
          where (QUALITY.QUALITYID = new.SAMPLERQUALITYID)
        into :CROPID, :CLASSID, :SORTID;

        select QUALITY_SELECT.HARVESTYEAR
          from QUALITY_SELECT(new.LABQUALITYID)
        into :HARVESTYEAR;

        if (:HARVESTYEAR is null) then
          select QUALITY_SELECT.HARVESTYEAR
            from QUALITY_SELECT(new.SAMPLERQUALITYID)
          into :HARVESTYEAR;

        new.PARTYID = null;

        select first 1 PARTIES.PARTYID
          from PARTIES
          where (PARTIES.STATE = 1)
            and ((PARTIES.STORAGEID is null) or (PARTIES.STORAGEID = new.STORAGEID))
            and ((PARTIES.CROPID is null) or (PARTIES.CROPID = :CROPID))
            and ((PARTIES.CLASSID is null) or (PARTIES.CLASSID = :CLASSID))
            and ((PARTIES.SORTID is null) or (PARTIES.SORTID = :SORTID))
            and ((PARTIES.HARVESTINGYEAR is null) or (PARTIES.HARVESTINGYEAR = :HARVESTYEAR))
        order by PARTIES.PARTYID desc
        into new.PARTYID;

        if (new.PARTYID is null) then
          new.PARTYID = -1;
      end
    end
    else begin
      select QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID
        from QUALITY
        where (QUALITY.QUALITYID = new.SAMPLERQUALITYID)
      into :CROPID, :CLASSID, :SORTID;

      new.PARTYID = null;

      select first 1 PARTIES.PARTYID
        from PARTIES
        where (PARTIES.STATE = 1)
          and ((PARTIES.STORAGEID is null) or (PARTIES.STORAGEID = new.STORAGEID))
          and ((PARTIES.CROPID is null) or (PARTIES.CROPID = :CROPID))
          and ((PARTIES.CLASSID is null) or (PARTIES.CLASSID = :CLASSID))
          and ((PARTIES.SORTID is null) or (PARTIES.SORTID = :SORTID))
      order by PARTIES.PARTYID desc
      into new.PARTYID;

      if (new.PARTYID is null) then
        new.PARTYID = -1;
    end
  end
end