what is autonomous trigger and autonomous transaction

Please find a related discussion here.

Hello ramya,

Your answer is not related to op question.

>>what is autonomous trigger and autonomous transaction


To make a transaction as autonomous transaction
In declarative block just use PRAGMA AUTONOMOUS TRANSACTION.

Features of AUTONOMOUS TRANSACTION are
- An autonomous transaction is an independent transaction initiated by another transaction.
- When an autonomous transaction is called the main transaction is temporarly suspended.
- An autonomous transaction allows you to subdevide a transaction into multiple commit and rollback transactions and each transaction is tracked seperately.
- It must contain at least one SQL statement.
- The autonomous transaction must commit or roll back before it returns control to the calling transaction.
- Autonomous transactions can be nested. There is no limit to the possible number of nesting levels.

Regards
Krishna

ok thanks krishna.

what i think is that trigger is any statement executed as a side effect of modification of data base

Hello mohan,

>>what i think is that trigger is any statement executed as a side effect of modification of data base

In Oracle 8i and above, Not only modifications of database, just about anything that happens in the database can have a trigger attached to it.

You can create triggers to be fired on any of the following:
DML statements (DELETE, INSERT, UPDATE)
DDL statements (CREATE, ALTER, DROP)
Database operations (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN)


Triggers are procedures that are stored in the database and implicitly run, or fired, when something happens.

Regards
Krishna

Hi friend, i got very good ppt on this the contents i m sharing with you so what is an autonomous transaction? recall the definition of an atomic transaction? basically all work done between two commits. A good job stream generally is composed of just one transaction (ok oversimplified but enough for our discussion). This means we start our job, it modifies a bunch of tables with inserts, updates, and deletes, and then it commits all its work or it does a rollback. Everything gets done or it all gets undone like nothing happened. That makes everything we did before the commit, an atomic unit of work. But if all work gets done or none gets done, then how for example do we record the error that occurred, which caused our job to roll back? we cannot just insert an error message into a table, because when the transaction gets undone by a rollback, so will our error message insert. One way i suppose is to do a little footwork with plsql error trapping. But what we could really use is the ability to suspend the current transaction, and start a new one. We then do what ever we need done in this second transaction, commit to finish it, and then resume the main transaction. The autonomous transaction provides us with this ability. Here is a simple example of plsql that shows how it works. Create table t1 (a number) / create table t2 (a number) / create or replace procedure p2 as pragma autonomous_transaction; begin insert into t2 values (2); commit; end; / show errors create or replace procedure p1 as begin insert into t1 values (1); p2; rollback; end; / show errors select * from t1 / select * from t2 / exec p1 select * from t1 / select * from t2 / if you look closely at this code, you should notice two things: 1) p2 contains the autonomous_transaction pragma thus designating it as an autonomous transaction. It contains a commit at the end to save its work. 2) p1 calls p2 and then does a rollback undoing everything it did. This sequence of procedures begs two questions: 1) if p2 does a commit after p1 inserts, will the insert of p1 be saved? 2) if p1 is issuing a rollback for its work, what does that mean to the insert done by p2? an autonomous transaction is independent of the calling transaction. This means that the work done by p2 cannot affect the work done by p1 and the work done by p1 cannot affect the work done by p2. With respect to transaction semantics, it is as if two different sessions were executing one of each of these pieces of code. Therefore, the commit in p2 can have no impact on the insert done by p1 and the rollback of p1 is not in conflict with the commit done in p2. If you understand this, then you should understand why a call to p1 results in a row in t2 and no rows in t1 as seen below. Sql> select * from t1 2 / no rows selected sql> select * from t2 2 / no rows selected sql> exec p1 pl/sql procedure successfully completed. Sql> select * from t1 2 / no rows selected sql> select * from t2 2 / a ---------- 2 if we reverse the commit and rollback with this variation of code: create or replace procedure p2 as pragma autonomous_transaction; begin insert into t2 values (2); rollback; end; / show errors create or replace procedure p1 as begin insert into t1 values (1); p2; commit; end; / show errors sql> delete from t1 2 / 1 row deleted. Sql> delete from t2 2 / 0 rows deleted. Sql> commit 2 / commit complete. Sql> exec p1 pl/sql procedure successfully completed. Sql> sql> select * from t1 2 / a ---------- 1 sql> select * from t2 2 / no rows selected then we can see there is a row in t1 and none in t2. You should run these sql snippets yourself so that you can see first hand the behavior of autonomous transactions. The key thing to take way from these examples is that there are two transactions involved; a main transaction, and a side transaction. Technically i suppose there is no such thing as a “side transaction”, but i choose these two terms for emphasis because i hope to guide you into thinking about autonomous transactions as side events, and not as a mechanism for main processing logic; (more on this later). So, what do the two transactions see from each other? this is a fundamental issue to grasp. If you know the answer to the above question then you have a reasonable handle on how autonomous transactions interact with other transactions. Again, let us look at a code snippet for the details. Delete from t1 / delete from t2 / commit / create or replace procedure p2 as pragma autonomous_transaction; count_v number := 0; begin select count(*) into count_v from t1; if count_v = 0 then raise_application_error(-20999,'no rows in table t1'); else raise_application_error(-20999,'t1 has rows'); end if; commit; end; / show errors create or replace procedure p1 as begin insert into t1 values (1); p2; commit; end; / show errors exec p1 continuing with our original tables (t1 and t2), the code snippet above will do four things of interest: 1) p1 will insert a row into table t1 2) p1 will call p2, an autonomous transaction 3) the autonomous transaction will count the rows in table t1 4) the autonomous transaction will raise an exception based on how many rows it counts, thus showing us how it interacts with its calling transaction. Executing p1 we get the following: sql> exec p1 begin p1; end; * error at line 1: ora-20999: no rows in table t1 ora-06512: at "kevin.p2", line 7 ora-06512: at "kevin.p1", line 5 ora-06512: at line 1 the error thrown tells us that the select statment in the autonomous transaction counted zero rows in table t1. How can that be? we inserted a row into t1 before we started the autonomous transaction, how can the autonomous transaction not see it? the answer is simple but requires a basic understanding of oracle transaction semantics. An autonomous transaction is as was said before, a separate transaction. We know that oracle supports several variations of concurrency and transaction control, also called isolation level, and we know that the default mode for oracle which is almost universally the setting of all oracle instances, is statement level consistency and it works like this: each sql statement can see the committed work in the database as of the time it starts, plus all uncommitted changes made by its transaction up to the time it starts. Understanding this, it is obvious why the autonomous transaction counted zero rows; because being a separate transaction from the calling transaction, its sql statements cannot see any uncommitted changes made by the calling transaction. This is a little surprise developers confront when first learning how to use autonomous transactions. How about the other way around, can the calling transaction’s sql see the committed changes of the autonomous transaction? understanding oracle transaction semantics, we would expect yes. I leave the proof of this to you guys to do. Still even after you have shown that the calling transaction can see the changes made by the called autonomous transaction, you have to remember, that you were doing your work using the default isolation level that oracle runs under. There is some discussion on the internet about how changing the isolation level you are using can change this result such that the calling transaction won’t see changes made by its called autonomous transaction. A discussion of isolation level is way more that i want to get into. It is moot by my way of thinking anyway, as almost everybody uses the default isolation level. But hey, i thought i’d mention it so if you were game you could search for it yourself. If you find some interesting pages on the net, post them here so we can all read them. You can check the following on metalink for a hint to this. Note:65961.1 you may be grasping at this point, that although autonomous transactions look very useful on the surface (and they can be), the details of their behavior can lead the unsuspecting into trouble. Getting into trouble autonomous transactions and avoiding mutating tables (bad idea) knowing that an autonomous transaction cannot see the uncommitted work of its calling transaction, what happens when a trigger calls an autonomous transaction? better yet, what happens when the entire trigger is itself composed of an autonomous transaction? consider the following code snippet: create table t3 (a number) / create or replace trigger air_t3 after insert on t3 for each row declare count_v number := 0; begin select count(*) into count_v from t3; raise_application_error(-20999,'t3 rowcount = '||count_v); end; / show errors insert into t3 values (1) / after reviewing this code, some of you may recognize the inevitable mutating table error we are going to get. Sql> insert into t3 values (1) 2 / insert into t3 values (1) * error at line 1: ora-04091: table kevin.t3 is mutating, trigger/function may not see it ora-06512: at "kevin.air_t3", line 4 ora-04088: error during execution of trigger 'kevin.air_t3' it is not legal to select off a table we just inserted into. This is one of the details of working with triggers on tables. Without getting into the details of this error, suffice it to say that oracle is doing its job by protecting us from updating data in an ambiguous situation. But suppose we make the trigger an autonomous transaction, then what? assuming the ambiguity of the trigger is based on not really knowing the state of the table to show as is the case for a multi-row insert, then making the trigger an autonomous transaction should resolve the ambiguity because it defines what we must see with respect to uncommitted changes. Create or replace trigger air_t3 after insert on t3 for each row declare pragma autonomous_transaction; count_v number := 0; begin select count(*) into count_v from t3; raise_application_error(-20999,'t3 rowcount = '||count_v); end; / show errors insert into t3 values (1) / sql> insert into t3 values (1) 2 / insert into t3 values (1) * error at line 1: ora-20999: t3 rowcount = 0 ora-06512: at "kevin.air_t3", line 6 ora-04088: error during execution of trigger 'kevin.air_t3' no more mutating error. Neat you say. How easy is that, to avoid mutating and constraining table errors? true, but did you examine our user defined error message, raised by the code snippet? it says there are no rows in the table the trigger is firing on. How can that be? this is an after insert of row trigger; it only fires when a new row is added to the table, so there must be a row in the table. But as we have already seen, an autonomous transaction cannot see uncommitted changes of the transaction that called it. How intuitive it is that a trigger fired for the insert of a row cannot see the row that fired it. Indeed, a little forethought reveals the situation is much deeper. Since the autonomous transaction cannot see uncommitted changes from its calling transaction, all work done by the calling transaction is not viewable to the trigger code, not just the row inserted in to the table. If the autonomous transaction should happen to need to see the effects of modified data from the main transaction, it can't. There are at least two dangers here. 1) there are sequences of events (inserts, updates, deletes) that can transpire which although you managed to get by the mutating table error, will only lead you to another error later on in the code. 2) worse, there are sequences of events (inserts, updates, deletes) which won’t generate an error later; your transaction will succeed, but because of the nuances of autonomous transactions, you may not be getting the answer you think you are getting, thus resulting in possible data corruption by your application. Hmm.. I wonder what all this means to trigger chains several layers deep. Triggers with autonomous transactions are clearly not for the faint of heart. Generally speaking, they are considered bad practice and except for the most knowledgeable of developers, this combination of features used together will lead to unexpected problems. Here are some interesting links that discusses the issue in more detail. oracle.com http://asktom.oracle.com/pls/asktom/...:2212445691154 getting into trouble how to deadlock you own session deadlock is a situation where transaction a locks resource r1 and transaction b locks resource r2, then transaction a tries to lock r2 and transaction b tries to lock r1. Each transaction is waiting on the other for a resource that will never be released because neither transaction can finish. Catch 22? what does this mean to autonomous transactions? i am sure you have guessed it. Locking in oracle is managed at a transaction level, not a session level. Autonomous transactions are nothing special in this regard. They deadlock the same way as normal transactions. Thus a single session can deadlock itself if it is not careful. Here is a simple code snippet to illustrate. Notice because we are using autonomous transactions which means the primary transaction is in suspense and cannot finish till the autonomous transaction completes, we only need one resource to lock on, not the typical circular chain of locked resources (neat a chain of length one). Create table t4 (a number) / insert into t4 values (1) / commit / create or replace procedure p2 as pragma autonomous_transaction; begin update t4 set a = 2; commit; end; / show errors create or replace procedure p1 as begin update t4 set a = 3; p2; commit; end; / show errors exec p1 sql> exec p1 begin p1; end; * error at line 1: ora-00060: deadlock detected while waiting for resource ora-06512: at "kevin.p2", line 5 ora-06512: at "kevin.p1", line 5 ora-06512: at line 1 wow, that was even easier than i thought. I didn’t have to wait long at all for the error to come back. You can check this issue via this metalink document if you have access to metalink. Note:224305.1 or, you could just try reading the manual. It has an entire section devoted to transaction management, including a discussion on deadlock in autonomous transactions. 4 transaction management valid if possibly dubious ways to use autonomous transactions i will admit to having an affinity for code that abuses the database. Some of the best code i have seen is people writing stuff that really stretches the intent of a feature. This natural developer creativity is not lost on autonomous transactions. Here is a sampling some special scenarios: updating data from a query we all know you can write a plsql function and if it has the right purity level, you can call this function from sql. But, these function may not write to the database. Why? again it has to do with potential ambiguity of data that might be seen during the query. But as we saw before, the autonomous transaction removes this ambiguity. Does this mean that if we build our function that writes to the databvase, as an autonomous transaction, we can call this function that writes to the database from a query? yes it does. Here is a simple code snippet to illustrate: drop table t1 / drop table t2 / create table t1 (a number) / insert into t1 values (1) / commit / create table t2 (a number) / create or replace function f1 return number as pragma autonomous_transaction; begin insert into t2 values (2); commit; return (1); end; / show errors select t2.a from t2 / select t1.a,f1 from t1 / select t2.a from t2 / sql> select t2.a from t2 2 / no rows selected sql> select t1.a,f1 from t1 2 / a f1 ---------- ---------- 1 1 sql> select t2.a from t2 2 / a ---------- 2 yep, using an autonomous transaction, we just queried a row off table t1 and the select statement called a function which unknown to the query, inserted a row into t2. One proposed reason used for this is the need to record the fact that some specific data was queried. Given a controlled application, one could insert a row into an audit table of some kind whenever a specific column was selected, or a specific row was selected. It might also be possible to stop people from querying as in some obscure need to make sure people can select “such and such” only once per day. But like anything, there are nuances to consider. As a general rule it is i suppose true, that a function in a select list is executed once per row returned, but not really. There are several situations in which we don’t know how many times oracle will actually call a function embedded in a query. In fact it really does not matter how many times a function is called from a query. As long as the query gives the correct answer, oracle should be free to optimize query execution as it sees fit. But what if we are trying to change data in a hidden fashion along the way? if the function is not execute the expected number of times, our query may give us the correct answer, but our hidden update may well be wrong. Here are two examples (i am sure there are more): 1) deterministic functions. These are functions that always return the same answer for the same inputs. Oracle has an optimizing feature for deterministic functions that allows it to reduce the number of times it has to call them, to the point where it may not even call the function at all. Yes, you have to declare the function as deterministic so you have some control over it. But you sure need to know what your transactions are going to look like. 2) functions in a where clause get called differently depending upon query plans. Tom kyte has a fine example of how he can make the same table and same data and same query, execute a function call a different number of times based on something as simple as adding an index, or changing statistics. Actually, anything that could cause a query plan change at runtime could have this affect. http://asktom.oracle.com/pls/asktom/...:1547006324238 indeed, there is even a suspect issue of the read consistency of using functions from sql in the first place. Here is an interesting post form tom kyte again (gee maybe you should check his site since i keep referencing him) on this issue: http://asktom.oracle.com/pls/asktom/...82900346230020 executing ddl from a trigger i basically said autonomous transactions inside triggers was usually a bad idea. But consider this problem: “i want to create a new user, whenever a row is inserted into table x”. Using an autonomous transaction, we can execute ddl like “create user” if we so desire. This certainly has the basic functionality we want. But there is of course one obvious flaw: what happens when the main transaction fails, after the autonomous transaction has created the user? answer: you have a user id in your database, without the associated referencing data used to create it because the row insert that holds this information was undone by a rollback. If the user is inserted a second time (which he can be because there is no row in the table to stop it), you get an error in the autonomous transaction when you try to create the user. You will need some fancy error handling and transaction management to deal with this and i bet it still won’t be right. It looks like the easy solution is starting to get a lot more complicated. In the old days i think this need was satisfied by using dbms_job to submit a job request that would create the user. If i recall correctly, the job submit won’t go through unless the transaction completes successfully. This is better as you won’t get orphaned users in your database, although i suppose you have to wait for the job to finish before actually seeing and making use of the user so there is some delay which might not fit in with your plans. All in all it is an ugly problem to start with. I won’t bother with a code snippet for this one. So, what are autonomous transactions for? maybe it’s a bit dull, but in a phrase: low volume job auditing. Oracle originally exposed autonomous transactions to developers as a mechanism for committing information on the side whilst not affecting the main event. They wanted to give us a way to record what happened in a job regardless of success or failure of the job itself, and without changing the transaction semantics of the main transaction. To this end, it is actually a good idea to use autonomous transactions to create a job monitoring package for your self to enable the monitoring of plsql jobs. You would capture information like: • job start and end • job parameters • job step start and end • job step parameters • job and step error conditions • and whatever else you want. Steve feuerstein of quest software fame has one. Tom kyte of asktomhome fame has one. I have one (not that i would ever put my self in the same league as these two gentlemen). You should have one. Job logging that uses autonomous transactions allows you to capture the log data for sure. My comment of low volume is intended to indicate that you don’t really want to spawn autonomous transactions for example inside a plsql loop for every row, because this can get expensive. Additionally, there are auditing tools built into oracle these days and i for one am loath to write my own code to duplicate a feature that oracle already provides so there may be an existing auditing solution you can adapt or integrate into your overall auditing plans.