Thanks for the prompt tip Jack, it works! :) Are there any caveats?
How about concurrency? As it is now, I insert into the PL/SQL table
using
v_Index := NVL(UpdatePackage.v_IDs.LAST, -1);
v_Index := v_Index+1;
UpdatePackage.v_IDs(v_Index) := :new.id;
within the row trigger. In the statement trigger I use
v_Index := UpdatePackage.v_IDs.FIRST;
WHILE v_Index IS NOT NULL LOOP
BEGIN
OPEN selectCursor; --Selects LONG field from the updated table
FETCH selectCursor INTO tempContent; --tempContent is a LONG var
CLOSE selectCursor;
IF tempContent IS NOT NULL THEN --Insert into target table
INSERT INTO table_b (id, field)
VALUES (UpdatePackage.v_IDs(v_Index),'<data>'||tempContent||'</data>');
tempContent := NULL;
END IF;
END;
UpdatePackage.v_IDs.DELETE(v_Index); --Delete from the PL/SQL table
v_Index := UpdatePackage.v_IDs.NEXT(v_Index);
END LOOP;
Any bets on the thread safety on this procedure? What if eg. 4 users
update the PL/SQL table in the row triggers, and all 4 of the subsequent
statement triggers read the same variable from the PL/SQL table. Is
this an issue at all? I'm not at all experienced in the more complex
ways of PL/SQL.
Thanks a ton.
Morten
On Wed, 21 Mar 2001, Jack C. Applewhite wrote:
> Morten,
>
> One solution to this classic "Mutating Table" problem is to
> capture the ID (better yet, RowID) of each inserted row in a
> public PL/SQL table in an After Row Trigger, then loop
> through the PL/SQL table in an After Statement Trigger and
> do your Selects and Inserts. Works great.
>
> Jack
>
> --------------------------------
> Jack C. Applewhite
> Database Administrator/Developer
> OCP Oracle8 DBA
> iNetProfit, Inc.
> Austin, Texas
> www.iNetProfit.com
> [EMAIL PROTECTED]
>
>
> -----Original Message-----
> Morten
> Primdahl
> Sent: Wednesday, March 21, 2001 9:16 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Hi. I'm indexing the DB with the use of triggers.
> In one table, I have a LONG field, I need to read
> the :new value of this field in the trigger, modify
> it slightly, and insert the value into my context
> indexed table.
>
> I'm not allowed to query the table in the trigger
> because of mutation, so I cannot do stuff like
>
> CREATE TRIGGER ... ON test_table
> FOR EACH ROW
> DECLARE
> tempContent LONG;
>
> CURSOR selectCursor IS
> SELECT long_field FROM test_table WHERE id = :new.id;
>
> BEGIN
> OPEN selectCursor;
> FETCH selectCursor INTO tempContent;
> CLOSE selectCursor;
>
> And I cannot reference the LONG field directly, eg.
> :new.long_field,
> what can be done? I need to do the equivalent of
> BEGIN
> INSERT INTO other_table VALUES
> ('PREFIX'||:new.long_field||'POSTFIX');
> END;
> In the trigger. Any help greatly appreciated.
>
> Thanks
>
> Morten
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jack C. Applewhite
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Morten Primdahl
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
