Re: Maintaining Transaction logs on tables in Oracle 8.0.4....

Thanks for the reply Thomas,

 I'm sorry to use more of your time, I was following you upto this point :

>SQL> create or replace trigger employ_ai
> 2 after insert on employ
> 3 declare
> 4 l_lob clob;
> 5 l_resume VARCHAR2(200);
> 6 l_offset INTEGER:=1;
> 7 l_amount INTEGER:=200;
> 8 begin
> 9 for i in 1 .. employ_pkg.new_rowids.count loop
> ... insert into another table select whatever from employ
> where rowid = employ_pkg.new_rowids(i); .....
> 15 end loop;
> 16 end;
> 17 /

I can see you're looping round all the records inserted - intending to copy the necessary fields from employ into a transaction table (employ_trans say).

I'm very new to Oracle so please humor the following questions :

• The l_lob, l_resume, l_offset etc variables you declare don't seem to get used?
• This technique works (even though it is inside a trigger) because its not a "for each row" trigger, Yes?
• Lets imagine I had 50 of these data tables which needed this technique with say 100 users banging on them, how ineffecient is this technique? Would YOU ever use it or would you redesign the schema to avoid it if possible?

Thank you again for your time - I really appreciate your help.

• Ian Sparks.

Thomas Kyte wrote...
>so, you cannot do what you are attempting, however, a workaround for you
might
>be to defer reading the LOB until an AFTER trigger (not for each row):
>
>
>SQL> CREATE TABLE employ(name VARCHAR2(80),resume CLOB);
>Table created.
>
>SQL> create or replace package employ_pkg
> 2 as
> 3 type rowidArray is table of rowid index by binary_integer;
> 4
> 4 new_rowids rowidArray;
> 5 empty rowidArray;
> 6 end;
> 7 /
>Package created.
>
>SQL> create or replace trigger employ_bi
> 2 before insert on employ
> 3 begin
> 4 employ_pkg.new_rowids := employ_pkg.empty;
> 5 end;
> 6 /
>Trigger created.
>
>SQL> create or replace trigger employ_aifer
> 2 after insert on employ
> 3 for each row
> 4 begin
> 5 employ_pkg.new_rowids( employ_pkg.new_rowids.count+1 ) :=
>:new.rowid;
> 6 end;
> 7 /
>Trigger created.
>
>SQL> create or replace trigger employ_ai
> 2 after insert on employ
> 3 declare
> 4 l_lob clob;
> 5 l_resume VARCHAR2(200);
> 6 l_offset INTEGER:=1;
> 7 l_amount INTEGER:=200;
> 8 begin
> 9 for i in 1 .. employ_pkg.new_rowids.count loop
> ... insert into another table select whatever from employ
> where rowid = employ_pkg.new_rowids(i); .....
> 15 end loop;
> 16 end;
> 17 /
>Trigger created.
Received on Fri Feb 12 1999 - 19:39:13 CET