/******************************************************************************/
/***               Generated by IBExpert 20.05.2014 10:58:40                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/


CREATE GENERATOR PRICEGOODS_ID;

CREATE TABLE PRICEGOODS (
    ID        INTEGER NOT NULL,
    GDSKEY    INTEGER NOT NULL,
    PRICE     DOUBLE PRECISION,
    PRICEID   INTEGER NOT NULL,
    CPRICE    DOUBLE PRECISION,
    USERNAME  VARCHAR(20),
    LASTEDIT  TIMESTAMP DEFAULT Current_TimeStamp
);




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

ALTER TABLE PRICEGOODS ADD CONSTRAINT PRICEGOODS_PK PRIMARY KEY (ID);


/******************************************************************************/
/***                              Foreign Keys                              ***/
/******************************************************************************/

ALTER TABLE PRICEGOODS ADD CONSTRAINT PRICEGOODS_GDSKEY FOREIGN KEY (GDSKEY) REFERENCES GOODS (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE PRICEGOODS ADD CONSTRAINT PRICEGOODS_PRICEID FOREIGN KEY (PRICEID) REFERENCES PRICELIST (ID) ON DELETE CASCADE ON UPDATE CASCADE;


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


SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: PRICEGOODS_AI */
CREATE OR ALTER TRIGGER PRICEGOODS_AI FOR PRICEGOODS
ACTIVE AFTER INSERT POSITION 0
AS
 declare variable aBASE_PRICE_ID INTEGER;
begin
  /*insert into PALM_PRICELISTGOODS (PRICEGOODS_ID,MODIFY) values (NEW.ID,0); */
/* Изменим цену базового прайс-листа в ТОВАРАХ */
  select BASE_PRICE_LIST_ID from MOBIL_MAIN where ID = 1
  into :aBASE_PRICE_ID;
  if (NEW.PRICEID = :aBASE_PRICE_ID) then
    update GOODS SET BASE_PRICE = NEW.PRICE
    where ID = NEW.GDSKEY;
end
^


/* Trigger: PRICEGOODS_AU */
CREATE OR ALTER TRIGGER PRICEGOODS_AU FOR PRICEGOODS
ACTIVE AFTER UPDATE POSITION 0
AS
 declare variable aBASE_PRICE_ID INTEGER;
begin
/*  insert into PALM_PRICELISTGOODS (PRICEGOODS_ID,MODIFY) values (NEW.ID,0); */
/*  update PALM_PRICELISTGOODS set MODIFY = 1
  where PRICEGOODS_ID = NEW.ID; */
/* Редактирование базовой цены в ТОВАРАХ */
  select BASE_PRICE_LIST_ID from MOBIL_MAIN where ID = 1
  into :aBASE_PRICE_ID;

  if (NEW.PRICEID = :aBASE_PRICE_ID) then
   update GOODS SET BASE_PRICE = NEW.PRICE
   where ID = NEW.GDSKEY;
end
^


/* Trigger: PRICEGOODS_BI0 */
CREATE OR ALTER TRIGGER PRICEGOODS_BI0 FOR PRICEGOODS
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (New.ID is Null) then New.ID = Gen_ID(PriceGoods_ID, 1);
end
^


/* Trigger: PRICEGOODS_BU0 */
CREATE OR ALTER TRIGGER PRICEGOODS_BU0 FOR PRICEGOODS
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  New.LASTEDIT = Current_TimeStamp;
end
^


SET TERM ; ^



/******************************************************************************/
/***                              Descriptions                              ***/
/******************************************************************************/

COMMENT ON TABLE PRICEGOODS IS 
'Позиции товаров в прайс-листах';



/******************************************************************************/
/***                          Fields descriptions                           ***/
/******************************************************************************/

COMMENT ON COLUMN PRICEGOODS.USERNAME IS 
'Имя пользователя';

COMMENT ON COLUMN PRICEGOODS.LASTEDIT IS 
'Дата / время последней модификации записи';



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/