REM Copyright (c) 2003 Konrad Buessow

REM This program is free software; you can redistribute and/or modify 
REM it under the terms of the GNU General Public Licence as published by
REM the Free Software Foundation; eiher version 2, or (at your option) any
REM later version.

REM This program is distributed in the hope that it will be useful, but 
REM WITHOUT ANY WARRANTY; without even the implied warranty of 
REM MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
REM GNU General Public Licence for more details. 

REM You should have received a copy of the GNU General Public Licence 
REM along with this program; if not, write to the Free Software 
REM Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA


-- 
-- TRIGGER: TR_SEQ_303_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_303_1
BEFORE INSERT 
ON ANTIBIOTICS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: BARCODE_INS 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER BARCODE_INS
BEFORE INSERT 
ON BARCODE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
begin
 select BIG_SEQ.NEXTVAL
  into :new.barcode
   from dual;
end;
 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: INSUPDATE_BARCODE_TRIG 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER INSUPDATE_BARCODE_TRIG
BEFORE INSERT OR UPDATE OF CLASS,PLABO_ADMINID,PLATESBOXESID,PLATE_NUMBER 
ON BARCODE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
DECLARE
  kontrollsumme number;
BEGIN
  /*
  35 = well in plate or tube in box
  36 = plate or box
  */
  IF :new.class = 35 AND (
  :new.platesboxesid IS NULL OR
  :new.plabo_adminid IS NOT NULL OR
  :new.plate_number IS NOT NULL) THEN
    RAISE_APPLICATION_ERROR(
      -20001,
      'Well in plate or tube in box, please enter platesboxesid, but not plabo_adminid (' ||
      :new.plabo_adminid || ') or plate_number (' || :new.plate_number || ') !');
  ELSIF :new.class = 36 AND (
  :new.platesboxesid IS NOT NULL OR 
  :new.plabo_adminid IS NULL OR
  :new.plate_number IS NULL) THEN
    RAISE_APPLICATION_ERROR(-20002,'Plate or box, please enter plabo_adminid and plate_number, but not platesboxesid');
  END IF;
  
/*
  if :new.class = 35 and check_delivery(:new.platesboxesid) <> 0 then
    RAISE_APPLICATION_ERROR( 
      -20003,'The vector of this clone cannot be determined. Do not deliver.');
  end if;
  
  if :new.class = 36 then
    select sum(check_delivery(platesboxesid))  
    into kontrollsumme
    from platesboxes 
    where platesboxesadminid = :new.plabo_adminid
    and plate_number = :new.plate_number;
    if kontrollsumme <> 0 then
      RAISE_APPLICATION_ERROR(
        -20003,'The vector of one of these clones cannot be determined. Do not deliver.');
    end if;
  end if;    
*/
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_306_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_306_1
BEFORE INSERT 
ON BUFFERS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.buffer_ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 


-- 
-- TRIGGER: TR_SEQ_308_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_308_1
BEFORE INSERT 
ON CLONEIMAGE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.CLONEIMAGEID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_309_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_309_1
BEFORE INSERT 
ON CLONEINDEX
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
DECLARE
  mycurrval NUMBER; 
  seq_not_initialised EXCEPTION;
  PRAGMA EXCEPTION_INIT(seq_not_initialised, -08002); 
BEGIN
  BEGIN
    SELECT BIG_SEQ.CURRVAL INTO mycurrval FROM dual;
  EXCEPTION
    WHEN seq_not_initialised THEN 
      SELECT BIG_SEQ.NEXTVAL INTO mycurrval FROM dual;
  END;
  IF :new.cloneid IS NULL 
  THEN
    SELECT BIG_SEQ.NEXTVAL
    INTO :new.cloneid
    FROM dual;
  ELSIF :new.cloneid > mycurrval THEN 
    RAISE_APPLICATION_ERROR(-20001,'Cannot insert ' || :new.cloneid || 
      ' into CLONEINDEX because the value is larger than ' || mycurrval);  
  END IF; 
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: INSTRIGGER_CLONEOTHER 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER INSTRIGGER_CLONEOTHER
BEFORE INSERT 
ON CLONEOTHER
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
 SELECT BIG_SEQ.NEXTVAL
  INTO :new.cloneotherid
   FROM dual;
END;


 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_311_2 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_311_2
BEFORE INSERT 
ON CLONEOTHER
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.CLONEOTHERID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: CLONESRZPD_CLONENAME_CHECK 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER CLONESRZPD_CLONENAME_CHECK
BEFORE INSERT OR UPDATE OF CLONENAME,COLUMN_,LIBNO,PLATE,ROW_ 
ON CLONESRZPD
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
  IF :new.LIBNO IS NULL AND :new.ROW_ IS NULL AND :new.COLUMN_ IS NULL
    AND :new.PLATE IS NULL THEN
    
    :new.LIBNO := SUBSTR(:new.CLONENAME,6,3);
    :new.ROW_ := SUBSTR(:new.CLONENAME,9,1);
    :new.COLUMN_ := SUBSTR(:new.CLONENAME,10,2);
    :new.PLATE := SUBSTR(:new.CLONENAME,12);
   END IF;
   
   IF (:new.LIBNO <> SUBSTR(:new.CLONENAME,6,3)) THEN
      RAISE_APPLICATION_ERROR(-20000,'CLONENAME and LIBNO disagree.');
   END IF;
   IF (:new.ROW_ <> SUBSTR(:new.CLONENAME,9,1)) THEN
      RAISE_APPLICATION_ERROR(-20000,'CLONENAME and ROW_ disagree.');
   END IF;
  IF (:new.ROW_ NOT In ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P')) THEN
    raise_application_error(-20000,'Check Constraint on Column ROW_ Violated...');
  END IF;
   IF (:new.COLUMN_ <> SUBSTR(:new.CLONENAME,10,2)) THEN
      RAISE_APPLICATION_ERROR(-20000,'CLONENAME and COLUMN_ disagree.');
   END IF;
   IF NOT (:new.COLUMN_ < 25 AND :new.COLUMN_ > 0) THEN
       raise_application_error(-20000,'Check Constraint on Column COLUMN_ Violated...');
   END IF;
   IF (:new.PLATE <> SUBSTR(:new.CLONENAME,12)) THEN
      RAISE_APPLICATION_ERROR(-20000,'CLONENAME and PLATE disagree.');
   END IF;

 END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_313_7 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_313_7
BEFORE INSERT 
ON CLONESRZPD
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
DECLARE 
  mycurrval NUMBER;
  newcloneid cloneindex.cloneid%TYPE;
  seq_not_initialised EXCEPTION;
  got_no_cloneid EXCEPTION;
  PRAGMA EXCEPTION_INIT(seq_not_initialised, -08002); 
  /* When BIG_SEQ.CURRVAL is called before BIG_SEQ.NEXTVAL in the session,
  ORA-08002 error is raised. */
BEGIN
  /* If necessary, generate clone in CLONEINDEX and write the generated CloneID
  into CLONESRZPD. */
  /* get the last value from the sequence SEQ_309_1, which is used to generate
  the cloneIDs in CLONEINDEX. */
  BEGIN
    SELECT BIG_SEQ.CURRVAL INTO mycurrval FROM dual; 
    /* mycurrval := BIG_SEQ.CURRVAL; doesn't work because CURRVAL is a pseudo column*/
  EXCEPTION
    WHEN seq_not_initialised THEN
      SELECT BIG_SEQ.NEXTVAL INTO mycurrval FROM dual;
  END;
  
  IF :new.cloneid IS NULL 
  THEN
    BEGIN
      /* Generate new CloneIndex entry with indexclonename = :new.clonename. */
      newcloneid := clones.ins_cloneindex(:new.clonename,1,0,0);
      IF newcloneid IS NULL THEN
        RAISE got_no_cloneid;
      ELSE
        :new.cloneid := newcloneid;
      END IF;
    EXCEPTION
        WHEN got_no_cloneid THEN
          RAISE_APPLICATION_ERROR(-20004,'ERROR: Could not generate CloneID. Clone Name ' ||
          :new.clonename || ' may already be present in Cloneindex!');
    END;
    
  ELSIF :new.cloneid > mycurrval THEN 
    /* a cloneid was supplied which was not created with SEQ_309_1 */
    RAISE_APPLICATION_ERROR(-20001,'Cannot insert ' || :new.cloneid || 
      ' into CLONESRZPD because the value is larger than BIG_SEQ.CURRVAL ' || mycurrval);
  END IF;
  /* generate clonesrzpd.ID */
  SELECT BIG_SEQ.NEXTVAL 
  INTO :new.ID FROM dual; 
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_316_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_316_1
BEFORE INSERT 
ON COLUMNS_
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.column_ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 


-- 
-- TRIGGER: DNAAC_TRG2 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER DNAAC_TRG2
BEFORE INSERT 
ON DNA_ACTION
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.DNAACTIONID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_317_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_317_1
BEFORE INSERT 
ON DNALANES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_322_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_322_1
BEFORE INSERT 
ON EMPTYCOLUMNTYPES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_323_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_323_1
BEFORE INSERT 
ON EXPERIMENTATOR
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: EXP_TRIG 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER EXP_TRIG
BEFORE INSERT 
ON EXPERIMENTS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
begin
 select EXPERIMENT_SEQ.NEXTVAL
  into :new.exp_id
   from dual;
end;
 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: FVR_376_EXPRESSIONCLONES 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER FVR_376_EXPRESSIONCLONES
BEFORE INSERT OR UPDATE 
ON EXPRESSIONCLONES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
   IF NOT (:new.wholeProtStrength < 4 OR :new.wholeProtStrength is Null) THEN
       raise_application_error(-20000,'Check Constraint on Column wholeProtStrength Violated...');
   END IF;
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: FVR_378_EXPRESSIONCLONES 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER FVR_378_EXPRESSIONCLONES
BEFORE INSERT OR UPDATE 
ON EXPRESSIONCLONES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
   IF NOT (:new.solubleProtStrength < 4 OR :new.solubleProtStrength is Null) THEN
       raise_application_error(-20000,'Check Constraint on Column solubleProtStrength Violated...');
   END IF;
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: FVR_380_EXPRESSIONCLONES 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER FVR_380_EXPRESSIONCLONES
BEFORE INSERT OR UPDATE 
ON EXPRESSIONCLONES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
   IF NOT (:new.wholeNiNTAStrength < 4 OR :new.wholeNiNTAStrength is Null) THEN
       raise_application_error(-20000,'Check Constraint on Column wholeNiNTAStrength Violated...');
   END IF;
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: FVR_382_EXPRESSIONCLONES 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER FVR_382_EXPRESSIONCLONES
BEFORE INSERT OR UPDATE 
ON EXPRESSIONCLONES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
   IF NOT (:new.solubleNiNTAStrength < 4 OR :new.solubleNiNTAStrength is Null) THEN
       raise_application_error(-20000,'Check Constraint on Column solubleNiNTAStrength Violated...');
   END IF;
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_325_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_325_1
BEFORE INSERT 
ON EXPRESSIONCLONES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: FVR_384_GENBANKENTRIES 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER FVR_384_GENBANKENTRIES
BEFORE INSERT OR UPDATE 
ON GENBANKENTRIES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
   IF NOT (:new.cellLoc='extra'  OR :new.cellLoc='intra'  OR :new.cellLoc='unknown'  OR :new.cellLoc is Null) THEN
       raise_application_error(-20000,'Check Constraint on Column cellLoc Violated...');
   END IF;
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: FVR_388_GENBANKENTRIES 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER FVR_388_GENBANKENTRIES
BEFORE INSERT OR UPDATE OF TYPE_ 
ON GENBANKENTRIES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
   IF NOT (:new.TYPE_='mRNA'  OR :new.TYPE_='gene'  OR :new.TYPE_='protein' OR :new.TYPE_='DNA') THEN
       raise_application_error(-20000,'Check Constraint on Column TYPE_ Violated...');
   END IF;
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: GBENTRIES_INSMOLWEIGHT 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER GBENTRIES_INSMOLWEIGHT
BEFORE INSERT OR UPDATE OF MOL_WEIGHT,SEQPEP_ID,SEQ_DB 
ON GENBANKENTRIES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
begin
  if :new.mol_weight is null and :new.seqpep_id is not null and not :new.seq_db like 'pdb' then
      select seq_dnapep.get_pepweight(seq) into :new.mol_weight from seq_pep
      where :new.seqpep_id = seq_pep.seqpep_id;
  end if;
end; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: GENBANKENTRIES_CHECK_SEQDB 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER GENBANKENTRIES_CHECK_SEQDB
BEFORE INSERT OR UPDATE OF SEQ_DB 
ON GENBANKENTRIES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
begin
  if not (:new.seq_db in ('genembl','genpept','sp_trembl','genbank_pdb','pdb','enst','ensp')) then
  raise_application_error(-20000,'Please choose from genembl, genpept, sp_trembl, enst, ensp (Ensembl) and pdb.');
  end if;
end; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_329_11 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_329_11
BEFORE INSERT 
ON GENBANKENTRIES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: LIGATION_PCR_OR_INSERTCLONE 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER LIGATION_PCR_OR_INSERTCLONE
BEFORE INSERT OR UPDATE OF INSERTCLONEID,INSERT_IS_RE_FRAGMENT,PCREXPID 
ON LIGATION
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
DECLARE
  pcr_and_insertclone EXCEPTION;
BEGIN
  IF (:new.pcrexpid IS NOT NULL AND :new.insertcloneid IS NOT NULL) THEN
      RAISE_APPLICATION_ERROR
      (-20000,'Please do not enter a PCR AND an insert clone. PCR is sufficient.');
  END IF;
  IF :new.pcrexpid IS NOT NULL THEN
    IF :new.insert_is_re_fragment IS NULL THEN
      :new.insert_is_re_fragment := 0;
    ELSIF :new.insert_is_re_fragment <> 0 THEN
      RAISE_APPLICATION_ERROR
      (-20001,'PCR and RE_FRAGMENT are mutually exclusive. Ligate either PCR or RE fragment.');
    END IF;
  END IF;     
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_335_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_335_1
BEFORE INSERT 
ON LIGATION
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.LIGATIONID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- TRIGGER: TR_SEQ_340_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_340_1
BEFORE INSERT 
ON OLIGOCLEANINGKITS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: INS_PAGE 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER INS_PAGE
BEFORE INSERT 
ON PAGE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
begin

  if :new.experimentator_id = 7 then
    raise_application_error(-20001,'Who is the Experimentator??');
  end if; 


  SELECT PAGE_SEQ.NEXTVAL
   INTO :new.pageid
    from dual;
end; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_342_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_342_1
BEFORE INSERT 
ON PAGELANES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: FVR_390_PCREXPERIMENT 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER FVR_390_PCREXPERIMENT
BEFORE INSERT OR UPDATE OF GENERIC_TEMPLATE_ID,PRODUCTAMOUNT,TEMPLATECLONEID 
ON PCREXPERIMENT
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
DECLARE
  dummy NUMBER;
  redundant_template EXCEPTION;
  no_template EXCEPTION;
BEGIN
    /* check for correct amount */
    IF (:new.productAmount < 0 OR :new.productAmount >= 6 OR :new.productAmount is Null) THEN
        raise_application_error(-20000,'Check Constraint on Column productAmount Violated...');
    END IF;
   
  /* check for correct template information */
    /* check that exactly one template is entered */
  BEGIN  
    IF 
      :new.generic_template_id IS NULL AND 
      :new.templatecloneid IS NULL AND 
      :new.template_pcrid IS NULL THEN
        raise no_template;
    ELSIF 
      (:new.generic_template_id IS NOT NULL AND 
      (:new.templatecloneid IS NOT NULL OR 
      :new.template_pcrid IS NOT NULL)) OR
      (:new.templatecloneid IS NOT NULL AND 
      :new.template_pcrid IS NOT NULL) THEN
        raise redundant_template;
    END IF;      
  EXCEPTION
    WHEN redundant_template THEN
      raise_application_error(-20002,'Enter EITHER a clone OR a PCR OR a generic template as template.');
    WHEN no_template THEN
      raise_application_error(-20001,'What is the template? Enter template clone or PCR or generic template.');
  END;
    /* check that a generic template is entered correctly. */  
  IF :new.generic_template_id IS NOT NULL THEN
        BEGIN
          SELECT flag_value
            INTO dummy 
            FROM flag_col 
           WHERE table_name = 'PCREXPERIMENT' 
             AND col_name = 'GENERIC_TEMPLATE_ID'
             AND flag_value = :new.generic_template_id;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            raise_application_error(-20003,'Cannot find this generic_template_id ' ||
            :new.generic_template_id || ' in table flag_col.');
        END;
  END IF;
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_345_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_345_1
BEFORE INSERT 
ON PCREXPERIMENT
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.PCREXPERIMENTID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: FVR_392_PCRPRIMER 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER FVR_392_PCRPRIMER
BEFORE INSERT OR UPDATE 
ON PCRPRIMER
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
   IF NOT (:new.UpperLowerStrand='U'  OR :new.UpperLowerStrand = 'L' OR :new.UpperLowerStrand is Null) THEN
       raise_application_error(-20000,'Check Constraint on Column UpperLowerStrand Violated...');
   END IF;
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_347_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_347_1
BEFORE INSERT 
ON PCRPRIMER
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_350_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_350_1
BEFORE INSERT 
ON PCRPROTOCOL
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 


-- 
-- TRIGGER: PLABOLOC_TRG 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER PLABOLOC_TRG
BEFORE INSERT 
ON PLABO_LOC
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.PLABO_LOC_ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: INSTRIGG_PB_GLYCID 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER INSTRIGG_PB_GLYCID
BEFORE INSERT 
ON PLATESBOXES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
begin
  SELECT BIG_SEQ.NEXTVAL
    INTO :new.PLATESBOXESID
      FROM dual;
   if ( :new.platesboxesadminid  = 96) then
     select BIG_SEQ.NEXTVAL
      into :new.glycerol_id
       from dual;
   end if;
 end;

 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: INSUPDATE_PLABO_TRIG 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER INSUPDATE_PLABO_TRIG
BEFORE INSERT OR UPDATE OF CLONEID,PLATESBOXESADMINID,PROTEIN_ID 
ON PLATESBOXES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
DECLARE
  new_content NUMBER;
BEGIN
  SELECT contentid
  INTO new_content
   FROM platesboxesadmin
  WHERE platesboxesadminid = :new.platesboxesadminid;
  IF new_content = 6 AND (
  :new.cloneid IS NOT NULL OR :new.protein_id IS NULL) THEN
    RAISE_APPLICATION_ERROR(-20001,'Achtung! Please enter Protein_ID but not Clone ID!');
  END IF;
END;  
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 



-- 
-- TRIGGER: TR_SEQ_354_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_354_1
BEFORE INSERT 
ON PLATESBOXESADMIN
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.PLATESBOXESADMINID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: INSTRIGGER_PROTEXPRESS 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER INSTRIGGER_PROTEXPRESS
BEFORE INSERT 
ON PROT_EXPRESS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
begin

  if :new.experimentator_id = 7 then
    raise_application_error(-20001,'Who is the Experimentator??');
  end if; 
  
  if :new.protocol_id is null then
    raise_application_error(-20002,'Please enter the protocol ID!');
  end if; 
   
  select BIG_SEQ.NEXTVAL
  into :new.protexpress_id
  from dual;
   
end; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: PROTEXPRESS_UPDATE_TRIG 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER PROTEXPRESS_UPDATE_TRIG
BEFORE INSERT OR UPDATE OF SNS_0TO3,SNS_PAGELANE,SPS_0TO3,SPS_PAGELANE,WNS_0TO3,WNS_PAGELANE,WPS_0TO3,WPS_PAGELANE 
ON PROT_EXPRESS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
begin
/*
  if 
    :new.wps_0to3 is null and   
    :new.sps_0to3 is null and   
    :new.wns_0to3 is null and   
    :new.sns_0to3 is null then
      raise_application_error(-20000,'Please enter the expression strength!');   
  end if;
*/
  if 
    :new.WPS_PAGELANE is null and   
    :new.sps_PAGELANE is null and   
    :new.wns_PAGELANE is null and   
    :new.sns_PAGELANE is null then
      raise_application_error(-20000,'Where is the gel for this experiment?');   
  end if;
  if 
    :new.WPS_PAGELANE in (:new.sps_PAGELANE,:new.wns_PAGELANE,:new.sns_PAGELANE) or
    :new.SPS_PAGELANE in (:new.wns_PAGELANE,:new.sns_PAGELANE) or
    :new.SNS_PAGELANE = :new.wns_PAGELANE
  then
      raise_application_error(-20000,'The same page gel lane was entered for different experiments!');   
  end if;  
end; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: PROT_PREPS_INSTRIG 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER PROT_PREPS_INSTRIG
BEFORE INSERT 
ON PROT_PREPS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  
  SELECT BIG_SEQ.NEXTVAL 
  INTO :new.protein_id 
  FROM dual; 
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: FVR_396_RESIN 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER FVR_396_RESIN
BEFORE INSERT OR UPDATE 
ON RESIN
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
 IF NOT (:new.mediumType = 'IEC-cation' OR :new.mediumType = 'IEC-anion' OR :new.mediumType = 'Aff' OR :new.mediumType = 'Chr' OR :new.mediumType = 'RP' OR
 :new.mediumType = 'HIC' OR :new.mediumType = 'GF') THEN
  raise_application_error(-20000,'Please enter IEC-cation, IEC-anion, GF, Chr, RP, Aff or RP.');
 END IF;
END;
 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_357_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_357_1
BEFORE INSERT 
ON RESIN
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.ID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_358_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_358_1
BEFORE INSERT 
ON RESTRICTIONENZYMES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.ENZYMEID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: INSTRIG_ROBOTJOBS 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER INSTRIG_ROBOTJOBS
BEFORE INSERT 
ON ROBOT_JOBS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
begin
 select BIG_SEQ.NEXTVAL
  into :new.id
   from dual;
end; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: ROBOT_JOBSDelTrig 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER ROBOT_JOBSDelTrig BEFORE DELETE ON ROBOT_JOBS 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW 
    DECLARE RowCount NUMBER; 
BEGIN 
-- Parent Delete: CASCADE 
    DELETE FROM ROBOT_REARRAY 
        WHERE JOB_ID = :OLD.ID; 
 
END; 
 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: INSTRIG_ROBOT_REARRAY 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER INSTRIG_ROBOT_REARRAY
BEFORE INSERT 
ON ROBOT_REARRAY
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
begin
  select BIG_SEQ.NEXTVAL
into :new.id
from dual;
end; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

--
-- TRIGGER: SEQDNA_INSTRIG 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER SEQDNA_INSTRIG
BEFORE INSERT 
ON SEQ_DNA
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
DECLARE
  mycurrval NUMBER; 
  seq_not_initialised EXCEPTION;
  PRAGMA EXCEPTION_INIT(seq_not_initialised, -08002); 
BEGIN
  BEGIN
    SELECT big_seq.CURRVAL INTO mycurrval FROM dual;
  EXCEPTION
    WHEN seq_not_initialised THEN 
      SELECT BIG_SEQ.NEXTVAL INTO mycurrval FROM dual;
  END;
  IF :new.seqdna_id IS NULL 
  THEN
    SELECT BIG_SEQ.NEXTVAL
    INTO :new.seqdna_id
    FROM dual;
  ELSIF :new.seqdna_id > mycurrval THEN 
    RAISE_APPLICATION_ERROR(-20001,'Cannot insert ' || :new.seqdna_id || 
      ' into seqdna because the value is larger than ' || mycurrval);  
  END IF; 
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: SEQPEP_INSTRIG 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER SEQPEP_INSTRIG
BEFORE INSERT 
ON SEQ_PEP
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
DECLARE
  mycurrval NUMBER; 
  seq_not_initialised EXCEPTION;
  PRAGMA EXCEPTION_INIT(seq_not_initialised, -08002); 
BEGIN
  BEGIN
    SELECT big_seq.CURRVAL INTO mycurrval FROM dual;
  EXCEPTION
    WHEN seq_not_initialised THEN 
      SELECT BIG_SEQ.NEXTVAL INTO mycurrval FROM dual;
  END;
  IF :new.seqpep_id IS NULL 
  THEN
    SELECT BIG_SEQ.NEXTVAL
    INTO :new.seqpep_id
    FROM dual;
  ELSIF :new.seqpep_id > mycurrval THEN 
    RAISE_APPLICATION_ERROR(-20001,'Cannot insert ' || :new.seqpep_id || 
      ' into seqdna because the value is larger than ' || mycurrval);  
  END IF; 
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_359_7 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_359_7
BEFORE INSERT 
ON SEQUENCING
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.SEQEXPID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_362_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_362_1
BEFORE INSERT 
ON SPECIES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.SPECIESNO FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_363_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_363_1
BEFORE INSERT 
ON STRAINS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.STRAINID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: INSTRIGG_TRANSFORMATIONMETHOD 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER INSTRIGG_TRANSFORMATIONMETHOD
BEFORE INSERT 
ON TRANSFORMATIONMETHOD
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
begin
select  BIG_SEQ.NEXTVAL
into :new.method_id
from dual;
end;
 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: INSTRIGG_TRANSFORMATIONS 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER INSTRIGG_TRANSFORMATIONS
BEFORE INSERT 
ON TRANSFORMATIONS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
  SELECT BIG_SEQ.NEXTVAL
    INTO :new.Trafo_ID
      FROM dual;
END;
 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

-- 
-- TRIGGER: TR_SEQ_367_1 
--

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 
CREATE OR REPLACE TRIGGER TR_SEQ_367_1
BEFORE INSERT 
ON VECTORS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN  SELECT BIG_SEQ.NEXTVAL INTO :new.VECTORID FROM dual; END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 

CREATE OR REPLACE TRIGGER INREASECLONELIGATIONID BEFORE INSERT 
ON CLONELIGATION 
FOR EACH ROW BEGIN  
    SELECT BIG_SEQ.NEXTVAL 
    INTO :new.LIGCLONEID 
    FROM dual; 
END;
/