SET SQL DIALECT  3 ;
SET NAMES WIN1251;

CREATE TABLE GOODS (
    GOOD_ID     VARCHAR( 15 ) NOT NULL,
    GROUP_ID    VARCHAR( 5 ) NOT NULL,
    NAME1       VARCHAR( 50 ) NOT NULL COLLATE PXW_CYRL,
    TARIH       DATE NOT NULL,
    PRICE1      DOUBLE PRECISION,
    PRICE2      DOUBLE PRECISION,

ALTER TABLE GOODS ADD CONSTRAINT PK_GOODS PRIMARY KEY (GOOD_ID);

ALTER TABLE GOODS ADD CONSTRAINT FK_GOODS FOREIGN KEY (GROUP_ID) REFERENCES GOODS_GROUP (GROUP_ID) ON UPDATE CASCADE;

);

CREATE TRIGGER GOODS_LASTNO_COUNT FOR GOODS
ACTIVE AFTER INSERT POSITION  0 
AS
begin
  update goods_group set goods_group.last_no = goods_group.last_no +  1 
  where goods_group.group_id = new.group_id;
end


CREATE TABLE GOODS_GROUP (
    GROUP_ID  VARCHAR( 5 ) NOT NULL COLLATE PXW_CYRL,
    GRNAME1   VARCHAR( 40 ) NOT NULL COLLATE PXW_CYRL,
    GRNAME2   VARCHAR( 40 ),
    LAST_NO   INTEGER NOT NULL
);


ALTER TABLE GOODS_GROUP ADD CONSTRAINT PK_GOODS_GROUP PRIMARY KEY (GROUP_ID);