Home -> Usenet -> comp.databases.oracle -> Trigger Question

Trigger Question

From: Jerry Davidson <jerry_davidson_at_compuserve.com>
Date: Fri, 22 Mar 2002 10:48:13 -0600
Message-ID: <3C9B604D.90306_at_compuserve.com> 






I want to have a "Before Insert" trigger become conditional.  Here is 
the situation:



You can insert a new record from a screen (user types it in).  In that 
case, the trigger will access a sequence and use that to populate the 
field.  This works and here is the trigger





CREATE OR REPLACE TRIGGER USA_Contractor_BI


   BEFORE INSERT



   ON USA_Contractor


   FOR EACH ROW



declare


     newid number;


   begin

     select USA_Contractor_seq.nextval
       into newid from dual;



     :new.Contractor_Key := newid;

     :new.Contractor_ID := 'CID' || LPAD(TO_CHAR(newid), 5, '0');


     :new.Create_Date := sysdate;






END USA_Contractor_BI;





A second way a record can get into the table is if the user restores it 
from an archive table.  In that case I don't want the sequence to be 
triggered because it will screw up the ID.  I thought to add the following:





CREATE OR REPLACE TRIGGER USA_Contractor_BI


   BEFORE INSERT



   ON USA_Contractor


   FOR EACH ROW



   WHEN (:old.contractor_id IS NULL)


declare


     newid number;


   begin

     select USA_Contractor_seq.nextval
       into newid from dual;



     :new.Contractor_Key := newid;

     :new.Contractor_ID := 'CID' || LPAD(TO_CHAR(newid), 5, '0');


     :new.Create_Date := sysdate;






END USA_Contractor_BI;





The difference is the "WHEN" clause.  I'm trying to identify cases where 
the value is already there (archived records) using the "old" buffer.



Doesn't like the check for a null value.  What am I doing wrong?



TIA,



Jerry
Received on Fri Mar 22 2002 - 17:48:13 CET












Original text of this message