Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Triggers to Update Calculated Fields
A copy of this was sent to "Michael" <Michael.Wrobel_at_unisys.com>
(if that email address didn't require changing)
On Wed, 8 Dec 1999 18:16:46 -0500, you wrote:
>I want to use triggers to update calculated fields. I am creating a trigger
>for each row after an insert, update, or delete. Something with the trigger
>is not going through because no updates that were specified in the trigger
>are happening. Has anyone used triggers to keep calculated fields updated.
>Is there a recommended strategy?
>
>I have 6 fields and if any are inserted, updated, or deleted, I want to run
>an automatic recalculation for 2 calculated fields. I have been attempting
>the recalculation with an update clause inside of the trigger.
>
>Thanks,
>
>Mike
>
You do *not* update in a ROW EACH ROW TRIGGER against the base table. You use a BEFORE, FOR EACH ROW trigger and just modify the :new record. for example:
tkyte_at_8.0> create table t ( a int, b int, c int );
Table created.
tkyte_at_8.0>
tkyte_at_8.0> create or replace trigger t_trigger
2 BEFORE insert or update on t FOR EACH ROW
3 begin
4 :new.c := :new.a+:new.b;
5 end;
6 /
Trigger created.
tkyte_at_8.0>
tkyte_at_8.0> insert into t ( a, b ) values ( 1, 3 );
1 row created.
tkyte_at_8.0> select * from t;
A B C
---------- ---------- ----------
1 3 4
tkyte_at_8.0> update t set a = 55;
1 row updated.
tkyte_at_8.0> select * from t;
A B C
---------- ---------- ----------
55 3 58
tkyte_at_8.0>
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Dec 09 1999 - 06:49:10 CST
![]() |
![]() |