Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: I'm sorry, another dumb question about Triggers

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

From: Brian Peasland <dba_at_remove_spam.peasland.com>
Date: Thu, 7 Aug 2003 15:37:35 GMT
Message-ID: <3F32723F.F68CE9AD@remove_spam.peasland.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US