Re: Recreating a trigger in a 'live' system

From: Dan Norris <>
Date: Wed, 25 Jun 2008 08:26:19 -0500
Message-ID: <>


If you want to be sure you've quiesced activity, add "lock table t1 in
exclusive mode;" after running the 2nd create table statement. Once you
get the lock, create your trigger which will release the lock.


Mercadante, Thomas F (LABOR) wrote:
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?

From: [] 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
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 - 08:26:19 CDT

Original text of this message