RE: LONG fields within triggers.

From: Jack C. Applewhite <japplewhite_at_inetprofit.com>
Date: Wed, 21 Mar 2001 09:35:29 -0800
Message-ID: <F001.002D3388.20010321075645@fatcity.com>

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
japplewhite_at_inetprofit.com

-----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: japplewhite_at_inetprofit.com

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: ListGuru_at_fatcity.com (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). Received on Wed Mar 21 2001 - 11:35:29 CST