Re: Trigger and LONG-datatype

brrr...
  Jürgen, You are in a mess. first of all, :new and :old can NOT be used with long and long raw columns. I reproduced Your case in a testscheme of mine as follows:

create table post

(post_nr number,
 post_typ varchar2(2),
 post_text long);

create table p_post
(post_nr number,
 post_text long);

now my workaround, assuming post_nr is unique:

create or replace trigger TR_POST_AI_PPOST after insert on post for each row
begin
if :new.post_typ = 'RE' then
  insert into p_post
  (post_nr, post_text)
  select post_nr, post_text from post
  where post_nr = :new.post_nr;
end if;
end;
/

unfortunately this does not work. it rises

ORA-04091: table TEST.POST is mutating, trigger/function may not see it
ORA-06512: at "TEST.TR_POST_AI_PPOST", line 3
ORA-04088: error during execution of trigger 'TEST.TR_POST_AI_PPOST'

hm. messages and codes manual says I am 'in the middle' of the insert statement
though the trigger is 'after insert', but anyway. I tried to use a cursor loop with the same (expected) result, ORA-04091.

according to messages and codes manual the problem arises because I want to query the table the trigger is hooked on. so I need some 3rd table.

create table post_action
(post_nr number);

initialize:

insert into post_action values(1);

hook the trigger on post_action and remove condition:

drop trigger TR_POST_AI_PPOST;

create or replace trigger TR_POST_AI_PPOST after update on post_action for each row begin
  insert into p_post
  (post_nr, post_text)
  select post_nr, post_text from post
  where post_nr = :new.post_nr;
end;
/

and to fire trigger cascade:

create or replace trigger POST_HISTORY
after insert on post for each row
begin
if :new.post_typ = 'RE' then
  update post_action set post_nr = :new.post_nr; end if;
end;
/

also does not work: insert on post now gives

ORA-04091: table TEST.POST is mutating, trigger/function may not see it
ORA-06512: at "TEST.TR_POST_AI_PPOST", line 2
ORA-04088: error during execution of trigger 'TEST.TR_POST_AI_PPOST'
ORA-06512: at "TEST.POST_HISTORY", line 3
ORA-04088: error during execution of trigger 'TEST.POST_HISTORY'

trigger TR_POST_AL_POST on table post_action now complains about mutating table post.

anyhow, oracle replication on oracle 7.x managed this case, cause it built PL/SQL triggers
on the application tables that wrote away inserts, updates and deletes to replications tables.

has anybody some idea??

cheers
--marcus

Jürgen Kreppner schrieb in Nachricht <79pg22$jh4$1_at_news.odn.de>...
>Hi,
>
>i have a Trigger which minutes a table with a LONG-field.
>The Trigger looks like this:
>
>create or replace trigger gepard.TR_POST_AI_PPOST
>after insert on gepard.post for each row
>begin
> if :new.post_typ = 'RE' then
> insert into prot.p_post
> (p_post_post_nr, p_post_gpar_nr, p_post_firm_nr, p_post_fist_nr,
> p_post_ntea_nr, p_post_post_text, p_post_post_kennzeichen,
>p_post_post_version)
> values
> (:new.post_nr, :new.gpar_nr, :new.firm_nr, :new.fist_nr,
> :new.ntea_nr, :new.post_text, :new.post_kennzeichen, :new.post_version);
> end if;
>end;
>/
>
>The Trigger minutes all fields exactly, except the LONG-field
>(:new.post_text). There are no errors or warnings. In the table p_post the
>field is NULL!
>
>Why?
>
>
Received on Wed Feb 10 1999 - 06:53:51 CST