CREATE TABLE KATEGORIJE_PROIZVODA
(
  ID     NUMBER NOT NULL,
  NAZIV  VARCHAR2(32 BYTE),
  OPIS   VARCHAR2(64 BYTE),
  CONSTRAINT pk_kat_proizvod PRIMARY KEY (ID) USING INDEX
);

CREATE SEQUENCE SCOTT.SEQ_KAT_PROIZVOD_ID
  START WITH 1
  MAXVALUE 99999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  ORDER;
  
CREATE OR REPLACE TRIGGER TR_KAT_PROIZVOD_ID
BEFORE INSERT
ON SCOTT.KATEGORIJE_PROIZVODA
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
   
   SELECT SEQ_KAT_PROIZVOD_ID.NEXTVAL INTO :NEW.ID FROM dual;

EXCEPTION
  WHEN OTHERS THEN
    NULL;
END ;
/


CREATE TABLE PROIZVODI
(
  ID            NUMBER NOT NULL,
  NAZIV         VARCHAR2(32 BYTE),
  KAT_PROIZ_ID  NUMBER,
  CONSTRAINT pk_proizvod PRIMARY KEY(ID) USING INDEX,
  CONSTRAINT fk_kat_proizvod FOREIGN KEY (kat_proiz_id) 
    REFERENCES KATEGORIJE_PROIZVODA(ID)
    ON DELETE CASCADE  
);

CREATE SEQUENCE SCOTT.SEQ_PROIZVOD_ID
  START WITH 1
  MAXVALUE 99999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  ORDER;
  
  
CREATE OR REPLACE TRIGGER TR_PROIZVOD_ID
BEFORE INSERT
ON SCOTT.PROIZVODI
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
   
   SELECT SEQ_PROIZVOD_ID.NEXTVAL INTO :NEW.ID FROM dual;

EXCEPTION
  WHEN OTHERS THEN
    NULL;
END ;
/