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: sybrandb <sybrandb_at_gmail.com>
Date: 1 Feb 2007 01:47:05 -0800
Message-ID: <1170323225.835625.53420@a34g2000cwb.googlegroups.com>


On Feb 1, 5:06 am, "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 -

Not a business case. Just a big MESS, using improper procedures, probably caused by (looking at your posting history) not performing the RTFM routine.

--
Sybrand Bakker
Senior Oracle DBA
Received on Thu Feb 01 2007 - 03:47:05 CST

Original text of this message

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