--5.0.1.73
alter table xunivset add barcode VARCHAR2(20);

create unique index XUNIVSET_PK on xunivset (NRSET,COD);

create or replace View vms_univset
(nrset,cod,nrord,mark,param,barcode,codvechi,denumirea,um)
as
select a."nrset",
          a."cod",
          a."nrord",
          a."mark",
          param,
          barcode,
          (select codvechi
             from tms_univers
            where cod = a.cod)
             codvechi,
          (select denumirea
             from tms_univers
            where cod = a.cod)
             denumirea,
          (select um
             from tms_univers
            where cod = a.cod)
             um
     from xunivset a;

create or replace trigger VMS_UNIVSET_TR
INSTEAD OF INSERT ON VMS_UNIVSET
FOR EACH ROW
DECLARE
 NRORD$ INT:=:NEW.NRORD;
BEGIN
 IF NVL(:NEW.COD,0)=0 THEN
  RETURN;
 END IF;
 IF NRORD$ IS NULL THEN
  SELECT MAX(NRORD)+1 INTO NRORD$ FROM XUNIVSET
  WHERE NVL(NRSET,0)=NVL(:NEW.NRSET,0);
 END IF;
 INSERT INTO XUNIVSET(NRSET,COD,NRORD,MARK,PARAM,BARCODE)
 VALUES (NVL(:NEW.NRSET,0),:NEW.COD,NVL(NRORD$,1),:NEW.MARK,:NEW.PARAM, :NEW.BARCODE);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
 UPDATE XUNIVSET SET NRORD=NVL(NRORD$,1),MARK=:NEW.MARK,PARAM=:NEW.PARAM
 WHERE NRSET=NVL(:NEW.NRSET,0) AND COD=:NEW.COD;
END;
/
----------------------------------------------------------------


