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;