I'm sorry, another dumb question about Triggers

From: tk <theronk_at_charter.net>
Date: Thu, 7 Aug 2003 08:59:23 -0600
Message-ID: <vj4mfs6sii9u3c@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... Received on Thu Aug 07 2003 - 09:59:23 CDT