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

dean 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. You are performing DDL ... a bad practice in any application and DDL begins with a COMMIT ... does its work ... and ends with a second COMMIT.

So the question that needs to be asked is why a design that enables and disables a trigger? What is the business case?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org