Avatar of AidenA
AidenA
 asked on

Oracle error: ORA-01086: savepoint START_TRANSACTION never established

Hi, I'm getting the above error when I run a transaction. I've traced the error to the below queries. So, it seems that if I raise an error before the following queries run, then the rollback succeeds without any problem. However, if I raise an error after the first 'ALTER TRIGGER...' statement then I get the above error (ORA-01086) and the rollback cannot complete. Obviously the alter trigger (or execute immediate?) statement is preventing Oracle from being able to do a rollback.

So, my question is, what's going on here exactly, and how can I fix it?

Thanks, Aiden


EXECUTE IMMEDIATE 'ALTER TRIGGER ctc_table_trig DISABLE';

DELETE FROM CTC_Table;

EXECUTE IMMEDIATE 'ALTER TRIGGER ctc_table_trig ENABLE';

Open in new window

Oracle DatabaseASP.NETSQL

Avatar of undefined
Last Comment
AidenA

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sean Stuber

rather than disabling the triggers, put a condition in them so they don't actually do anything.

that way you don't need to execute ddl at all


something like

BEGIN
IF some_package.variable = true then
   --- your trigger code here
END IF;
END;

then, in your script, set the variable to FALSE before the delete, then reset it to true when your transaction is complete.
Sean Stuber

and lastly,  if you're trying to circumvent your triggers, maybe there shouldn't be triggers on the table to begin with.

or, if they really should be there, maybe trying to get around them isn't a good idea
AidenA

ASKER
ok thanks. I just disable the tigger before running my transaction then and enable it afterwards.

I could change it as you suggest so that I don't have to do that, and probably should, but I don't have time to start messing around with the queries so I guess I'll just do it this way out of convenience!  :-/

thanks!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes