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

From: Alan Mills <Alan.Mills_at_xservices.pants.fujitsu.com>
Date: Thu, 7 Aug 2003 15:52:37 +0100
Message-ID: <bgtp6g$2t7j$1@news.icl.se>

"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. > Received on Thu Aug 07 2003 - 09:52:37 CDT