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

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 -- Received on Wed Jan 31 2007 - 17:41:12 CST