Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: I'm sorry, another dumb question about Triggers
In this case, I'd probably remove the "OR UPDATE" clause. I agree with
you, that this may introduce unintended side affects. I've seen
perfectly acceptable reasons to change the primary key, but this may not
be one of them.
HTH,
Brian
tk wrote:
>
> 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...
-- =================================================================== Brian Peasland dba_at_remove_spam.peasland.com Remove the "remove_spam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three"Received on Thu Aug 07 2003 - 10:37:35 CDT