Re: Trigger Question

Jerry Davidson <jerry_davidson_at_compuserve.com> wrote in news: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?

You can't check for :old on an insert. :old.* will always be null on an insert. You should be checking WHEN (:new.contractor_id IS NULL). If the record being inserted already has a contractor_id then the trigger will not fire.

-- 
Ken Denny
http://www.kendenny.com/

An unemployed court jester is nobody's fool.