Re: I'm sorry, another dumb question about Triggers

Hi Alan Mills, thanks for writing this:
>
> "tk" <theronk_at_charter.net> wrote in message
> news:vj4mfs6sii9u3c_at_corp.supernews.com...
> > I promise to keep my questions to a minimum after this... Pardon my
> > ignorance but I'm going thru the trigger examples in the Oracle 8i PL/SQL
> > book. p. 270 has the following trigger:
> > --------
> > CREATE OR REPLACE TRIGGER GenerateStudentID
> > BEFORE INSERT OR UPDATE ON students
> > FOR EACH ROW
> > BEGIN
> > /* Fill in the ID field of students with the next value from
> > student_sequence. Since ID is a column in students, :new.ID
> > is a valid reference. */
> > SELECT student_sequence.NEXTVAL
> > INTO :new.ID
> > FROM dual;
> > END GenerateStudentID;
> > --------
> > I understand clearly what this is doing. However, I'm dumbfounded as to
> > "why" they have this also called on an UPDATE statement?
> > When I issue the following update:
> >
> > update students set major = 'Computer Science' where id = 10020;
> >
> > The actual "id" gets changed from 10020 to 10021 due to the trigger logic.
> > Pardon my complete ignorance and "moronity" (if such a word exists) but
> does
> > not that violate referential integrity (since ID is the primary key) and
> is
> > it not considered a bad thing to update the primary key of an existing
> row?
> > I wonder if they meant to have just: BEFORE INSERT ON students rather
> than
> > BEFORE INSERT OR UPDATE ON students
> >
> > In all of my past/present jobs, once a row has a primary key associated
> with
> > it, that primary key "value" never gets changed.
> >
> > thanks...
> >
> >
>
> I would agree with you whole heartedly. NEVER update a primary key. Remove
> the words "OR UPDATE" to make it a much more reasonable trigger.
> >
>

Or ... if you expect to someday expand the trigger to also handle updates, surround your SELECT with:

if inserting then
  select...
end if;

-- 
[:%s/Karsten Farrell/Oracle DBA/g]