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

Home -> Community -> Usenet -> c.d.o.server -> Re: Help with Triggers

Re: Help with Triggers

From: Al Reid <areidjr_at_reidDASHhome.com>
Date: Wed, 23 Aug 2006 07:28:28 -0400
Message-ID: <12eof6btcein249@corp.supernews.com>


FM wrote:
> G Quesnel wrote:
>> You seemed to be trying to update a row in the middle of updating
>> that row ... it looks ugly.
>> Regardless of the validity of what you are trying to achieve,
>> you should remove the update statement from withing your trigger (and
>> the commit).
>>
>> How about replacing your select statement with something like ...
>> Select CCAPADM.SEQ_PRINT_INDEX.NEXTVAL INTO :NEW.PRINT_INDEX from
>> dual;
>>
>> hth
>
> Also, the 10gR2 documentation indicates that :
>
> Restrictions on AFTER Triggers AFTER triggers are subject to the
> following restrictions:
> You cannot write either the :OLD or the :NEW value.
>
> So you should change the trigger to a BEFORE trigger.
>
> FM

Thanks G Quesnel and FM for the pointers. I ended up using both of your suggestions (select into :new and BEFORE UPDATE). The trigger is now declared as:

CREATE OR REPLACE TRIGGER "CCAPADM".TRG_PRINT_INDEX BEFORE UPDATE OF "PRINTED"
ON "CCAPADM"."JOBLIST"
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN    SELECT CCAPADM.SEQ_PRINT_INDEX.NEXTVAL INTO :NEW.PRINT_INDEX    FROM DUAL;    COMMIT; END; and is working properly.

Thanks again.

-- 
Al Reid
Received on Wed Aug 23 2006 - 06:28:28 CDT

Original text of this message

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