update column on same table using database trigger [message #671558] |
Thu, 06 September 2018 06:45 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a59247482a11edb9544247f102223e8d?s=64&d=mm&r=g) |
mist598
Messages: 1195 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 #671564 is a reply to message #671562] |
Thu, 06 September 2018 07:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13923 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a59247482a11edb9544247f102223e8d?s=64&d=mm&r=g) |
mist598
Messages: 1195 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 #671583 is a reply to message #671581] |
Thu, 06 September 2018 09:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a59247482a11edb9544247f102223e8d?s=64&d=mm&r=g) |
mist598
Messages: 1195 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 #671594 is a reply to message #671583] |
Thu, 06 September 2018 13:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 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 Go to previous message](/forum/theme/orafaq/images/up.png) |
Bill B
Messages: 1971 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?
|
|
|