Home » SQL & PL/SQL » SQL & PL/SQL » After Trigger is not working .
After Trigger is not working . [message #360316] Thu, 20 November 2008 06:57 Go to next message
ashishsaha04
Messages: 2
Registered: November 2008
Junior Member
We are having a trigger like this
1) The Trigger is written on a table TABLEONE. The TABLEONE contains columns KEYID,COL1,COL2

2) In the Trigger ,we are calling a stored Procedure STROREPROC1(:new.KEYID)

3) I am using following query in the stored procedure STROREPROC1

Select COL1,COL2 from TABLEONE Where KEYID = parameterkeyid

Question

1) I executed Update TABLEONE Set COL1 = ‘FOUR’, COL2 = ‘FIVE’ Where KEYID = ‘KEYONE’

2) Whether in the STROREPROC1, Select COL1,COL2 from TABLEONE Where KEYID = parameterkeyid

will retrieve FOUR and FIVE



Right now it is retrieving the old values in COL1 and COL2

Re: After Trigger is not working . [message #360318 is a reply to message #360316] Thu, 20 November 2008 07:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When you were writing this post, did it once cross your mind to actuall post the code of the trigger and the procedure?

Regardless of the answer to that question, kindly post them now, to give us a fighting chance of figuringthis out.
Re: After Trigger is not working . [message #360341 is a reply to message #360316] Thu, 20 November 2008 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you just write a test case and execute it you will immediately see it returns an error because Oracle does not allow this.
But, GREAT, your question prove you already understand why it does not allow it.

Regards
Michel
Re: After Trigger is not working . [message #360405 is a reply to message #360341] Thu, 20 November 2008 21:16 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Unless his trigger is autonomous, in which case the trigger should return the row before it was updated even though this is an after update trigger. Thus one reason why using autonomous_transaction inside a trigger can be a really bad idea.

SQL> drop table tableone
  2  /

Table dropped.

SQL> 
SQL> create table tableone
  2  (
  3   keyid varchar2(10)
  4  ,col1 varchar2(10)
  5  ,col2 varchar2(10)
  6  )
  7  /

Table created.

SQL> 
SQL> insert into tableone values ('KEYONE','THREE','FOUR');

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> select * from tableone;

KEYID      COL1       COL2
---------- ---------- ----------
KEYONE     THREE      FOUR

SQL> 
SQL> create or replace trigger aur_tableone
  2  after update on tableone
  3  for each row
  4  declare
  5     r1 tableone%rowtype;
  6  begin
  7     select *
  8     into r1
  9     from tableone
 10     where keyid = :new.keyid;
 11  raise_application_error(-20999,'(keyid,col1,col2)=('||r1.keyid||','||r1.col1||','||r1.col2||')');
 12  end;
 13  /

Trigger created.

SQL> 
SQL> Update tableone Set COL1 = 'FOUR', COL2 = 'FIVE' Where KEYID = 'KEYONE';
Update tableone Set COL1 = 'FOUR', COL2 = 'FIVE' Where KEYID = 'KEYONE'
       *
ERROR at line 1:
ORA-04091: table KEVIN.TABLEONE is mutating, trigger/function may not see it
ORA-06512: at "KEVIN.AUR_TABLEONE", line 4
ORA-04088: error during execution of trigger 'KEVIN.AUR_TABLEONE'


SQL> 
SQL> create or replace trigger aur_tableone
  2  after update on tableone
  3  for each row
  4  declare
  5     pragma autonomous_transaction;
  6     r1 tableone%rowtype;
  7  begin
  8     select *
  9     into r1
 10     from tableone
 11     where keyid = :new.keyid;
 12  raise_application_error(-20999,'(keyid,col1,col2)=('||r1.keyid||','||r1.col1||','||r1.col2||')');
 13  end;
 14  /

Trigger created.

SQL> 
SQL> Update tableone Set COL1 = 'FOUR', COL2 = 'FIVE' Where KEYID = 'KEYONE';
Update tableone Set COL1 = 'FOUR', COL2 = 'FIVE' Where KEYID = 'KEYONE'
       *
ERROR at line 1:
ORA-20999: (keyid,col1,col2)=(KEYONE,THREE,FOUR)
ORA-06512: at "KEVIN.AUR_TABLEONE", line 9
ORA-04088: error during execution of trigger 'KEVIN.AUR_TABLEONE'


SQL> 


So, for those who don't get it, I ask you, why is your after update trigger seeing the row as it was before it was updated? The select statement should have picked up the updates right? But it didn't. You need to understand autonomous transactions to know why. Once you do you will realize why autonomous transactions inside a trigger are bad if you are using them to effect a specific kind of business logic instead of using them to simply audit with.

The OP is likely not creating and after update trigger as autonomous but this is related enough and fun enough to comment on.

Kevin

[Updated on: Thu, 20 November 2008 21:17]

Report message to a moderator

Re: After Trigger is not working . [message #360486 is a reply to message #360405] Fri, 21 November 2008 02:48 Go to previous messageGo to next message
ashishsaha04
Messages: 2
Registered: November 2008
Junior Member
Thanks Kevin,
Please Suggest me any replacement for pragma autonomous transactions so my trigger will work.
Re: After Trigger is not working . [message #360516 is a reply to message #360486] Fri, 21 November 2008 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Aaargh! Suggesting workaround to bad stuff ALWAYS lead to question how to implement it when the correct behaviour is to get rid of all this shit.
You can put a bold, red, blinking, size 10 warning, it will never be seen.
This is why I didn't speak about this.

ashishsaha04, why do you want to develop something that you know is not correct?

Regards
Michel
Re: After Trigger is not working . [message #360598 is a reply to message #360516] Fri, 21 November 2008 09:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Are you telling us that you are indeed using AUTONOMOUS TRANSACTION as the mechanism to get your trigger to finish. If so we are telling you that this is bad coding because it has the logical error you are experieincing.

To solve such a problem you must remove the autonomous transaction from your trigger code. This will of course mean you can't write the trigger you want to write and must have a different approach to your problem. There are two solutions most people use:

1) do the work inside your applications instead. This gets around the problem but does not centralize the logic in any way so you must make certain that all access to the data contains the logic. It is the traditional way of doing it. It of course leads to the maintenance issues we are all familiar with. Unless you have a highly controled system development process, sooner or later someone will use a tool or write some code that does not have the logic you need and your data will get corrupted.

2) use INSTEAD-OF-TRIGGERS. This allows you to centralize logic and thus make sure that all applications will be using it. This would be my choice. It follows the same design philosophy as the trigger solution you were trying initialy. Nothing is perfect however. One downside to INSTEAD-OF-TRIGGERS is that they are an advanced feature. You will find that although all advanced features of oracle work, they don't necessarily work together in combination with other advanced features. For example, since instead of triggers involve use of a view layer, if the view is complex enough, there will be no root ROWID associated with it. Thus you cannot reference ROWID in your code on this view. If you have existing code that uses ROWID on specific tables, when you introduce INSTEAD-OF-TRIGGERS, this code may stop working an must be re-written. In the end this is not a bad thing as you should never have been using ROWID in a production solution anyway. Another example is MERGE. The MERGE statment will not run against views with INSTEAD-OF-TRIGGERS so if your code uses merge statements, you will have to re-write them too. This too in my mind may not be a bad thing for although MERGE can be faster that the traditional UPSERT logic in some cases, it is not actually a well understood database feature. It does not for example do what people think it does. Most people believe MERGE is the implementation of UPSERT (eg. update else insert logic), all in one statement. But it ain't. If you think it is, then you do not know how it works. For example, try creating a suite of triggers on a table along with some test data, then do a merge and see what triggers get fired. You will be confused. Imagine what this would do to auditing triggers?

But, will you please confirm your problem. You never actually posted any working code, nor said that you were using AUTONOMOUS TRANSACTIONS in your triggers. Are you?

As you can see, Michel and I sometimes have a difference of opinion on how to deal with newbies and the technology. I am happy to give newbies plenty of rope to hang themselves. I want to expose newbies to different thought processes and take advantage of their excitement and energy as a newbie, to get them looking at the power of Oracle as a development platform. I prefer guidence over protection as a way of working with newibies. But please let me be clear. Michel is correct in saying that I am not telling you to use AUTONOMOUS TRANSACTIONS inside your triggers. I am telling you not to do that. Additionally you should step back and understand the process you are working with before you jump into coding a solution for it. Lastly growth often requires pain. If you are going to use something new, find a mentor to assist you, or expect some pain.

Good luck, Kevin
Re: After Trigger is not working . [message #360603 is a reply to message #360598] Fri, 21 November 2008 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for this explaination,
Michel
Re: After Trigger is not working . [message #360605 is a reply to message #360603] Fri, 21 November 2008 10:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I respect your opinions immensly Michel. It is good to have your opinion next to mine, especially when it differs. It not only gives the OP and others access to alternative views, but also elevates my posts to a higher stature by virtue of their proximity to your posts.

Kevin
Re: After Trigger is not working . [message #360608 is a reply to message #360605] Fri, 21 November 2008 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're too kind with me.
I'm sorry that my english skills are too low to post longer and more detailled answers. Hopefully some people like you can do it.

Regards
Michel
Re: After Trigger is not working . [message #360611 is a reply to message #360608] Fri, 21 November 2008 10:46 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I like getting in line behind you. You do a great job of opening doors for people to post through. OraFAQ would be way less without you.

Kevin
Previous Topic: Sorting logic not working for all data conditions
Next Topic: SUM of records
Goto Forum:
  


Current Time: Tue Dec 06 08:34:51 CST 2016

Total time taken to generate the page: 0.05336 seconds