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

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

Re: Row-Trigger question (Oracle 8)

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Sun, 14 Nov 2004 17:35:28 +0100
Message-ID: <cn5d5v$llh$1@news5.zwoll1.ov.home.nl>


Michael Kreitmann wrote:

> Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0411110801.1ba19040_at_posting.google.com>...
>

>>m.kreitmann_at_gmx.net (Michael Kreitmann) wrote in message news:<987ebcdd.0411110035.2ee89cc6_at_posting.google.com>...
>>
>>>Hello,
>>>
>>>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!
>>>
>>>Regards
>>>Michael
>>
>>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.

-- 

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

Original text of this message

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