Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Trigger calling commit, where no commit is specified.

Re: Trigger calling commit, where no commit is specified.

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 2 Feb 2007 08:57:00 -0800
Message-ID: <1170435420.248852.106360@l53g2000cwa.googlegroups.com>


On Jan 31, 11:06 pm, "dean" <deanbrow..._at_yahoo.com> wrote:
> On Jan 31, 6:41 pm, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Jan 31, 4:12 pm, "dean" <deanbrow..._at_yahoo.com> wrote:
>
> > > The following procedure enables a trigger:
>
> > > CREATE OR REPLACE PROCEDURE
> > > "UP_JAN24"."ENABLE_TRIGGER_NO_COMMIT"
> > > (trigger_name in varchar2, program_name in varchar2,
> > > process_id in number)
> > > as
> > > begin
> > > declare
> > > err_msg varchar2(200);
> > > begin
> > > execute immediate 'ALTER TRIGGER '||trigger_name||' ENABLE';
> > > insert into status_log values (sysdate, user,
> > > program_name,process_id,'ETRIG','ETRIG Procedure enabled trigger: ' ||
> > > trigger_name,0);
> > > exception
> > > when others then
> > > err_msg := substr(sqlerrm, 1, 200);
> > > insert into status_log values (sysdate, user,
> > > program_name,process_id,'ETRIGERR','ETRIG Procedure Error: '||err_msg,
> > > 0);
> > > end;
> > > end;
>
> > > Now, I am finding that simply by calling this procedure, that any
> > > changes I have made through the same application are committed. Is
> > > this right? I assumed that any changes would be part of the main
> > > transaction. I have checked that the table 'status_log' has no
> > > triggers on itself causing the commit.
>
> > > 9.2i Windows, ADO interface.
>
> > This is expected behavior because as it states in the documentation
> > all DDL is preceded by an implicit commit and immediately followed by
> > an implicit commit.
>
> > If you do not wish to commit the application changes but still want to
> > run the DDL command look up anonymous transaction in the documentation
> > (or search the group archives).
>
> > HTH -- Mark D Powell --- Hide quoted text -
>
> > - Show quoted text -
>
> Ok that makes sense now. It can only commit changes from the app that
> does the DDL right?
>
> The business case is that we sometimes refresh all data from another
> source, wiping out most of the records in certain tables and replacing
> them with new data. Only user-edited records are kept. During this
> refresh period, we need to disable the triggers that log such user-
> changes.
>
> Thanks for the help
>
> Dean- Hide quoted text -
>
> - Show quoted text -

You do not have to use DDL for this. You could just use delete followed by insert or sqlldr to reload the data. However, I have some concerns that this is a workable design. It would depend on the details.

Some things for you to consider.

Concurrent access and the Oracle read consistency model in relation to your process if more than one session will access this data concurrently.

The Oracle workplace manager feature that will create and maintain multiple versions of a table. I am not that familiar with the product. I do know there are performance issues involved from people who have tried to use the feature.

Global Temporary Tables, GTT, defined once and every user gets a private copy. Data wiped on commit or retained until end of session. GTT visible only to creating session. Should be available from at least 8.1.7 and up. Again there can be performance issues. There was a bug related to truncate, etc... But GTT can be indexed.

If there is only one updating process and it is batch then permanent work tables hit with a truncate at the job start may be an option.

Make sure you understand the read consistency and locking model then look at the end requirement and then look at the source. Not map a path from the source to the end using the most appropriate features.

HTH -- Mark D Powell -- Received on Fri Feb 02 2007 - 10:57:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US