Home » SQL & PL/SQL » SQL & PL/SQL » update column on same table using database trigger
update column on same table using database trigger [message #671558] Thu, 06 September 2018 06:45 Go to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Hi All,

with xxc_test
as
(
select 1111 order, 10 order_amount , NULL xxc_col1 from dual
union all
select 2222 order, 20 order_amount , NULL xxc_col1 from dual
)

if i update the order_amount with some value for 1111 order then i need to update the xxc_col1 with sysdate.
I need database trigger.
Oracle Database 11.2.0.4.0 version.
Re: update column on same table using database trigger [message #671559 is a reply to message #671558] Thu, 06 September 2018 07:04 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Could you please help me on this? please let me know if you need more details.
Re: update column on same table using database trigger [message #671560 is a reply to message #671558] Thu, 06 September 2018 07:04 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
What is the purpose of the code you have posted? Are you saying that you want a trigger on that SELECT statement? You can't do that. You have triggers on tables.
Re: update column on same table using database trigger [message #671561 is a reply to message #671559] Thu, 06 September 2018 07:05 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
mist598 wrote on Thu, 06 September 2018 13:04
Could you please help me on this? please let me know if you need more details.

Come on mate, that's not even 20 minutes.

Doing your job is not my day job.
Re: update column on same table using database trigger [message #671562 is a reply to message #671560] Thu, 06 September 2018 07:08 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Thanks John.

I have existing row Order#1111 and order_amount is 10.
If i update order_amount for Order#1111 with 30 then the xxc_col1 column should be update with sysdate.

[Updated on: Thu, 06 September 2018 07:10]

Report message to a moderator

Re: update column on same table using database trigger [message #671563 is a reply to message #671562] Thu, 06 September 2018 07:13 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
If you're determined to use a bad method like this, check out this:

https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#LNPLS01374

Let us know what you tried and what doesn't work.


Note that this method will neither scale nor perform well. Triggers are evil and should never, ever enforce things like this. I've yet to see a use case for a trigger which is not better served by an API outside of audit.
Re: update column on same table using database trigger [message #671564 is a reply to message #671562] Thu, 06 September 2018 07:15 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
So if the order_amount column is updated then you want the xxc_col1 column to be set to sysdate.
That's really basic use of triggers.
Have a read of the documentation on triggers - there's examples.
You'll need a before update row trigger.
You'll need to compare the old and new values of order_amount.

Have a try yourself.
Re: update column on same table using database trigger [message #671565 is a reply to message #671564] Thu, 06 September 2018 07:30 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Cookiemonster,

create or replace trigger XXC_AMT_TRG
BEFORE UPDATE
   ON XXC_TEST
   FOR EACH ROW 
begin
    if :OLD.ORDER_AMOUNT=:NEW.ORDER_AMOUNT Then
    UPDATE XXC_TEST  
       SET XXC_COL1=SYSDATE
     WHERE 1=1
       AND ORDER_NO=:OLD.ORDER_NO;
	end if;
end;
select * from XXC_TEST

update XXC_TEST
set ORDER_AMOUNT=30
where ORDER_NO=1111

ORA-04091: table APPS.XXC_TEST is mutating, trigger/function may not see it
ORA-06512: at "APPS.XXC_AMT_TRG", line 3
ORA-04088: error during execution of trigger 'APPS.XXC_AMT_TRG'

[Updated on: Thu, 06 September 2018 07:40]

Report message to a moderator

Re: update column on same table using database trigger [message #671566 is a reply to message #671565] Thu, 06 September 2018 07:40 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
mist598 wrote on Thu, 06 September 2018 05:30
Hi Cookiemonster,


create or replace trigger XXC_AMT_TRG
AFTER UPDATE
   ON XXC_TEST
   FOR EACH ROW 
begin
    if updating('ORDER_AMOUNT') Then
	UPDATE XXC_TEST  
	   SET XXC_COL1=SYSDATE
	 WHERE 1=1
	   AND ORDER=:OLD.ORDER;
	end;
	end if;
end;

Getting ORA-04091 table is mutating
Can't issue SQL statement against same table upon which the trigger is based.
UPDATE statement above changes every row in the table!
BEFORE UPDATE trigger is needed
Re: update column on same table using database trigger [message #671567 is a reply to message #671566] Thu, 06 September 2018 07:41 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Tried Mutating error is coming.
Re: update column on same table using database trigger [message #671568 is a reply to message #671567] Thu, 06 September 2018 07:48 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
mist598 wrote on Thu, 06 September 2018 05:41
Tried Mutating error is coming.
Can't issue SQL statement against same table upon which the trigger is based.

NEW:XXC_COL1=SYSDATE;
Re: update column on same table using database trigger [message #671569 is a reply to message #671568] Thu, 06 September 2018 07:53 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
Can't issue SQL statement against same table upon which the trigger is based.

NEW:XXC_COL1=SYSDATE;
What is the solution? i don't understand...

Thank you,
Re: update column on same table using database trigger [message #671570 is a reply to message #671569] Thu, 06 September 2018 08:07 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
We're not going to spoon feed you the answer.

You don't need an update. Please review the link Roachcoach gave and pay attention to new and old as cookiemonster said. This is as basic as a trigger can be.
Re: update column on same table using database trigger [message #671571 is a reply to message #671570] Thu, 06 September 2018 08:11 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Yes you are correct and i know this.

I used the below code and able to do in Toad tool i mean XXC_COL1 column is updating.
But i am getting below error while i am updating the order_amount from front end.
ORA-04091: table APPS.XXC_TEST is mutating, trigger/function may not see it
ORA-06512: at "APPS.XXC_AMT_TRG", line 3
ORA-04088: error during execution of trigger 'APPS.XXC_AMT_TRG'
create or replace trigger XXC_AMT_TRG
BEFORE UPDATE
   ON XXC_TEST
   FOR EACH ROW 
BEGIN
      if updating('ORDER_AMOUNT') Then
      :NEW.XXC_COL1:=SYSDATE;
     end if;
end;
Re: update column on same table using database trigger [message #671572 is a reply to message #671571] Thu, 06 September 2018 08:53 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's no way that trigger is causing that error.
Check if you haven't still got an older version of the trigger (with the update statement) on the DB.
Re: update column on same table using database trigger [message #671573 is a reply to message #671572] Thu, 06 September 2018 09:01 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
cookiemonster wrote on Thu, 06 September 2018 14:53
There's no way that trigger is causing that error.
Check if you haven't still got an older version of the trigger (with the update statement) on the DB.
He did say the table already has a number of triggers.

As I said, triggers are evil Wink
Re: update column on same table using database trigger [message #671574 is a reply to message #671573] Thu, 06 September 2018 09:09 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
Roachcoach wrote on Thu, 06 September 2018 15:01
cookiemonster wrote on Thu, 06 September 2018 14:53
There's no way that trigger is causing that error.
Check if you haven't still got an older version of the trigger (with the update statement) on the DB.
He did say the table already has a number of triggers.

As I said, triggers are evil Wink
Yes! You run some statement, and without your knowledge something totally unrelated can occur. In C, we used to call that a "side effect". And any C programmer knows that if he is caught writing code with side effects, he will be shot.


Re: update column on same table using database trigger [message #671575 is a reply to message #671574] Thu, 06 September 2018 09:32 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Actually i want to update XXC_COL1 column with sysdate for the existing data, but it is updating for the new created rows as well.

Is there any other ways to do this?
Re: update column on same table using database trigger [message #671576 is a reply to message #671575] Thu, 06 September 2018 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
mist598 wrote on Thu, 06 September 2018 07:32
Actually i want to update XXC_COL1 column with sysdate for the existing data, but it is updating for the new created rows as well.

Is there any other ways to do this?
yes, do NOT use trigger.
Re: update column on same table using database trigger [message #671577 is a reply to message #671576] Thu, 06 September 2018 09:39 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Then what is the best to update automatically?
Re: update column on same table using database trigger [message #671579 is a reply to message #671577] Thu, 06 September 2018 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
mist598 wrote on Thu, 06 September 2018 07:39
Then what is the best to update automatically?
"Automagically" is a figment of your imagination.
NOTHING happens automatically when it come to computers. Everything must be manually programmed.

which metric measures "best"?
What is unit of measure for best?

How does dumb database know or decide which rows & which columns need to be updated?
Re: update column on same table using database trigger [message #671580 is a reply to message #671575] Thu, 06 September 2018 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

mist598 wrote on Thu, 06 September 2018 16:32
Actually i want to update XXC_COL1 column with sysdate for the existing data, but it is updating for the new created rows as well.
...

Not possible with a trigger on UPDATE.


[Updated on: Thu, 06 September 2018 09:52]

Report message to a moderator

Re: update column on same table using database trigger [message #671581 is a reply to message #671580] Thu, 06 September 2018 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post a test case that proves what you claim and we can reproduce what you have.

Re: update column on same table using database trigger [message #671582 is a reply to message #671558] Thu, 06 September 2018 09:57 Go to previous messageGo to next message
pablolee
Messages: 2881
Registered: May 2007
Location: Scotland
Senior Member
As has been said, triggers suck. Create an API through which the data in the table is manipulated. Use the code in that API to perform the task that you need to do.
Re: update column on same table using database trigger [message #671583 is a reply to message #671581] Thu, 06 September 2018 09:59 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Michel,

I want to update xxc_col1 column with sysdate for the particular line_no for order.
Needed automatically update process when i am trying to update order_amount value.

with xxc_test
as
(
select 1111 order, 10 order_amount , 1 line_no, NULL xxc_col1 from dual
union all
select 1111 order, 15 order_amount , 2 line_no ,NULL xxc_col1 from dual
union all
select 2222 order, 20 order_amount , 1 line_no ,NULL xxc_col1 from dual
)

If i update ORDER_AMOUNT with 50 for LINE_NO for 1111 order then needed to update XXC_COL1 with sysdate.
 
ORDER_NO   LINE_NO    ORDER_AMOUNT	XXC_COL1
1111	     1	        50	        06-09-2018 10:30:10
1111	     2	        15	
2222	     1	        20	
Re: update column on same table using database trigger [message #671584 is a reply to message #671583] Thu, 06 September 2018 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ ORDER is a reserved word, you can't use it as column name, post a VALID test case.
2/ What you posted is NOT a test case how can you UPDATE a WITH clause?
3/ Your trigger do that!


Re: update column on same table using database trigger [message #671594 is a reply to message #671583] Thu, 06 September 2018 13:12 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
mist598 wrote on Thu, 06 September 2018 10:59

If i update ORDER_AMOUNT with 50 for LINE_NO for 1111 order then needed to update XXC_COL1 with sysdate.
 
ORDER_NO   LINE_NO    ORDER_AMOUNT	XXC_COL1
1111	     1	        50	        06-09-2018 10:30:10
1111	     2	        15	
2222	     1	        20	
There are two order_no equal to 1111.
A simple trigger setting :new or :old to the sysdate with the proper condition will set the xxc_col1 to sysdate, unless you are just plain not explaining what you want correctly.
The trigger execute only on the row(s) it updating. It cannot possibly update other rows as you said if you just use :old and :new.
Re: update column on same table using database trigger [message #671608 is a reply to message #671594] Fri, 07 September 2018 09:25 Go to previous message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
You can't use an on update trigger to set the XXC_COL1 COLUMN TO SYSDATE, however an BEFORE INSERT OR UPDATE OF ORDER_AMOUNT would do what you want and the body of the trigger would be a single statement with no if. Is that enough hint to do what you need to do?
Previous Topic: Help in YIELD [Excel Function] formula in PLSQL
Next Topic: Help in Understanding a complex (complex at least for me) sql to solve sudoku
Goto Forum:
  


Current Time: Wed Sep 23 02:47:50 CDT 2020