/******************************************************************************/ 
 /***               Generated by IBExpert 16.06.2004 17:18:05                ***/ 
 /******************************************************************************/ 

SET SQL DIALECT  3 ;

SET NAMES WIN1251;



 /******************************************************************************/ 
 /***                                 Tables                                 ***/ 
 /******************************************************************************/ 
CREATE GENERATOR PART_ID_GEN;

CREATE TABLE PART (
    PART_ID                    T_PART_ID NOT NULL  /* T_PART_ID = INTEGER */ ,
    TYPE_BILL_ID               T_TYPE_BILL_ID  /* T_TYPE_BILL_ID = INTEGER */ ,
    MAIN_PART_ID               T_MAIN_PART_ID  /* T_MAIN_PART_ID = INTEGER */ ,
    UNIT_ID                    T_UNIT_ID  /* T_UNIT_ID = INTEGER */ ,
    DEPARTMENT_ID              T_DEPARTMENT_ID  /* T_DEPARTMENT_ID = INTEGER */ ,
    PART_CATEGORY_ID           T_PART_CATEGORY_ID  /* T_PART_CATEGORY_ID = INTEGER */ ,
    BASE_PART_ID               T_BASE_PART_ID NOT NULL  /* T_BASE_PART_ID = INTEGER */ ,
    POINTER_PART_ID            T_POINTER_PART_ID  /* T_POINTER_PART_ID = INTEGER */ ,
    REG_DATE                   T_REG_DATE  /* T_REG_DATE = DATE DEFAULT 'NOW' */ ,
    REG_TIME                   T_REG_TIME  /* T_REG_TIME = TIME DEFAULT 'NOW' */ ,
    FNAME_SHORT                T_FNAME_SHORT  /* T_FNAME_SHORT = VARCHAR(84) */ ,
    FNAME                      T_FNAME  /* T_FNAME = VARCHAR(150) */ ,
    COMMENT                    T_COMMENT  /* T_COMMENT = VARCHAR(250) */ ,
    BOOLEAN_GOODS_OR_SERVICE   T_BOOLEAN_GOODS_OR_SERVICE  /* T_BOOLEAN_GOODS_OR_SERVICE = INTEGER DEFAULT 0 NOT NULL CHECK (VALUE IN (0, 1)) */ ,
    VERSION_CODE               T_VERSION_CODE  /* T_VERSION_CODE = VARCHAR(250) */ ,
    VERSION_BUILD              T_VERSION_BUILD  /* T_VERSION_BUILD = VARCHAR(250) */ ,
    SUMMA_PRICE                T_SUMMA_PRICE  /* T_SUMMA_PRICE = NUMERIC(12,2) DEFAULT 0 NOT NULL */ ,
    QUANTITY                   T_QUANTITY  /* T_QUANTITY = NUMERIC(14,4) DEFAULT 1 NOT NULL */ ,
    QUANTITY_TOTAL             T_QUANTITY_TOTAL  /* T_QUANTITY_TOTAL = NUMERIC(14,4) DEFAULT 1 NOT NULL */ ,
    SUMMA_NO_TAX_RATE          T_SUMMA_NO_TAX_RATE  /* T_SUMMA_NO_TAX_RATE = NUMERIC(12,2) DEFAULT 0 NOT NULL */ ,
    PC_TAX_RATE                T_PC_TAX_RATE DEFAULT  0   /* T_PC_TAX_RATE = NUMERIC(12,4) DEFAULT 0 NOT NULL */ ,
    SUMMA_TAX_RATE             T_SUMMA_TAX_RATE  /* T_SUMMA_TAX_RATE = NUMERIC(12,2) DEFAULT 0 NOT NULL */ ,
    SUMMA                      T_SUMMA  /* T_SUMMA = NUMERIC(12,2) DEFAULT 0 NOT NULL */ ,
    NODE_CATEGORY              T_NODE_CATEGORY  /* T_NODE_CATEGORY = VARCHAR(6) NOT NULL CHECK (VALUE IN ('Ветка', 'Лист', 'Пакет', 'Фантом')) */ ,
    BOOLEAN_PART_OR_REFERENCE  T_BOOLEAN_PART_OR_REFERENCE  /* T_BOOLEAN_PART_OR_REFERENCE = INTEGER DEFAULT 0 NOT NULL CHECK (VALUE IN (0, 1)) */ ,
    LOCATION                   T_LOCATION  /* T_LOCATION = INTEGER DEFAULT 0 */ ,
    CHILD_COUNT                T_CHILD_COUNT  /* T_CHILD_COUNT = INTEGER DEFAULT 0 NOT NULL */ 
);





 /******************************************************************************/ 
 /***                              Primary Keys                              ***/ 
 /******************************************************************************/ 

ALTER TABLE PART ADD PRIMARY KEY (PART_ID);


 /******************************************************************************/ 
 /***                                Indices                                 ***/ 
 /******************************************************************************/ 

CREATE INDEX XIE1PART ON PART (POINTER_PART_ID);
CREATE INDEX XIE2PART ON PART (LOCATION);
CREATE INDEX XIE3PART ON PART (NODE_CATEGORY);
CREATE INDEX XIE4PART ON PART (BASE_PART_ID);
CREATE INDEX XIF146PART ON PART (PART_CATEGORY_ID);
CREATE INDEX XIF156PART ON PART (DEPARTMENT_ID);
CREATE INDEX XIF168PART ON PART (UNIT_ID);
CREATE INDEX XIF185PART ON PART (MAIN_PART_ID);
CREATE INDEX XIF186PART ON PART (TYPE_BILL_ID);


 /******************************************************************************/ 
 /***                                Triggers                                ***/ 
 /******************************************************************************/ 


SET TERM ^ ;




 /* Trigger: PART_AD */ 
CREATE TRIGGER PART_AD FOR PART
ACTIVE AFTER DELETE POSITION  0 
AS
DECLARE VARIABLE numrows INTEGER;
BEGIN

.....

 /* Part ссылается на формирующую деталь Part Запрет удаления родительской записи */ 
    select count(*)
      from Part
      where
        Part.part_id = OLD.part_id into numrows;
    IF (numrows >  0 ) THEN
    BEGIN
      EXCEPTION CUSTOM_RESTRICT 'Удалять нельзя: имеются записи в связанной таблице "Прайс"';
    END

...
END
^

 /* Trigger: PART_AU */ 
CREATE TRIGGER PART_AU FOR PART
ACTIVE AFTER UPDATE POSITION  0 
AS
DECLARE VARIABLE numrows INTEGER;
BEGIN
...
 /* Part ссылается на формирующую деталь Part Запрет изменения родительской записи */ 
  IF
    (OLD.part_id <> NEW.part_id) THEN
  BEGIN
    select count(*)
      from Part
      where
        Part.part_id = OLD.part_id into numrows;
    IF (numrows >  0 ) THEN
    BEGIN
      EXCEPTION CUSTOM_RESTRICT 'Изменять нельзя: имеются записи в связанной таблице "Прайс"';
    END
  END

...

END
^

 /* Trigger: PART_GEN_ID */ 
CREATE TRIGGER PART_GEN_ID FOR PART
ACTIVE BEFORE INSERT POSITION  0 
as
begin 
    IF (new.Part_id IS NULL) THEN
       new.Part_id = GEN_ID(Part_id_gen,  1 );
end
^

 /* Trigger: PART_POINTER_AD */ 
CREATE TRIGGER PART_POINTER_AD FOR PART
ACTIVE AFTER DELETE POSITION  0 
AS
 /* При удалении детали
  удалять и соответсвующие детали пакетов */ 
BEGIN
  if (old.boolean_part_or_reference =  0 ) then
  begin
    delete from Part
    where Part.pointer_Part_id =
          old.Part_id;
  end
END
^

 /* Trigger: PART_POINTER_BU */ 
CREATE TRIGGER PART_POINTER_BU FOR PART
ACTIVE BEFORE UPDATE POSITION  0 
AS
 /* При изменении детали изменять и 
   соотв. детали пакетов*/ 
BEGIN
 /* При изменении детали изменять и 
   соотв. детали пакетов*/ 
 /* Если это не перемещение...*/ 
if ((new.Part_id = old.Part_id)
   and ((old.node_category = 'Лист')
      or(old.node_category = 'Пакет'))
   and (old.boolean_part_or_reference =  0 ))  then begin

 /* Если есть ссылки на элемент*/ 
  if (exists (select Part_id from Part
               where Part.pointer_Part_id = old.Part_id)) then
  begin

   /* Переносим Код вида счета */ 
    if ((old.type_bill_id <> new.type_bill_id)
     or (old.type_bill_id is not null) and
        (new.type_bill_id is null)
     or (old.type_bill_id is null) and
        (new.type_bill_id is not null)) then begin
      update Part set type_bill_id = new.type_bill_id
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Код главной номенклатуры */ 
    if ((old.main_part_id <> new.main_part_id)
     or (old.main_part_id is not null) and
        (new.main_part_id is null)
     or (old.main_part_id is null) and
        (new.main_part_id is not null)) then begin
      update Part set main_part_id = new.main_part_id
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Код ед измер */ 
    if ((old.unit_id <> new.unit_id)
     or (old.unit_id is not null) and
        (new.unit_id is null)
     or (old.unit_id is null) and
        (new.unit_id is not null)) then begin
      update Part set unit_id = new.unit_id
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Код подразделения */ 
    if ((old.department_id <> new.department_id)
     or (old.department_id is not null) and
        (new.department_id is null)
     or (old.department_id is null) and
        (new.department_id is not null)) then begin
      update Part set department_id = new.department_id
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Код категории товара */ 
    if ((old.part_category_id <> new.part_category_id)
     or (old.part_category_id is not null) and
        (new.part_category_id is null)
     or (old.part_category_id is null) and
        (new.part_category_id is not null)) then begin
      update Part set part_category_id = new.part_category_id
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Полн. наименование */ 
    if ((old.fname_short <> new.fname_short)
     or (old.fname_short is not null) and
        (new.fname_short is null)
     or (old.fname_short is null) and
        (new.fname_short is not null)) then begin
      update Part set fname_short = new.fname_short
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Полное наименование */ 
    if ((old.fname <> new.fname)
     or (old.fname is not null) and
        (new.fname is null)
     or (old.fname is null) and
        (new.fname is not null)) then begin
      update Part set fname = new.fname
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Товар/Услуга */ 
    if ((old.boolean_goods_or_service <> new.boolean_goods_or_service)
     or (old.boolean_goods_or_service is not null) and
        (new.boolean_goods_or_service is null)
     or (old.boolean_goods_or_service is null) and
        (new.boolean_goods_or_service is not null)) then begin
      update Part set boolean_goods_or_service = new.boolean_goods_or_service
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Код версии */ 
    if ((old.version_code <> new.version_code)
     or (old.version_code is not null) and
        (new.version_code is null)
     or (old.version_code is null) and
        (new.version_code is not null)) then begin
      update Part set version_code = new.version_code
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Сборка */ 
    if ((old.version_build <> new.version_build)
     or (old.version_build is not null) and
        (new.version_build is null)
     or (old.version_build is null) and
        (new.version_build is not null)) then begin
      update Part set version_build = new.version_build
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Цена */ 
    if ((old.summa_price <> new.summa_price)
     or (old.summa_price is not null) and
        (new.summa_price is null)
     or (old.summa_price is null) and
        (new.summa_price is not null)) then begin
      update Part set summa_price = new.summa_price
      where old.Part_id = Part.pointer_Part_id;
      execute procedure recount_references_Part(old.Part_id); 

    end

 /* Переносим Ставка налога, % */ 
    if ((old.pc_tax_rate <> new.pc_tax_rate)
     or (old.pc_tax_rate is not null) and
        (new.pc_tax_rate is null)
     or (old.pc_tax_rate is null) and
        (new.pc_tax_rate is not null)) then begin
      update Part set pc_tax_rate = new.pc_tax_rate
      where old.Part_id = Part.pointer_Part_id;
      execute procedure recount_references_Part(old.Part_id); 

    end



  end
end
END
^

 /* Trigger: PART_QUANTITY_ADD */ 
CREATE TRIGGER PART_QUANTITY_ADD FOR PART
ACTIVE BEFORE INSERT POSITION  2 
as
declare variable P_Node_Category VARCHAR( 6 );
declare variable P_Quantity_Total NUMERIC ( 14 , 4 );
declare variable Koeff NUMERIC ( 14 , 4 );
 /*Генерируется, только если есть такое поле:*/ 
declare variable P_Boolean_Part_Or_Reference INTEGER;


BEGIN
   /* Добавление: если добавляем не ссылку, а лист или пакет, то
     посмотреть количество у родителя, и,
     если он пакет, и умножить количество родителя на текущее количество */ 
  Koeff =  1 ;
  if ((new.Node_Category IN  ('Лист', 'Пакет')) 
  and (new.BASE_Part_ID >  0 )
 /*Генерируется, только если есть такое поле:*/ 
  and (new.Boolean_Part_Or_Reference =  0 )
) then begin
    select Node_Category, 
 /*Генерируется, только если есть такое поле:*/ 
  Boolean_Part_Or_Reference,
Quantity_Total from Part T
      where T.Part_ID = new.BASE_Part_ID into
      P_Node_Category,
 /*Генерируется, только если есть такое поле:*/ 
  P_Boolean_Part_Or_Reference,
      P_Quantity_Total;
    if ((P_Node_category = 'Пакет') 
 /*Генерируется, только если есть такое поле:*/ 
  and (P_Boolean_Part_Or_Reference =  0 )

) then
        Koeff = P_Quantity_Total; 
  end
  new.Quantity_Total = new.Quantity * Koeff; 

END
^

 /* Trigger: PART_QUANTITY_UPD */ 
CREATE TRIGGER PART_QUANTITY_UPD FOR PART
ACTIVE BEFORE UPDATE POSITION  2 
as
declare variable P_Node_Category VARCHAR( 6 );
declare variable P_Quantity_Total NUMERIC ( 14 , 4 );
declare variable Koeff NUMERIC ( 14 , 4 );
declare variable P_Boolean_Part_Or_Reference INTEGER;

BEGIN    
   /* Узел-не-ссылка-и-(лист или пакет) переместился или изменилось количество: то
   посмотреть общее количество у родителя, и,
   если родитель пакет, получить новое общее количество, умножив общее количество родителя на текущее количество */ 
  Koeff =  1 ;
  if ((new.Node_Category IN  ('Лист', 'Пакет')) 
    and (new.Boolean_Part_Or_Reference =  0 )


) then begin

    if ((old.Base_Part_ID <> new.Base_Part_ID) 
      or(old.Quantity <> new.Quantity) ) then begin  /* Перемещение */ 
      if (new.BASE_Part_ID >  0 ) then begin
        select Node_Category, 
        Boolean_Part_Or_Reference,

        Quantity_Total from Part T
          where (T.Part_ID = new.BASE_Part_ID) into
          P_Node_Category, 
          P_Boolean_Part_Or_Reference,

          P_Quantity_Total;
        if ((P_Node_category = 'Пакет') 
        and (P_Boolean_Part_Or_Reference =  0 )


      ) then
          Koeff = P_Quantity_Total; 
      end   
      new.Quantity_Total = new.Quantity * Koeff; 
    end

    if ((new.Quantity_Total <> old.Quantity_Total) 
    and (new.Node_Category = 'Пакет')) then
      update Part T set 
        T.Quantity_Total = T.Quantity * new.Quantity_Total
        where ((T.Base_Part_Id = New.Part_Id) 
         and  (T.Node_Category IN  ('Лист', 'Пакет')) 
        and (T.Boolean_Part_Or_Reference =  0 )

); 
  end
END
^

 /* Trigger: PART_SUMM_ADD */ 
CREATE TRIGGER PART_SUMM_ADD FOR PART
ACTIVE BEFORE INSERT POSITION  2 
as
BEGIN    
  if ((new.base_Part_id >  0 ) and (new.summa <>  0 )) then begin
     /* Если от чего-то наследуется, родителю передать сумму*/ 
    update Part T set
      T.summa_price = T.summa_price + new.summa
      where T.Part_id = new.base_Part_id;
    execute procedure recount_Part(new.base_Part_id);
  end
END
^

 /* Trigger: PART_SUMM_DEL */ 
CREATE TRIGGER PART_SUMM_DEL FOR PART
ACTIVE BEFORE DELETE POSITION  2 
as
BEGIN    
  if ((old.base_Part_id >  0 ) and (old.summa <>  0 )) then begin
   /* Если от чего-то наследовался, у родителя вычесть сумму*/ 
    update Part T set
      T.summa_price = T.summa_price - old.summa
      where T.Part_id = old.base_Part_id;
    execute procedure recount_Part(old.base_Part_id);
  end
END
^

 /* Trigger: PART_SUMM_UPD */ 
CREATE TRIGGER PART_SUMM_UPD FOR PART
ACTIVE BEFORE UPDATE POSITION  2 
as
BEGIN    
  if ((old.base_Part_id >  0 ) and (old.summa <>  0 )) then begin
   /* Если от чего-то наследовался, у бывшего родителя вычесть сумму*/ 
    update Part T set
      T.summa_price = T.summa_price - old.summa
      where T.Part_id = old.base_Part_id;
    execute procedure recount_Part(old.base_Part_id);
  end
  if ((new.base_Part_id >  0 ) and (new.summa <>  0 )) then begin
   /* Если будет от чего-то наследоваться, новому родителю передать сумму*/ 
    update Part T set
      T.summa_price = T.summa_price + new.summa
      where T.Part_id = new.base_Part_id;
    execute procedure recount_Part(new.base_Part_id);
  end
END
^

 /* Trigger: PART_TREE_BIUD */ 
CREATE TRIGGER PART_TREE_BIUD FOR PART
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION  99 
as
BEGIN    --  Прайс - обеспечение фукционирования как дерева
 
  if (inserting or updating) then begin
    if (new.base_Part_id is null) then 
         new.base_Part_id =  0 ;
    if (new.child_count is null) then 
         new.child_count =  0 ;
    if (new.location is null)  then   -- Вполне достаточно для обеспечения последовательного размещения элементов
 
         new.location = GEN_ID(Part_id_gen,  1 );  -- при перемещении между уровнями можно просто сбросить значение Location
 
  end

  if ((inserting) or
      (updating and
       (old.Base_Part_ID <> new.Base_Part_ID)
         )) then    -- Увеличить счетчик детей у нового родителя
 
          UPDATE Part T SET T.CHILD_COUNT=T.CHILD_COUNT+ 1 
            WHERE T.Part_ID = new.Base_Part_ID;

  if ((deleting) or
      (updating and
        (old.Base_Part_ID <> new.Base_Part_ID)
          )) then    -- Уменьшить счетчик детей у старого родителя
 
        UPDATE Part T SET T.CHILD_COUNT=T.CHILD_COUNT- 1 
          WHERE T.Part_ID = old.Base_Part_ID;

  if (deleting) then  -- Каскадное удаление. Если не требуется, то перекрывается на клиенте
 
      delete from Part T 
        where
          T.base_Part_id = OLD.Part_id;
END
^
...

SET TERM ; ^



 /*************/ 
 /* Процедуры */ 
 /*************/ 

CREATE PROCEDURE GET_TREE_PART (
    MASTER_PART_ID INTEGER)
RETURNS (
    PART_ID INTEGER,
    TYPE_BILL_ID INTEGER,
    MAIN_PART_ID INTEGER,
    UNIT_ID INTEGER,
    DEPARTMENT_ID INTEGER,
    PART_CATEGORY_ID INTEGER,
    BASE_PART_ID INTEGER,
    POINTER_PART_ID INTEGER,
    REG_DATE DATE,
    REG_TIME TIME,
    FNAME_SHORT VARCHAR( 84 ),
    FNAME VARCHAR( 150 ),
    COMMENT VARCHAR( 250 ),
    BOOLEAN_GOODS_OR_SERVICE INTEGER,
    VERSION_CODE VARCHAR( 250 ),
    VERSION_BUILD VARCHAR( 250 ),
    SUMMA_PRICE NUMERIC( 12 , 2 ),
    QUANTITY NUMERIC( 14 , 4 ),
    QUANTITY_TOTAL NUMERIC( 14 , 4 ),
    SUMMA_NO_TAX_RATE NUMERIC( 12 , 2 ),
    PC_TAX_RATE NUMERIC( 12 , 4 ),
    SUMMA_TAX_RATE NUMERIC( 12 , 2 ),
    SUMMA NUMERIC( 12 , 2 ),
    NODE_CATEGORY VARCHAR( 6 ),
    BOOLEAN_PART_OR_REFERENCE INTEGER,
    LOCATION INTEGER,
    CHILD_COUNT INTEGER)
AS
begin
  for select part_id, type_bill_id, main_part_id, unit_id, department_id, part_category_id, base_part_id, pointer_part_id, reg_date, reg_time, fname_short, fname, comment, boolean_goods_or_service, version_code, version_build, summa_price, quantity, quantity_total, summa_no_tax_rate, pc_tax_rate, summa_tax_rate, summa, node_category, boolean_part_or_reference, location, child_count
  from Part T
  where Base_Part_id =:MASTER_Part_id
  order by T.Location, T.FName_Short
  into :part_id, :type_bill_id, :main_part_id, :unit_id, :department_id, :part_category_id, :base_part_id, :pointer_part_id, :reg_date, :reg_time, :fname_short, :fname, :comment, :boolean_goods_or_service, :version_code, :version_build, :summa_price, :quantity, :quantity_total, :summa_no_tax_rate, :pc_tax_rate, :summa_tax_rate, :summa, :node_category, :boolean_part_or_reference, :location, :child_count
   do begin
     suspend;
     if (Child_Count >  0 ) then
       for  select part_id, type_bill_id, main_part_id, unit_id, department_id, part_category_id, base_part_id, pointer_part_id, reg_date, reg_time, fname_short, fname, comment, boolean_goods_or_service, version_code, version_build, summa_price, quantity, quantity_total, summa_no_tax_rate, pc_tax_rate, summa_tax_rate, summa, node_category, boolean_part_or_reference, location, child_count
       from get_tree_part(:Part_id)
       into :part_id, :type_bill_id, :main_part_id, :unit_id, :department_id, :part_category_id, :base_part_id, :pointer_part_id, :reg_date, :reg_time, :fname_short, :fname, :comment, :boolean_goods_or_service, :version_code, :version_build, :summa_price, :quantity, :quantity_total, :summa_no_tax_rate, :pc_tax_rate, :summa_tax_rate, :summa, :node_category, :boolean_part_or_reference, :location, :child_count
     do suspend;
   end
end

 /* Эта процедурка - для контроля против "циклических ссылок" */ 

CREATE PROCEDURE GET_PARENTS_PART (
    ID INTEGER)
RETURNS (
    DID INTEGER,
    OID INTEGER)
AS
BEGIN
  WHILE (ID >  0 ) DO
    BEGIN
      SELECT t.Part_id, t.base_Part_id
      FROM Part T
      WHERE T.Part_id = :ID
      INTO :DID, :OID;
      ID=OID;
      SUSPEND;
    END
END

 /*Домаю, понятно:*/ 

CREATE PROCEDURE RECOUNT_PART (
    ID INTEGER)
AS
begin
  update part  T set
    T.summa = T.summa_price * T.quantity,
    T.summa_tax_rate = T.summa / ( T.pc_tax_rate +  100 ) * T.pc_tax_rate,
    T.summa_no_tax_rate = T.summa - T.summa_tax_rate
  where part_id = :Id;
end

 /*Это - то же самое, но для работы со "ссылками":*/ 

CREATE PROCEDURE RECOUNT_REFERENCES_PART (
    REFERENCES_ID INTEGER)
AS
begin
  update Part  T set
    T.summa = T.summa_price * T.quantity,
    T.summa_tax_rate = T.summa / ( T.pc_tax_rate +  100 ) * T.pc_tax_rate,
    T.summa_no_tax_rate = T.summa - T.summa_tax_rate
  where Part.pointer_Part_id = :references_id;
end

 /* Trigger: PART_GEN_ID */ 
CREATE TRIGGER PART_GEN_ID FOR PART
ACTIVE BEFORE INSERT POSITION  0 
as
begin 
    IF (new.Part_id IS NULL) THEN
       new.Part_id = GEN_ID(Part_id_gen,  1 );
end
^

 /* Trigger: PART_POINTER_AD */ 
CREATE TRIGGER PART_POINTER_AD FOR PART
ACTIVE AFTER DELETE POSITION  0 
AS
 /* При удалении детали
  удалять и соответсвующие детали пакетов */ 
BEGIN
  if (old.boolean_part_or_reference =  0 ) then
  begin
    delete from Part
    where Part.pointer_Part_id =
          old.Part_id;
  end
END
^

 /* Trigger: PART_POINTER_BU */ 
CREATE TRIGGER PART_POINTER_BU FOR PART
ACTIVE BEFORE UPDATE POSITION  0 
AS
 /* При изменении детали изменять и 
   соотв. детали пакетов*/ 
BEGIN
 /* При изменении детали изменять и 
   соотв. детали пакетов*/ 
 /* Если это не перемещение...*/ 
if ((new.Part_id = old.Part_id)
   and ((old.node_category = 'Лист')
      or(old.node_category = 'Пакет'))
   and (old.boolean_part_or_reference =  0 ))  then begin

 /* Если есть ссылки на элемент*/ 
  if (exists (select Part_id from Part
               where Part.pointer_Part_id = old.Part_id)) then
  begin

   /* Переносим Код вида счета */ 
    if ((old.type_bill_id <> new.type_bill_id)
     or (old.type_bill_id is not null) and
        (new.type_bill_id is null)
     or (old.type_bill_id is null) and
        (new.type_bill_id is not null)) then begin
      update Part set type_bill_id = new.type_bill_id
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Код главной номенклатуры */ 
    if ((old.main_part_id <> new.main_part_id)
     or (old.main_part_id is not null) and
        (new.main_part_id is null)
     or (old.main_part_id is null) and
        (new.main_part_id is not null)) then begin
      update Part set main_part_id = new.main_part_id
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Код ед измер */ 
    if ((old.unit_id <> new.unit_id)
     or (old.unit_id is not null) and
        (new.unit_id is null)
     or (old.unit_id is null) and
        (new.unit_id is not null)) then begin
      update Part set unit_id = new.unit_id
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Код подразделения */ 
    if ((old.department_id <> new.department_id)
     or (old.department_id is not null) and
        (new.department_id is null)
     or (old.department_id is null) and
        (new.department_id is not null)) then begin
      update Part set department_id = new.department_id
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Код категории товара */ 
    if ((old.part_category_id <> new.part_category_id)
     or (old.part_category_id is not null) and
        (new.part_category_id is null)
     or (old.part_category_id is null) and
        (new.part_category_id is not null)) then begin
      update Part set part_category_id = new.part_category_id
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Полн. наименование */ 
    if ((old.fname_short <> new.fname_short)
     or (old.fname_short is not null) and
        (new.fname_short is null)
     or (old.fname_short is null) and
        (new.fname_short is not null)) then begin
      update Part set fname_short = new.fname_short
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Полное наименование */ 
    if ((old.fname <> new.fname)
     or (old.fname is not null) and
        (new.fname is null)
     or (old.fname is null) and
        (new.fname is not null)) then begin
      update Part set fname = new.fname
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Товар/Услуга */ 
    if ((old.boolean_goods_or_service <> new.boolean_goods_or_service)
     or (old.boolean_goods_or_service is not null) and
        (new.boolean_goods_or_service is null)
     or (old.boolean_goods_or_service is null) and
        (new.boolean_goods_or_service is not null)) then begin
      update Part set boolean_goods_or_service = new.boolean_goods_or_service
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Код версии */ 
    if ((old.version_code <> new.version_code)
     or (old.version_code is not null) and
        (new.version_code is null)
     or (old.version_code is null) and
        (new.version_code is not null)) then begin
      update Part set version_code = new.version_code
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Сборка */ 
    if ((old.version_build <> new.version_build)
     or (old.version_build is not null) and
        (new.version_build is null)
     or (old.version_build is null) and
        (new.version_build is not null)) then begin
      update Part set version_build = new.version_build
      where old.Part_id = Part.pointer_Part_id;
      
    end

 /* Переносим Цена */ 
    if ((old.summa_price <> new.summa_price)
     or (old.summa_price is not null) and
        (new.summa_price is null)
     or (old.summa_price is null) and
        (new.summa_price is not null)) then begin
      update Part set summa_price = new.summa_price
      where old.Part_id = Part.pointer_Part_id;
      execute procedure recount_references_Part(old.Part_id); 

    end

 /* Переносим Ставка налога, % */ 
    if ((old.pc_tax_rate <> new.pc_tax_rate)
     or (old.pc_tax_rate is not null) and
        (new.pc_tax_rate is null)
     or (old.pc_tax_rate is null) and
        (new.pc_tax_rate is not null)) then begin
      update Part set pc_tax_rate = new.pc_tax_rate
      where old.Part_id = Part.pointer_Part_id;
      execute procedure recount_references_Part(old.Part_id); 

    end



  end
end
END
^

 /* Trigger: PART_QUANTITY_ADD */ 
CREATE TRIGGER PART_QUANTITY_ADD FOR PART
ACTIVE BEFORE INSERT POSITION  2 
as
declare variable P_Node_Category VARCHAR( 6 );
declare variable P_Quantity_Total NUMERIC ( 14 , 4 );
declare variable Koeff NUMERIC ( 14 , 4 );
declare variable P_Boolean_Part_Or_Reference INTEGER;


BEGIN
   /* Добавление: если добавляем не ссылку, а лист или пакет, то
     посмотреть количество у родителя, и,
     если он пакет, и умножить количество родителя на текущее количество */ 
  Koeff =  1 ;
  if ((new.Node_Category IN  ('Лист', 'Пакет')) 
  and (new.BASE_Part_ID >  0 )
  and (new.Boolean_Part_Or_Reference =  0 )
) then begin
    select Node_Category, 
  Boolean_Part_Or_Reference,
Quantity_Total from Part T
      where T.Part_ID = new.BASE_Part_ID into
      P_Node_Category,
  P_Boolean_Part_Or_Reference,
      P_Quantity_Total;
    if ((P_Node_category = 'Пакет') 
  and (P_Boolean_Part_Or_Reference =  0 )

) then
        Koeff = P_Quantity_Total; 
  end
  new.Quantity_Total = new.Quantity * Koeff; 

END
^

 /* Trigger: PART_QUANTITY_UPD */ 
CREATE TRIGGER PART_QUANTITY_UPD FOR PART
ACTIVE BEFORE UPDATE POSITION  2 
as
declare variable P_Node_Category VARCHAR( 6 );
declare variable P_Quantity_Total NUMERIC ( 14 , 4 );
declare variable Koeff NUMERIC ( 14 , 4 );
declare variable P_Boolean_Part_Or_Reference INTEGER;

BEGIN    
   /* Узел-не-ссылка-и-(лист или пакет) переместился или изменилось количество: то
   посмотреть общее количество у родителя, и,
   если родитель пакет, получить новое общее количество, умножив общее количество родителя на текущее количество */ 
  Koeff =  1 ;
  if ((new.Node_Category IN  ('Лист', 'Пакет')) 
    and (new.Boolean_Part_Or_Reference =  0 )


) then begin

    if ((old.Base_Part_ID <> new.Base_Part_ID) 
      or(old.Quantity <> new.Quantity) ) then begin  /* Перемещение */ 
      if (new.BASE_Part_ID >  0 ) then begin
        select Node_Category, 
        Boolean_Part_Or_Reference,

        Quantity_Total from Part T
          where (T.Part_ID = new.BASE_Part_ID) into
          P_Node_Category, 
          P_Boolean_Part_Or_Reference,

          P_Quantity_Total;
        if ((P_Node_category = 'Пакет') 
        and (P_Boolean_Part_Or_Reference =  0 )


      ) then
          Koeff = P_Quantity_Total; 
      end   
      new.Quantity_Total = new.Quantity * Koeff; 
    end

    if ((new.Quantity_Total <> old.Quantity_Total) 
    and (new.Node_Category = 'Пакет')) then
      update Part T set 
        T.Quantity_Total = T.Quantity * new.Quantity_Total
        where ((T.Base_Part_Id = New.Part_Id) 
         and  (T.Node_Category IN  ('Лист', 'Пакет')) 
        and (T.Boolean_Part_Or_Reference =  0 )


); 
  end
END
^

 /* Trigger: PART_SUMM_ADD */ 
CREATE TRIGGER PART_SUMM_ADD FOR PART
ACTIVE BEFORE INSERT POSITION  2 
as
BEGIN    
  if ((new.base_Part_id >  0 ) and (new.summa <>  0 )) then begin
     /* Если от чего-то наследуется, родителю передать сумму*/ 
    update Part T set
      T.summa_price = T.summa_price + new.summa
      where T.Part_id = new.base_Part_id;
    execute procedure recount_Part(new.base_Part_id);
  end
END
^

 /* Trigger: PART_SUMM_DEL */ 
CREATE TRIGGER PART_SUMM_DEL FOR PART
ACTIVE BEFORE DELETE POSITION  2 
as
BEGIN    
  if ((old.base_Part_id >  0 ) and (old.summa <>  0 )) then begin
   /* Если от чего-то наследовался, у родителя вычесть сумму*/ 
    update Part T set
      T.summa_price = T.summa_price - old.summa
      where T.Part_id = old.base_Part_id;
    execute procedure recount_Part(old.base_Part_id);
  end
END
^

 /* Trigger: PART_SUMM_UPD */ 
CREATE TRIGGER PART_SUMM_UPD FOR PART
ACTIVE BEFORE UPDATE POSITION  2 
as
BEGIN    
  if ((old.base_Part_id >  0 ) and (old.summa <>  0 )) then begin
   /* Если от чего-то наследовался, у бывшего родителя вычесть сумму*/ 
    update Part T set
      T.summa_price = T.summa_price - old.summa
      where T.Part_id = old.base_Part_id;
    execute procedure recount_Part(old.base_Part_id);
  end
  if ((new.base_Part_id >  0 ) and (new.summa <>  0 )) then begin
   /* Если будет от чего-то наследоваться, новому родителю передать сумму*/ 
    update Part T set
      T.summa_price = T.summa_price + new.summa
      where T.Part_id = new.base_Part_id;
    execute procedure recount_Part(new.base_Part_id);
  end
END
^

 /* Trigger: PART_TREE_BIUD */ 
CREATE TRIGGER PART_TREE_BIUD FOR PART
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION  99 
as
BEGIN    --  Прайс - обеспечение фукционирования как дерева
 
  if (inserting or updating) then begin
    if (new.base_Part_id is null) then 
         new.base_Part_id =  0 ;
    if (new.child_count is null) then 
         new.child_count =  0 ;
    if (new.location is null)  then   -- Вполне достаточно для обеспечения последовательного размещения элементов
 
         new.location = GEN_ID(Part_id_gen,  1 );  -- при перемещении между уровнями можно просто сбросить значение Location
 
  end

  if ((inserting) or
      (updating and
       (old.Base_Part_ID <> new.Base_Part_ID)
         )) then    -- Увеличить счетчик детей у нового родителя
 
          UPDATE Part T SET T.CHILD_COUNT=T.CHILD_COUNT+ 1 
            WHERE T.Part_ID = new.Base_Part_ID;

  if ((deleting) or
      (updating and
        (old.Base_Part_ID <> new.Base_Part_ID)
          )) then    -- Уменьшить счетчик детей у старого родителя
 
        UPDATE Part T SET T.CHILD_COUNT=T.CHILD_COUNT- 1 
          WHERE T.Part_ID = old.Base_Part_ID;

  if (deleting) then  -- Каскадное удаление. Если не требуется, то перекрывается на клиенте
 
      delete from Part T 
        where
          T.base_Part_id = OLD.Part_id;
END
^