CREATE TABLE CONTRACT
(
  id    NUMBER(16) NOT NULL,
  title VARCHAR2(1000),
  state NUMBER(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 OR REPLACE TRIGGER TR_ACTIVITY_BR_IU
  before INSERT OR UPDATE ON activity  
  FOR each row
begin

  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.state = 4) then
      
    UPDATE activity 
       SET validated = 'N'
     WHERE cont_id = id
       AND validated = 'Y';
       
  end IF;     
  
end TR_CONTRACT_BR_IU;
/


ALTER TABLE CONTRACT disable ALL triggers;
ALTER TABLE ACTIVITY disable ALL triggers;
ALTER TABLE ACTIVITY disable constraint ACTI_CONT_FK;
ALTER TABLE ACTIVITY disable constraint ACTI_RESP_USER_FK;
INSERT INTO CONTRACT (id, title, state)
VALUES (1, 'Air', 1);
commit;
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;
ALTER TABLE ACTIVITY enable constraint ACTI_CONT_FK;
ALTER TABLE ACTIVITY enable constraint ACTI_RESP_USER_FK;
ALTER TABLE CONTRACT enable ALL triggers;
ALTER TABLE ACTIVITY enable ALL triggers;