Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Row-Trigger question (Oracle 8)

Re: Row-Trigger question (Oracle 8)

From: Frank van Bortel <>
Date: Sun, 14 Nov 2004 17:35:28 +0100
Message-ID: <cn5d5v$llh$>

Michael Kreitmann wrote:

> (Mark D Powell) wrote in message news:<>...

>> (Michael Kreitmann) wrote in message news:<>...
>>>I've a question about row triggers under oracle.
>>>If I define the trigger "FOR EACH ROW", the trigger will be fired n-times
>>>if the firing statement will hit n records. Is there any unique ID or hash-code
>>>or anything else, that is equal for all of these n executions of this trigger
>>>(and only for these n executions ? - the next statement firing the trigger 
>>>must produce a new (unique) ID ...)
>>>Many thanks for your advice!
>>What is it you are trying to do?
>>What version of Oracle will you be using?
>>Will the rows you wish to associate be submitted as one transaction or many?
>>HTH -- Mark D Powell --

> Hi!
> I'm using Oracle 8.1.7 und 9.2.0
> What I want to do:
> Inside my trigger I want to write one row for each update statement
> into table1 and n rows for each updated record into table2. This to
> table must be joined over an unique key, so that I want to use one
> trigger for writing both tables.
> Any yes, this will be (of course) one transaction.
> Example:
> update foo set field1 = 'xyz'
> should cause the following:
> table1:
> 001 (ID) 2004-11-12 12:00:00 UPDATE foo USER HOST
> table2:
> 001 (same ID as above) key 123 OLDVALUE NEWVALUE
> 001 (same ID as above) key 456 OLDVALUE NEWVALUE
> 001 (same ID as above) key 789 OLDVALUE NEWVALUE
> How everybody understands what I'm trying to do ;)
> Many thanks for your advise!
> Michael

Can be done.

How odd it may sound, look up
mutating tables and code to work around that.

Basically, declare an array (PL/SQL table) in a package. Call packaged procedures from your trigger, and fill the array with changed (:old / :new ) values, plus the id, as you go along. Then, in an after-statement trigger, call the packaged procedure that processes the PL/SQL table to fill table 2.


Frank van Bortel
Received on Sun Nov 14 2004 - 10:35:28 CST

Original text of this message