Oracle Query_Trigger [message #353309] |
Mon, 13 October 2008 05:24  |
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 #353344 is a reply to message #353309] |
Mon, 13 October 2008 07:37   |
Frank
Messages: 7901 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  |
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;
|
|
|