SchemaChange.sql

CREATE TABLE  M_LAB_MACHINES (	
    MACHINE_ID NUMBER NOT NULL ENABLE, 
	MACHINE_NAME VARCHAR2(500) NOT NULL ENABLE, 
	MACHINE_TYPE VARCHAR2(500), 
	MACHINE_MODEL VARCHAR2(500), 
	CONSTRAINT M_LAB_MACHINES_PK PRIMARY KEY (MACHINE_ID) ENABLE);
/
	
insert into M_LAB_MACHINES (MACHINE_ID,MACHINE_NAME,MACHINE_TYPE,MACHINE_MODEL) values (1, 'Mythic', 'HEMATOLOGY', '18');
/

drop table M_METHODS_EXP;
/

CREATE TABLE  M_METHODS_EXP  (	
    METHOD_ACRONYM VARCHAR2(2000) NOT NULL ENABLE, 
	MACHINE_ID NUMBER NOT NULL ENABLE, 
	PARAMETER_ID NUMBER NOT NULL ENABLE, 
	CONSTRAINT M_METHODS_EXP_PK PRIMARY KEY (MACHINE_ID, PARAMETER_ID) ENABLE);
/

ALTER TABLE  M_METHODS_EXP ADD CONSTRAINT M_METHODS_EXP_FK1 FOREIGN KEY (PARAMETER_ID)
	  REFERENCES  M_PARAMETERS (PARAMETER_ID) ON DELETE CASCADE ENABLE;
/

ALTER TABLE  M_METHODS_EXP ADD CONSTRAINT M_METHODS_EXP_FK2 FOREIGN KEY (MACHINE_ID)
	  REFERENCES  M_LAB_MACHINES (MACHINE_ID) ON DELETE CASCADE ENABLE;
/
	  
insert into M_METHODS_EXP (METHOD_ACRONYM,MACHINE_ID,PARAMETER_ID) select ACRONYM,1,PARAMETER_ID from m_parameters where ACRONYM is not NULL;

ALTER TABLE m_parameters SET UNUSED (ACRONYM);
/
ALTER TABLE m_parameters DROP UNUSED COLUMNS;
/

create or replace TRIGGER BI_DATE_USER
  before insert on T_RESULTS               
  for each row  
begin   
    select USER into :NEW.UPDATED_BY from dual;    
    SELECT SYSDATE into :NEW.UPDATE_DATE from dual;    
end;
/