CREATE TABLE CONTRACT
(
  id    NUMBER(16) NOT NULL,
  title VARCHAR2(1000),
  state NUMBER(1) DEFAULT 1
)
;
COMMENT ON column CONTRACT.id
  IS 'PK';
COMMENT ON column CONTRACT.title
  IS 'Contract title';
COMMENT ON column CONTRACT.state
  IS 'Contract state';
ALTER TABLE CONTRACT
  add constraint CONT_PK primary key (ID);

CREATE TABLE ACTIVITY
(
  id                  NUMBER(16) NOT NULL,
  cont_id             NUMBER(16),
  name                VARCHAR2(1000),
  cost                NUMBER(10,2),
  valid_from          DATE,
  valid_to            DATE,
  responsible_user_id NUMBER(16),
  validated           CHAR(1) DEFAULT 'Y'
)
;
COMMENT ON column ACTIVITY.id
  IS 'PK';
COMMENT ON column ACTIVITY.cont_id
  IS 'Contract FK';
COMMENT ON column ACTIVITY.name
  IS 'Activity name';
COMMENT ON column ACTIVITY.cost
  IS 'Activity costs';
COMMENT ON column ACTIVITY.valid_from
  IS 'Valid from';
COMMENT ON column ACTIVITY.valid_to
  IS 'Valid to';
COMMENT ON column ACTIVITY.responsible_user_id
  IS 'Responsible User FK';
COMMENT ON column ACTIVITY.validated
  IS 'Whether the activity is valid';
ALTER TABLE ACTIVITY
  add constraint ACTI_PK primary key (ID);
ALTER TABLE ACTIVITY
  add constraint ACTI_CONT_FK foreign key (CONT_ID)
  references CONTRACT (ID);
ALTER TABLE ACTIVITY
  add constraint ACTI_RESP_USER_FK foreign key (RESPONSIBLE_USER_ID)
  references USERS (ID);

CREATE sequence SEQ_ACTIVITY_ID
minvalue 1
maxvalue 999999999999999999999999999
START WITH 41
increment BY 1
cache 20
ORDER;

CREATE sequence SEQ_CONTRACT_ID
minvalue 1
maxvalue 999999999999999999999999999
START WITH 21
increment BY 1
cache 20
ORDER;

CREATE OR REPLACE force VIEW v_activities AS
SELECT a.id
      ,a.cont_id
      ,c.title cont_title
      ,a.name
      ,a.cost
      ,a.valid_from
      ,a.valid_to
  FROM activity a
      ,contract c
 WHERE a.cont_id = c.id;
  
CREATE OR REPLACE TRIGGER TR_ACTIVITY_BR_IU
  before INSERT OR UPDATE ON activity  
  FOR each ROW
BEGIN

  IF (:NEW.id IS NULL) THEN
    SELECT seq_activity_id.NEXTVAL INTO :NEW.id FROM dual;
  END IF;       
        
  IF (:NEW.validated = 'N') THEN
    
    IF (:NEW.valid_from IS NULL) THEN
      raise_application_error(-20000, 'Valid from can''t be null!');
    END IF;
  
  END IF;
  
END TR_ACTIVITY_BR_IU;
/

CREATE OR REPLACE TRIGGER TR_CONTRACT_BR_IU
  before INSERT OR UPDATE ON contract  
  FOR each ROW
BEGIN

  IF (:NEW.id IS NULL) THEN
    SELECT seq_contract_id.NEXTVAL INTO :NEW.id FROM dual;
  END IF;       
        
  IF (:NEW.state = 4) THEN
      
    UPDATE activity 
       SET validated = 'N'
     WHERE cont_id = id
       AND validated = 'Y';
       
  END IF;     
  
END TR_CONTRACT_BR_IU;
/

prompt Disabling triggers FOR CONTRACT...
ALTER TABLE CONTRACT disable ALL triggers;
prompt Disabling triggers FOR ACTIVITY...
ALTER TABLE ACTIVITY disable ALL triggers;
prompt Disabling foreign key constraints FOR ACTIVITY...
ALTER TABLE ACTIVITY disable constraint ACTI_CONT_FK;
ALTER TABLE ACTIVITY disable constraint ACTI_RESP_USER_FK;
prompt Loading CONTRACT...
INSERT INTO CONTRACT (id, title, state)
VALUES (1, 'Air', 1);
COMMIT;
prompt 1 records loaded
prompt Loading ACTIVITY...
INSERT INTO ACTIVITY (id, cont_id, name, cost, valid_from, valid_to, responsible_user_id, validated)
VALUES (1, 1, 'Part 1', .2, NULL, NULL, NULL, 'Y');
COMMIT;
prompt 1 records loaded
prompt Enabling foreign key constraints FOR ACTIVITY...
ALTER TABLE ACTIVITY enable constraint ACTI_CONT_FK;
ALTER TABLE ACTIVITY enable constraint ACTI_RESP_USER_FK;
prompt Enabling triggers FOR CONTRACT...
ALTER TABLE CONTRACT enable ALL triggers;
prompt Enabling triggers FOR ACTIVITY...
ALTER TABLE ACTIVITY enable ALL triggers;