Home » SQL & PL/SQL » SQL & PL/SQL » Trigger fires on deletion of uncommitted data and changes saved (Oracle Database 11g Release 11.1.0.7.0 - 64bit Production Windows)
Trigger fires on deletion of uncommitted data and changes saved [message #573284] Wed, 26 December 2012 04:58 Go to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

Hi all,
I've noticed a behavior which seemed kind of weird to me.
Maybe you can tell me if you think it's right or wrong for Oracle to behave like that.
What basically happened is that I've set up a trigger to capture deleted rows from testtab into testtab_del.

I have inserted and immediately after - deleted a row from testtab.
Then I inserted another row, committed, and checked my testtab_del table.

I've seen that val1 was inserted and committed into testtab_del.
This happened in spite of the fact that this row never existed as a *committed row*.

What do you think about this behavior in this scenario? Works as designed or not?


SQL> show user
USER is "ANDREY"
SQL> col tcol for a10
SQL> drop table testtab;

Table dropped.

SQL> drop table testtab_del;

Table dropped.

SQL>
SQL> create table testtab (tcol varchar2(100));

Table created.

SQL> create table testtab_del (tcol varchar2(100));

Table created.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER test_trg
  2    BEFORE DELETE ON testtab
  3    FOR EACH ROW
  4      BEGIN
  5       insert into testtab_del values(:old.tcol)
  6      end;
  7      /

Trigger created.

SQL>
SQL> select * from testtab;

no rows selected

SQL> insert into testtab values('val1');

1 row created.

SQL> delete from testtab where tcol='val1';

1 row deleted.

SQL> select * from testtab;

no rows selected

SQL>
SQL> insert into testtab values('val2');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from testtab;

TCOL
----------
val2

SQL> select * from testtab_del;

TCOL
----------
val1

SQL>
SQL>

Re: Trigger fires on deletion of uncommitted data and changes saved [message #573285 is a reply to message #573284] Wed, 26 December 2012 05:01 Go to previous messageGo to next message
Littlefoot
Messages: 19885
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Everything you did works as expected.
Re: Trigger fires on deletion of uncommitted data and changes saved [message #573286 is a reply to message #573284] Wed, 26 December 2012 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read the Database Concepts, espacially what's about the transaction, it seems you lack the basic of its principles.

Regards
Michel
Re: Trigger fires on deletion of uncommitted data and changes saved [message #573290 is a reply to message #573286] Wed, 26 December 2012 06:03 Go to previous messageGo to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

Michel Cadot wrote on Wed, 26 December 2012 13:05
Please read the Database Concepts, espacially what's about the transaction, it seems you lack the basic of its principles.

Regards
Michel



No I don't.
I just stated a specific scenario with a particular point(even if this behavior is correct).
Thanks for a meaningless answer.

Regards,
Andrey

[Updated on: Wed, 26 December 2012 06:04]

Report message to a moderator

Re: Trigger fires on deletion of uncommitted data and changes saved [message #573291 is a reply to message #573290] Wed, 26 December 2012 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
No I don't.


Yes, you do. Read again.
Tell us:
1/ Why do you think the behaviour is not the correct one?
2/ What does "commit" do?

If you answer this we can explain where you didn't understand what is the transaction.

Regards
Michel
Re: Trigger fires on deletion of uncommitted data and changes saved [message #573292 is a reply to message #573291] Wed, 26 December 2012 06:21 Go to previous messageGo to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

I did not say it is not the correct one. It is you who wants to point your finger at people in a patronizing intonation.

I said that I understand it is a delete that a commit was done after it, and it made the trigger fire.
It just seems a little weird to me, because this way a row that *never existed as a committed data* will be caught by this trigger,
Inserted and committed, and I wanted to ask what people think about it(in spite of the fact that it's *correct* or *expected* technical behavior).

This behavior makes it difficult to know whether an actual *committed* row was deleted from a table, or an uncommitted on.
Is there an alternative for it?
Re: Trigger fires on deletion of uncommitted data and changes saved [message #573293 is a reply to message #573292] Wed, 26 December 2012 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I said that I understand it is a delete that a commit was done after it, and it made the trigger fire.


Oh, this is not the transaction you don't understand, this is the trigger.
The trigger is fired by the statement you define it on, not by COMMIT (moreover there is no trigger on commit).

Database Application Developer's Guide - Fundamentals
Chapter 9 Coding Triggers
Juts after the first example of "creating triggers":

Quote:
The trigger is fired when DML operations (INSERT, UPDATE, and DELETE statements) are performed on the table.


Regards
Michel
Re: Trigger fires on deletion of uncommitted data and changes saved [message #573294 is a reply to message #573293] Wed, 26 December 2012 06:50 Go to previous messageGo to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

Yes, it will fire after the triggered DML, yet, when rollback is performed - the insert performed by the trigger will rollback too.
This creates an impression that the transaction in the trigger depends on the commit or rollback of the DML's,
But I understand that it *actually* depends on the ROLLBACK or COMMIT of the triggering session(yes, I understand, whole session, not transaction).

So, the question asked is(again...):

In the presented scenario, is it possible to know whether an actual *committed* row was deleted from a table, or an uncommitted one?
Can we capture only deletion of committed rows by a trigger/other method?

Regards,
Andrey

[Updated on: Wed, 26 December 2012 06:51]

Report message to a moderator

Re: Trigger fires on deletion of uncommitted data and changes saved [message #573295 is a reply to message #573294] Wed, 26 December 2012 07:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2078
Registered: January 2010
Senior Member
I still struggle to understand your question. You start a session. Session issues DELETE from testtab table. Before delete row level trigger fires and inserts a row into testtab_del table. Then row which caused trigger to fire is deleted from testtab table. At this point if you commit both insert into testtab_del and delete from testtab will be committed. And both will be rolled back if if you rollback. You want to commit/rollback delete from testtab and insert inro testtab_del separately? Then create trigger as PARGMA AUTONOMOUS_TRANSACTION.

SY.
Re: Trigger fires on deletion of uncommitted data and changes saved [message #573296 is a reply to message #573295] Wed, 26 December 2012 07:19 Go to previous messageGo to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

Hi Solomon Yakobson,
Thanks for your reply.

Your explanation helped me to understand that both insert and delete were actually committed,
So I was wrong when I said that this row was *never committed*.

I understand now better that a commit or rollback applies to all session, not only to a transaction,
And that's what happened here. My only claims are with my application design, not Oracle.

Thank you for the clear explanation.

Re: Trigger fires on deletion of uncommitted data and changes saved [message #573298 is a reply to message #573296] Wed, 26 December 2012 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I understand now better that a commit or rollback applies to all session, not only to a transaction,


In the end, you don't understand what is a transaction.
Saying that a commit or rollback applies to all sessions is just wrong. It applies and actually ends the current transaction. What "apply" to all OTHER sessions/transactions is (but nothing is actually applied) the visibility of the modifications made the completed transaction.
Commit or rollback is irrelevant to the current transaction; how it ends does not change anything of what it has done, only the persistency of this work.

Once again, read Database Concepts
Chapter 1 Introduction to the Oracle Database
Section Overview of Transactions
Chapter 4 Transaction Management
Section Introduction to Transactions

Regards
Michel
Re: Trigger fires on deletion of uncommitted data and changes saved [message #573301 is a reply to message #573296] Wed, 26 December 2012 08:29 Go to previous messageGo to next message
Littlefoot
Messages: 19885
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Andrey_R wrote on Wed, 26 December 2012 14:19

Your explanation helped me to understand that both insert and delete were actually committed,
So I was wrong when I said that this row was *never committed*.


As far as I can tell, you are wrong (again). You never issued a COMMIT, so nothing (not INSERT, not DELETE) was committed. And you were right when you said that the first record was "never committed" because - it was not.

Re-read Michel's reply once again: it is not a COMMIT that causes the trigger to fire - it is the DELETE statement. Then re-read Solomon's reply once again (the one that made you "understand" things).

Shortly: COMMIT and/or ROLLBACK have nothing to do with the whole story. There's a table and a BEFORE DELETE trigger on that table. As soon as you delete a record from the table, the trigger fires and does its job.
Re: Trigger fires on deletion of uncommitted data and changes saved [message #573303 is a reply to message #573298] Wed, 26 December 2012 09:33 Go to previous messageGo to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

Michel Cadot wrote on Wed, 26 December 2012 16:12
Quote:
I understand now better that a commit or rollback applies to all session, not only to a transaction,


In the end, you don't understand what is a transaction.
Saying that a commit or rollback applies to all sessions is just wrong. It applies and actually ends the current transaction. What "apply" to all OTHER sessions/transactions is (but nothing is actually applied) the visibility of the modifications made the completed transaction.
Commit or rollback is irrelevant to the current transaction; how it ends does not change anything of what it has done, only the persistency of this work.

Once again, read Database Concepts
Chapter 1 Introduction to the Oracle Database
Section Overview of Transactions
Chapter 4 Transaction Management
Section Introduction to Transactions

Regards
Michel


I said all session, not sessionS.
What I meant was the *whole* session(one session, not all).


Ok, I have got the phrase wrong, but there's no need to kill me for that(and save your docs, they were written by people, not the other way around).


>>>>As far as I can tell, you are wrong (again). You never issued a COMMIT, so nothing (not INSERT, not DELETE) was committed

Well, I've posted my code, and it shows clearly that a commit was issue, so I don't understand what you are talking about.

SQL> commit;

Commit complete.



Regards,
Andrey

[Updated on: Wed, 26 December 2012 09:36]

Report message to a moderator

Re: Trigger fires on deletion of uncommitted data and changes saved [message #573307 is a reply to message #573303] Wed, 26 December 2012 09:49 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
so what question remains unanswered?
Re: Trigger fires on deletion of uncommitted data and changes saved [message #573308 is a reply to message #573307] Wed, 26 December 2012 09:53 Go to previous messageGo to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

BlackSwan wrote on Wed, 26 December 2012 17:49
so what question remains unanswered?

None.

Thanks to everybody.

Regards,
Andrey
Re: Trigger fires on deletion of uncommitted data and changes saved [message #573310 is a reply to message #573308] Wed, 26 December 2012 10:00 Go to previous messageGo to next message
Littlefoot
Messages: 19885
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh, right - you committed, that is true, but kind of too late, when it was irrelevant for the issue you are/were concerned of.
Re: Trigger fires on deletion of uncommitted data and changes saved [message #573317 is a reply to message #573310] Wed, 26 December 2012 10:56 Go to previous messageGo to next message
Littlefoot
Messages: 19885
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Let me try with some examples.
SQL> create table testtab (tcol varchar2(100));

Table created.

SQL> create table testtab_del (tcol varchar2(100));

Table created.

SQL> create or replace trigger test_trg
  2    before delete on testtab
  3    for each row
  4  begin
  5    insert into testtab_del values (:old.tcol);
  6  end;
  7  /

Trigger created.

SQL> insert into testtab values ('val1');

1 row created.

SQL> -- it is irrelevant whether you COMMIT here or not. ROLLBACK would matter, though,
SQL> -- because there wouldn't be any row in the TESTTAB table.
SQL> -- Let's try ROLLBACK:
SQL> rollback;

Rollback complete.

SQL> delete from testtab where tcol = 'val1';

0 rows deleted.

SQL> select * from testtab_del;

no rows selected

SQL> -- of course, there's nothing there as we deleted 0 records
SQL>
SQL> insert into testtab values ('val1');

1 row created.

SQL> -- Let's try COMMIT:
SQL> commit;

Commit complete.

SQL> delete from testtab where tcol = 'val1';

1 row deleted.

SQL> select * From testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL> -- the same result as you got (and you didn't commit). If you commit now,
SQL> -- DELETE will become permanent and
SQL> -- a) there will be no records in TESTTAB table
SQL> -- b) there will be 1 record in TESTTAB_DEL table
SQL> -- So let's try it:
SQL> commit;

Commit complete.

SQL> select * from testtab;

no rows selected

SQL> select * from testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL> -- Moreover, it will be permanent "forever" (even if I disconnect):
SQL> disco
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> connect scott/tiger@ora10
Connected.

Session altered.

SQL> select * from testtab;

no rows selected

SQL> select * from testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL> -- See? Permanent. Let's see what happens with ROLLBACK.
SQL> delete from testtab_del;

1 row deleted.

SQL> commit;

Commit complete.

SQL> insert into testtab values ('val1');

1 row created.

SQL> delete from testtab;

1 row deleted.

SQL> select * from testtab;

no rows selected

SQL> select * from testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL> -- Rollback:
SQL> rollback;

Rollback complete.

SQL> select * from testtab;

no rows selected

SQL> select * from testtab_del;

no rows selected

SQL> -- No record in any of these tables. Why? Because ROLLBACK reverted the INSERT
SQL> -- statement as well, not only the DELETE.
SQL> -- Once again, but this time commit the INSERT.
SQL> insert into testtab values ('val1');

1 row created.

SQL> commit;

Commit complete.

SQL> delete from testtab;

1 row deleted.

SQL> select * from testtab;

no rows selected

SQL> select * from testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL> -- Rollback - at this point - will rollback only the DELETE; INSERT isn't affected
SQL> -- because it was already committed.
SQL> rollback;

Rollback complete.

SQL> select * from testtab;

TCOL
--------------------------------------------------------------------------------
val1

SQL> select * from testtab_del;

no rows selected

SQL>

Finally, if I may, the autonomous transaction: along with a trigger it is a good mechanism for logging changes because even if you rollback the main transaction, insert into the log table isn't affected by that command.
SQL> drop table testtab;

Table dropped.

SQL> drop table testtab_del;

Table dropped.

SQL> create table testtab (tcol varchar2(100));

Table created.

SQL> create table testtab_del (tcol varchar2(100));

Table created.

SQL> create or replace trigger test_trg
  2    before delete on testtab
  3    for each row
  4  declare
  5    pragma autonomous_transaction;
  6  begin
  7    insert into testtab_del values (:old.tcol);
  8    commit;
  9  end;
 10  /

Trigger created.

SQL> -- Note the COMMIT statement within the trigger; normally, you can't commit in a
SQL> -- trigger, but autonomous transaction allows you to do that.
SQL> insert into testtab values ('val1');

1 row created.

SQL> commit;

Commit complete.

SQL> delete from testtab;

1 row deleted.

SQL> -- Obviously, there's no record in TESTTAB, but it is inserted into the TESTTAB_DEL:
SQL> select * from testtab;

no rows selected

SQL> select * from testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL> -- ROLLBACK now. Previously, without the autonomous transaction, the result was:
SQL> -- record was put back into the TESTTAB and removed from TESTTAB_DEL.
SQL> -- With the autonomous transaction, record is put back into the TESTTAB, but - because
SQL> -- of COMMIT in a trigger - the record remains in TESTTAB_DEL.
SQL> rollback;

Rollback complete.

SQL> select * from testtab;

TCOL
--------------------------------------------------------------------------------
val1

SQL> select * from testtab_del;

TCOL
--------------------------------------------------------------------------------
val1

SQL>
Re: Trigger fires on deletion of uncommitted data and changes saved [message #573321 is a reply to message #573317] Wed, 26 December 2012 12:25 Go to previous message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the autonomous transaction: along with a trigger it is a good mechanism for logging changes because even if you rollback the main transaction, insert into the log table isn't affected by that command.


And to clearly specify, is not a good practice to archive deleted rows for the same reason as your example clearly shows it.

Regards
Michel
Previous Topic: triggers in oracle plsql
Next Topic: Delete Duplicate Records using Anaytical function
Goto Forum:
  


Current Time: Thu Dec 18 17:29:10 CST 2014

Total time taken to generate the page: 0.08028 seconds