Re: Recreating a trigger in a 'live' system
Date: Wed, 25 Jun 2008 08:26:19 -0500
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:
-- http://www.freelists.org/webpage/oracle-l Received on Wed Jun 25 2008 - 08:26:19 CDTPeter,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: firstname.lastname@example.org [mailto:email@example.com] 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 or replace trigger aifer_t1 after insert on t1 for each row
create table t2 (y number);
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 ?
Messenger's gone Mobile! Get it now!