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

RouteSCh_InsertSQ

Описание

(Нет описания для триггера RouteSCh_InsertSQ)

Определение

CREATE TRIGGER "RouteSCh_InsertSQ" FOR ROUTESILAGECHEMA
ACTIVE AFTER INSERT POSITION 0
AS
declare variable itClassID Integer;
declare variable OldPersentWeight Integer;
declare variable newSilageQualityID Integer;
declare variable oldSQID Integer;
declare variable itNet Integer;
declare variable partNet Integer;
declare variable oldInventoryID Integer;
declare variable tInventoryID Integer;

begin
  select it.inventoryid from it where it.routeid = new.routechemaid into :tInventoryID;
  if (:tInventoryID > 0) then
  for
   select
      IT.net,
      Quality.ClassID,
      routesilagechema.percentweight,
      SilagePacks.silagequalityid,
      it.inventoryid
    from IT
     join Quality on (IT.samplerqualityid = Quality.qualityid)
     join routesilagechema on (IT.routeid = routesilagechema.routechemaid
      and routesilagechema.silageid = new.silageid)
     join Silage on (new.silageid = Silage.silageid)
     join silagepacks  on (SilagePacks.silagepackid = Silage.silagepackid)
    where IT.inventoryid = :tInventoryID
      and IT.state <> -1
      and IT.operationid = 5
    group by
      IT.net,
      Quality.ClassID,
      routesilagechema.percentweight,
      SilagePacks.silagequalityid,
      it.inventoryid

    into :itNet,
         :itclassid,
         :oldpersentweight,
         :newSilageQualityID,
         :oldInventoryID
    do begin
      partNet = cast( cast(:itNet * :oldpersentweight as numeric (15,2))/ 100 as integer);
       update silage
       set silage.weight = silage.weight - :partNet,
           silage.inventoryid = :oldInventoryID
       where  silage.silageid = new.SilageID and silage.weight is not null;

       select
          SilageQuality.QualityID
        from SilageQuality
         join Quality on (SilageQuality.QualityID = Quality.QualityID)
        where SilageQuality.silagequalityid = :newSilageQualityID
          and Quality.ClassID = :itClassID
        into :oldSQID;

     update silagequality set weight = weight - :partNet
     where silagequality.silagequalityid = :newSilageQualityID
       and silagequality.qualityid = :oldSQID;


  end
end