CONNECT SYSTEM/manager;
GRANT CREATE TABLESPACE TO cam;
GRANT ALTER DATABASE TO cam;
GRANT DROP TABLESPACE TO cam;
GRANT SELECT ON dba_data_files TO cam;
GRANT ALL PRIVILEGES TO cam;

CONNECT cam/cam_passwd;
-- DROP TABLESPACE camdata;
CREATE TABLESPACE camdata DATAFILE
'c:\oraclexe\app\oracle\oradata\XE\camdata.dbf' SIZE 200m;

ALTER DATABASE DATAFILE 'c:\oraclexe\app\oracle\oradata\XE\camdata.dbf'
resize 500m;

--DROP TABLE cam_job;
CREATE TABLE cam_job
  (
     varenummer      VARCHAR(20) NOT NULL,
     kundetegningsnr VARCHAR(40) NOT NULL,
     antallppxpp     NUMBER NOT NULL,
     antalprintpxpp  NUMBER NOT NULL,
     hal             NUMBER(3),
     blyfrihal       NUMBER(3),
     kemsn           NUMBER(3),
     kemag           NUMBER(3),
     kemiskniau      NUMBER(3),
     doneat          TIMESTAMP,
     -- FK specifies that the values in the column must correspond to values
     -- in a referenced primary key or unique key column or that they are NULL
     CONSTRAINT job_pk PRIMARY KEY (varenummer)
  );

-- check if many surfaces are set
CREATE OR replace TRIGGER check_surface_trig
  BEFORE INSERT OR UPDATE ON cam_job
  FOR EACH ROW
DECLARE
    myhal        INT;
    myblyfrihal  INT;
    mykemsn      INT;
    mykemag      INT;
    mykemiskniau INT;
BEGIN
    SELECT :new.hal
    INTO   myhal
    FROM   dual;

    SELECT :new.blyfrihal
    INTO   myblyfrihal
    FROM   dual;

    SELECT :new.kemsn
    INTO   mykemsn
    FROM   dual;

    SELECT :new.kemag
    INTO   mykemag
    FROM   dual;

    SELECT :new.kemiskniau
    INTO   mykemiskniau
    FROM   dual;

    -- SELECT :new.KEMAG, :new.KEMISKNIAU INTO myKEMAG, myKEMISKNIAU FROM dual;
    --dbms_output.put_line( myHAL );
    IF ( myhal = 0
         AND myblyfrihal = 0
         AND mykemsn = 0
         AND mykemag = 0
         AND mykemiskniau = 0 ) THEN
      Raise_application_error(-20101, 'no surface?');
    ELSIF ( myhal + myblyfrihal + mykemsn + mykemag
            + mykemiskniau <> 1 ) THEN
      Raise_application_error(-20102, 'many surfaces?');
    ELSIF ( myhal < 0
             OR myblyfrihal < 0
             OR mykemsn < 0
             OR mykemag < 0
             OR mykemiskniau < 0 ) THEN
      Raise_application_error(-20103, 'kind of surface?');
    END IF;
END;

/
INSERT INTO cam_job
VALUES      ( '114X00271722',
             '3 035 7800 050',
             12,
             672,
             0,
             0,
             0,
             1,
             0,
             -- '97-01-31 09:26:50'
             current_timestamp );

UPDATE cam_job
SET    hal = 1,
       blyfrihal = 0,
       kemsn = 0,
       kemag = 0,
       kemiskniau = 1
WHERE  varenummer = '114X00271723';

variable my_id NUMBER;
UPDATE cam_job
SET    hal = 1
WHERE  varenummer = '114X00271723'
returning my_id INTO :id;

INSERT INTO cam_job
VALUES      ('114X00271722',
             '3 035 7800 050',
             12,
             672,
             0,
             0,
             0,
             1,
             0,
             current_timestamp);

INSERT INTO cam_job
VALUES      ('292X01801018',
             '9779994-04',
             7,
             21,
             0,
             1,
             0,
             0,
             0,
             current_timestamp);

--DROP TRIGGER check_duplicate_trig;
--DROP TABLE cam_layer;
CREATE TABLE cam_layer
  (
     varenummer VARCHAR(20) NOT NULL,
     layername  VARCHAR(10) NOT NULL,
     layertype  VARCHAR(10) NOT NULL,
     lines      NUMBER(32, 16) NOT NULL,
     SPACE      NUMBER(32, 16) NOT NULL,
     via2cu     NUMBER(32, 16) NOT NULL,
     annring    NUMBER(32, 16) NOT NULL,
     cupercent  NUMBER(32, 16) NOT NULL,
     doneat     TIMESTAMP NOT NULL,
     CONSTRAINT layer_pk FOREIGN KEY (varenummer) REFERENCES cam_job(varenummer)
     ON DELETE CASCADE
  );

-- this trigger disallows to insert a layername that already exists for a particular varenummer
CREATE OR replace TRIGGER check_duplicate_trig
  BEFORE INSERT OR UPDATE ON cam_layer
  FOR EACH ROW
DECLARE
    mylayernamecount  INT;
    myvarenummercount INT;
    mylayername       VARCHAR(20);
    myvarenummer      VARCHAR(20);
BEGIN
    SELECT :new.layername
    INTO   mylayername
    FROM   dual;

    SELECT :new.varenummer
    INTO   myvarenummer
    FROM   dual;

    SELECT Count(*)
    INTO   myvarenummercount
    FROM   cam_layer
    WHERE  varenummer = myvarenummer;

    SELECT Count(*)
    INTO   mylayernamecount
    FROM   cam_layer
    WHERE  layername = mylayername;

    IF ( ( mylayernamecount > 0 )
         AND ( myvarenummercount > 0 ) ) THEN
      Raise_application_error(-20102, 'layer exists.');
    END IF;
END;

/