Home » SQL & PL/SQL » SQL & PL/SQL » Computed columns in Oracle
Computed columns in Oracle [message #249621] Thu, 05 July 2007 04:19 Go to next message
majstoru
Messages: 68
Registered: October 2006
Location: Serbia
Member
Hi,

I have some columns in my Oracle table which is result of some operation on some another columns, for example:

column1 (numeric 15, 2)
column2 (numeric 15, 2)
column3 (numeric 15, 2) // computed (column1 + column2)

How to solve this situation, or how can Oracle automaticly do this operation after some changes on a table!

I try with:

CREATE OR REPLACE TRIGGER XE.TABLE1_X AFTER INSERT OR DELETE OR UPDATE ON XE.TABLE1
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
  IF INSERTING OR UPDATING THEN
    UPDATE TABLE1 SET COLUMN3 = :NEW.COLUMN1 + :NEW.COLUMN2;
  END IF;
END;


but oracle raise error:

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

What is wrong?

Thanks in advance...
Re: Computed columns in Oracle [message #249624 is a reply to message #249621] Thu, 05 July 2007 04:46 Go to previous messageGo to next message
flyboy
Messages: 1779
Registered: November 2006
Senior Member
Hi,

if you want to set COLUMN3 only in the row, you are inserting/updating, and not in ALL rows of TABLE1 (as UPDATE statement does), just change your trigger to BEFORE (instead of AFTER) and change the UPDATE statement to simple assignment
:NEW.COLUMN3 := :NEW.COLUMN1 + :NEW.COLUMN2;

P.S. Are you aware that the issued UPDATE in your trigger fires ALL triggers on TABLE1 update including the trigger which invoked it?
[Edit: Added last paragraph]

[Updated on: Thu, 05 July 2007 06:10]

Report message to a moderator

Re: Computed columns in Oracle [message #249628 is a reply to message #249621] Thu, 05 July 2007 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove DELETE from the trigger definition then you can remove the test in your code.

Regards
Michel
Re: Computed columns in Oracle [message #249633 is a reply to message #249621] Thu, 05 July 2007 06:50 Go to previous messageGo to next message
majstoru
Messages: 68
Registered: October 2006
Location: Serbia
Member
Hi,

Thanks for reply, my problem is solved! Cool
Re: Computed columns in Oracle [message #249798 is a reply to message #249633] Thu, 05 July 2007 21:59 Go to previous messageGo to next message
rleishman
Messages: 3701
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Or just wait for 11g to be released - I believe it supports computed columns without additional storage!

Ross Leishman
Re: Computed columns in Oracle [message #249818 is a reply to message #249798] Fri, 06 July 2007 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
without additional storage

I don't think this is true.
Without trigger yes but I think virtual columns are precomputed and stored as they are based on the same technique than FBI.
Moreover you'd can create an index on this column and even partition on it.

Regards
Michel
Re: Computed columns in Oracle [message #249888 is a reply to message #249621] Fri, 06 July 2007 07:32 Go to previous messageGo to next message
majstoru
Messages: 68
Registered: October 2006
Location: Serbia
Member
Hi,

I set up new trigger which is computing values before update!

If 11g will have this options that will be cool, does anybody know where I can find sometnihg about new thinks in Oracle 11g and any date of first release?

Thanks to all!
Re: Computed columns in Oracle [message #249895 is a reply to message #249888] Fri, 06 July 2007 08:10 Go to previous message
Michel Cadot
Messages: 59794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing really official on 11g but T. Kyte presentation.
(Unfortunatly I lost it!)

Regards
Michel
Previous Topic: Clarification on 'N' th row
Next Topic: datbase trigger & application trigger ??
Goto Forum:
  


Current Time: Wed Nov 26 20:17:37 CST 2014

Total time taken to generate the page: 0.08572 seconds