Re: INSERT trigger doesn't work...why not?

Hi,

the trigger is not being ignored, it lets your original insert unchanged and tries to insert a second row. (if your first insert succeeded, you should have gotten a mutating table error anyway.)

you can test this by removing the not null constraint from cc.person.person_id.

the way to go is to overwrite the values of the currently active insert.

create or replace trigger cc.person_insert_trigger   before insert
  on cc.person
  for each row
  when (new.person_id is null)
begin
  select cc.person_s.nextval into :new.person_id from dual; end;

Karsten

In article <9epU3.669$_Y3.23808_at_dfiatx1-snr1.gtei.net>,   "John Haskins" <76054.334SPAMBEGONE_at_compuserve.com> wrote:
> Greetings:
>
> I've created a simple trigger to ensure that new records always have
an ID
> number. The trigger checks whether the ID column is null, and if so,
uses a
> sequence to generate a value to include. However, the trigger is
being
> ignored. (Yes, it's enabled.) I'd appreciate if anyone has an idea
about
> what to do to make it get used.
>
> The trigger is:
> create or replace trigger cc.person_insert_trigger
> before insert
> on cc.person
> for each row
> when (new.person_id is null)
> begin
> insert into cc.person(
> person_id,
> person_last_name,
> person_first_name,
> person_middle_name
> )
> values (
> cc.person_s.nextval,
> :new.person_last_name,
> :new.person_first_name,
> :new.person_middle_name
> )
> ;
> end;
> /
>
> When I issue this DML command:
> insert into person (
> person_last_name,
> person_first_name,
> person_middle_name
> )
> values ('ZZZ Test',
> 'ZZZ Test first',
> 'ZZZ test middle'
> )
> ;
>
> I get the following PL/SQL error message:
> insert into person (
> *
> ERROR at line 1:
> ORA-01400: cannot insert NULL into ("CC"."PERSON"."PERSON_ID")
>
> Any assistance on this will be much appreciated!
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 05 1999 - 02:50:18 CST