Al Reid wrote:
> 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.
Not so sure about that. Why do you use a COMMIT?
Wouldn't that commit any updates prior to the statement which directly
or indirectly fired the trigger? Surely a rather dubious side-effect.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/
Received on Wed Aug 23 2006 - 07:03:54 CDT
