RE: Recreating a trigger in a 'live' system

From: Mercadante, Thomas F (LABOR) <>
Date: Wed, 25 Jun 2008 07:55:58 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF01E26AAB@EXCNYSM0A1AJ.nysemail.nyenet>


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?  


[] On Behalf Of peter bell Sent: Wednesday, June 25, 2008 7:30 AM
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);

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 ?


peter b

Messenger's gone Mobile! Get it now!

Received on Wed Jun 25 2008 - 06:55:58 CDT

Original text of this message