Re: nested trans. Oracle vrs MS SQL 6.5

Hi Gopal,

>
>If the trigger fails, who will the procedure know about this
>so that it can rollback too ?.

One way would be to have your trigger use Oracle's "raise_application_error" procedure call to communicate the error back to your application. For example:

create trigger your_trigger
  before insert
  on your_table
declare
  your_variable number;
begin
  --You can explicitly raise an error based on some   --arbitrary condition
  if new.some_column = 1 then
    raise_application_error (-20088,"some_column=1");   end if;

--Note that "exception" is a keyword and indicates --the beginning of an exception handler. exception
when others
  raise_application_error (-20099,"An Error Occurred"); end;

Your calling procedure will get the error and see the error code in SQLCODE and the message in SQLERRM.

This is documented in the SQL Language Reference Manual--look under "Create trigger". You will also want to look at the PL/SQL User's Guide. See ch 5 where it talks about exception handling.

>I was hoping that Oracle has the equivalent the _at__at_trancount variable
>in MS SQL 6.5 which tells us about nesting of transactions.

Oracle does not "nest" transactions, but it does support savepoints. Savepoints let you commit part of a transaction. For example:

insert into table_a...

savepoint first_part;

update table_b...
update table_c....

savepoint second_part;

select from table_d...

savepoint third_part;

--Now you have the option of doing a rollback to --any of the named savepoints.
If you like then

        rollback to savepoint second_part;
elsif whatever

        rollback to savepoint first_part;
else

         commit;
end if;

>I am also looking for books which deal programming issues such as these.
If you don't have the Oracle manuals, you can find "Oracle Press" books in most any bookstore and they appear to be good manual substitutes.

Hope this helps,

Jonathan

Jonathan Gennick
gennick_at_worldnet.att.net
"Brighten the Corner Where you Are" Received on Tue Apr 01 1997 - 00:00:00 CEST