Hi All,
The important thing about triggers is that, you can't call Transaction Control Statements(commit/rollback) inside a trigger. But what if a trigger is fired and then it makes an entry into another table (like user, sysdate and more). So when will this insert gets committed ?
Or can I call a procedure after this insert statement inside a trigger which has a COMMIT ? will this commit the insert statement ?
Thanks
Aj
9 6900
try to use pragma autonomous transaction in trigger..
You can also SET AUTOCOMMIT ON but this is dangerous
@debasisdas
Hi, Thanks for the reply. I went through pragma autonomous and I believe that is the only way we can do a commit through an internal process.
Regards,
Aj
As also suggested in the above post, that SET AUTOCOMMIT ON will also do the job. Check below: -
-
SQL> SET AUTOCOMMIT ON
-
SQL> ed
-
Wrote file afiedt.buf
-
-
1 create or replace trigger t after insert on emp for each row
-
2 begin
-
3 if inserting then
-
4 insert into emp1 values(:new.empid,:new.empname,:new.salary,:new.mgrid,:new.deptno);
-
5 end if;
-
6* end;
-
SQL> /
-
-
Trigger created.
-
-
SQL> insert into emp values(20,'B',1000,2,30,SYSDATE);
-
-
1 row created.
-
-
Commit complete.
-
SQL> select * from emp1;
-
-
EMPID
-
----------
-
EMPNAME
-
--------------------------------------------------------------------------------
-
SALARY MGRID DEPTNO
-
---------- ---------- ----------
-
20
-
B
-
1000 2 30
-
-
-
SQL> rollback;
-
-
Rollback complete.
-
-
SQL> select * from emp1;
-
-
EMPID
-
----------
-
EMPNAME
-
--------------------------------------------------------------------------------
-
SALARY MGRID DEPTNO
-
---------- ---------- ----------
-
20
-
B
-
1000 2 30
-
-
-
SQL>
-
Hi amitpatel66,
That was a great example. Thanks.
I didn't quite understand one aspect of this code you posted. After the auto commit, the SELECT * FROM EMPL; returned one row. But when you rolled back, why is that the same select still returns the same row. Shouldn't it be deleted and rolled back to its previous state. What I can assume from this is because we haven't created a save point so nothing rolled back. If not, kindly explain.
Regards,
Aj
@eeriehunk
To understand all that you need to understand what a AUTONOMOUS TRANSACTION is. and its uses.
@debasisdas
Great ! I will get on to research more on Autonomous Transaction. Thanks,
Aj
Once you SET AUTOCOMMIT ON, the INSERT operation performed in the TRIGGER will be COMMITED automatically. You can see in my example that when I am interting in to EMP table, the TRIGGER is Fired and it will INSERT the same Record in to EMP1 Table. And after these transactions, a COMMIT will get Executed Automatically. So all your Transactions are COMMITED. Thus when I queried from EMP1 first time it displayed one record, and even after ROLLBACK it displayed the same record that was inserted by a TRIGGER just to show you that it is AUTO COMMITED.
Saii 145
Recognized Expert New Member
I vote for autonomous transaction but i think committing through triggers should be limited to auditing purposes only.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: DP |
last post by:
Is there any command exists as "commit transaction" ?
begin transaction
< DML1 stmts >
begin transaction
< DML2 stmts >
rollback transaction
commit transaction
which set of DML will be...
|
by: anders_tung |
last post by:
Hi,
I have a procedure which will call 3 functions.
First function will update a record.
Second function will delete a record.
Third function will insert a record.
Each function will return...
|
by: Markus Breuer |
last post by:
I have a question about oracle commit and transactions. Following scenario:
Process A performs a single sql-INSERT into a table and commits the
transaction. Then he informs process B (ipc) to...
|
by: janet |
last post by:
HI,
I had a question on auto-commit in DB2 EEE V8 on AIX V5.
Here is my example..
there are two script A.sh , B.SQL
A.sh is following:
|
by: Re_endian_miss |
last post by:
I know even less about databases than I thought... I always thought
that the idea behind a transaction was that the actual table in
question does NOT get updated until a commit is issued (either...
| |
by: vijai kumar via DBMonster.com |
last post by:
Hi,
I am using UDB v 8.1 on win2000.
I have setup the ODBC datasources.
My application is connecting to the database and performing DML activities.
But I am getting the following error
...
|
by: fyi85 |
last post by:
I have 8.1.5 on Windows 2003, when I do from CLP with auto commit off:
db2 update table set column=something
and then
db2 terminate
and then
db2 connect to db
db2 select updated column from...
|
by: Ralf Assmann |
last post by:
Hi there,
we have the following problem using a DB2 version 7 on z/OS, referring
also <a...
|
by: debasisdas |
last post by:
trigger sample code Ex#10
=======================
INSTEAD OF TRIGGER
----------------------------------------
create or replace trigger mytrig instead of delete or insert or update on eview...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |