RE: Recreating a trigger in a 'live' system

Peter,  

My opinion:  

When the DDL is executed, Oracle will try and get an exclusive lock on the database table "t1". This prevents inserts from occurring. When your ddl concludes, the trigger is in place and and it will then fire. This is normal behavior for all DDL on a table (like creating an index or adding a column). You are changing the structure of a database object so Oracle needs to suspend activity on that object.  

Make sense?  

Tom

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of peter bell Sent: Wednesday, June 25, 2008 7:30 AM
To: oracle-l_at_freelists.org
Subject: Recreating a trigger in a 'live' system

Assuming I have the following :

create table t1 (x number);
create table t2 (y number);

create or replace trigger aifer_t1 after insert on t1 for each row begin
insert into t2 values (:new.x);
end;
/

And my application frequently executes :

insert into t1 values (:some_value);

If I recreate the trigger in a 'live' system, is Oracle able to somehow 'suspend' inserts on t1 whilst the trigger is being recreated (using breakable parse locks ?) and thereby ensure that all inserts have fired the trigger ?

Or is it possible that some inserts into t1 will not fire the trigger during the brief time it was being recreated ?

regards

peter b

Messenger's gone Mobile! Get it now!
<http://clk.atdmt.com/UKM/go/msnnkmgl0010000001ukm/direct/01/>

--
http://www.freelists.org/webpage/oracle-l