Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Query_Trigger (10g)
Oracle Query_Trigger [message #353309] Mon, 13 October 2008 05:24 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,
I have an issue while creating trigger;-
I have a table let suppose A with column C, D, E, F.
Now I have to write a trigger on table A which should
trigger when there is update on column C in Table A
and then update the column D with the new value of C.
I tried to do it as follows but the Column D is not being updated with the new value.
 

Create trigger trigg_name
  After update of C on A
 For each row
Is
declare
 Pragma Autonomous_transaction;
  New_val  varchar2(27);
Begin
 New_val := :new.C;
Dbms_output.put_line(New_val) ;   
            /* this outputs the new value when 
       evoking an update statement on Table A (Column C)  */
If updating then
Update A set D=:new.C where C=:old.C; 
       /* After compiling the trigger and evoking 
          an update statement on Table A (Column C) 
          the D column still not being updated */ 
Commit;
End;


Please let me know if you have solution about
this or has an alternative solution to it using trigger.

Thank you,
Re: Oracle Query_Trigger [message #353311 is a reply to message #353309] Mon, 13 October 2008 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should first read what is a trigger and how to use it.
Your reluctance to make any effort to read the documentation is dreadful and might dishearten to answer to other newbie.

Regards
Michel
Re: Oracle Query_Trigger [message #353315 is a reply to message #353309] Mon, 13 October 2008 05:30 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

First of all why do you want to commit inside a trigger ?

Thumbs Up
Rajuvan.
Re: Oracle Query_Trigger [message #353319 is a reply to message #353315] Mon, 13 October 2008 05:35 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

Commiting each rwecord after each updation. But It degrades the Performance.. Am I right?

So please give me the solution for this...
Re: Oracle Query_Trigger [message #353322 is a reply to message #353319] Mon, 13 October 2008 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That's in the doc.

Regards
Michel
Re: Oracle Query_Trigger [message #353323 is a reply to message #353309] Mon, 13 October 2008 05:43 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Not actually because of that .

Quote:
Triggers fire as part of the transaction. A transaction is the space between two COMMIT statements. Therefore it is not allowed to have a COMMIT inside a trigger


What will happen if you rolled back the actual transaction in your case ? Triggered even will be committed even if you rollback the update. Is this what you are looking for ?

Thumbs Up
Rajuvan.
Re: Oracle Query_Trigger [message #353341 is a reply to message #353319] Mon, 13 October 2008 07:09 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Commiting each rwecord after each updation. But It degrades the Performance.. Am I right?


You are asking for more trouble by combining triggers with autonomous transaction (if not used correctly).

Check this link.

http://www.oracle.com/technology/oramag/oracle/08-sep/o58asktom.html

Regards

Raj

P.S : I am not a big fan of triggers. I will try to avoid it as much as I can
Re: Oracle Query_Trigger [message #353344 is a reply to message #353309] Mon, 13 October 2008 07:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I agree with Michel. Why don't you take some time to back off and learn some basic Oracle- and PL/SQL-skills and concepts?
Maybe follow a training, maybe study some guides or books.
You continue to throw in questions that make it quite clear that you have no idea of the concepts of coding in Oracle.

I don't mean this to offend you, but rather to help you. I think you will never make any real progress unless you give yourself the time to step back and study.
Re: Oracle Query_Trigger [message #353345 is a reply to message #353309] Mon, 13 October 2008 07:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You really don't want to be committing each change. Trust us on this.

Points:
1) Your trigger only fires for updates - the If Updating check is pointless
2) Your current code, if it worked, would update every record that had the same value of column C - it might be a PK fields, but you haven't said so. I doubt this is what you want.
3 The only reason you need an autonomous transaction in there is because you have an update on table A. You don't need this update.

I think your trigger could be rewritten as:
Create or replace trigger trigg_name
  After update of C on A
 For each row
Begin
  :new.D := :new.C;
End;
Previous Topic: script to grant insert, update for all tables in a schema
Next Topic: Problem with pl/sql for loop
Goto Forum:
  


Current Time: Wed Dec 07 04:34:38 CST 2016

Total time taken to generate the page: 0.33232 seconds