sql oracle exo conception

gistfile1.sql

-- CREATE customer table
CREATE TABLE customer
( customer_id number(10) ,
  name varchar2(50) not null,
  date_naissance DATE,
  date_creation DATE DEFAULT sysdate not null ,
  CONSTRAINT customer_id_pk PRIMARY KEY (customer_id),
  CONSTRAINT customer_u UNIQUE(name, date_naissance)
);

-- CREATE status table for easy update status list
CREATE TABLE status(
	status_id number(10),
	status_detail varchar2(50) not null,
	CONSTRAINT status_id_pk PRIMARY KEY (status_id)
);

INSERT INTO status (status_id, status_detail) values(1,'CREATED');
INSERT INTO status (status_id, status_detail) values(2,'IN PROGRESS');
INSERT INTO status (status_id, status_detail) values(3,'SUCCESS');
INSERT INTO status (status_id, status_detail) values(4,'ERROR');
-- CREATE order table

CREATE TABLE orders(
	order_id NUMBER NOT NULL PRIMARY KEY,
	begin_order TIMESTAMP(9) DEFAULT SYSTIMESTAMP not null ,
	end_order TIMESTAMP(9),
	customer_id number(10) not null,
	quantity number(10,3) not null,
	status_id number(10),
	code_action varchar2(18) not null,
	CONSTRAINT code_action_ck CHECK (LENGTH(code_action) = 18),
	CONSTRAINT customer_id_fk FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON DELETE CASCADE,
	CONSTRAINT order_date CHECK (end_order > begin_order),
	CONSTRAINT quantity_ck CHECK (quantity <> 0),
	CONSTRAINT status_id_fk FOREIGN KEY (status_id) REFERENCES status(status_id)
);



--  Il ne doit y avoir QU’UN seul ordre passé ou en cours pour un client donné. Plusieurs ordres en succès ou en échec sont possibles.
CREATE UNIQUE INDEX orders_status ON orders
   (CASE WHEN status_id =1  or status_id = 2 THEN customer_id ELSE NULL END);


--La suppression d’un client effacera tous les ordres qu’il a passé SAUF si un ordre est en cours

CREATE OR REPLACE TRIGGER orders_client_delete
   BEFORE DELETE ON orders
   FOR EACH ROW

   DECLARE
     duplicate_info EXCEPTION;
     PRAGMA EXCEPTION_INIT (duplicate_info, -20001);
   BEGIN
   IF :old.status_id = 2 THEN
   	RAISE duplicate_info;
   END IF;
   EXCEPTION
     WHEN duplicate_info THEN
      raise_application_error(
      -20001, 'Order does not successful or in error');
   END orders_client_delete;
/
commit;