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

From: Rod Stewart <rod.stewart_at_afp.gov.au>
Date: Fri, 5 Nov 1999 11:50:04 +1100
Message-ID: <7vt99c$md2$1@platinum.sge.net>

John,

Your trigger as such is fine, though this is not the way that I would approach this situation. There are two issues that you have here. Firstly, the trigger will not fire until after the insert statement has been parsed. Secondly, the insert statement will fail (and hence, won't get past being parsed) because it appears that your person_id column is mandatory. For your trigger to work, you have to modify your person_id column to be nullable and then do the insert as follows:

insert into person (

    person_id,
    person_last_name,
    person_first_name,
    person_middle_name)

values (
  null,
'ZZZ Test',

   'ZZZ Test first',
'ZZZ test middle');

or you could insert another known value, such as -1, and test for that in the trigger instead of testing for null.

A neater way though to get around the entire situation is just to include getting the sequence next value within the insert statement like so:

insert into person (

    person_id,
    person_last_name,
    person_first_name,
    person_middle_name)

values (
  person_s.nextval,
'ZZZ Test',

   'ZZZ Test first',
'ZZZ test middle');

It depends on your situation, but this is the way it would normally be done.

HTH Rod J. Stewart Received on Thu Nov 04 1999 - 18:50:04 CST