Re: Trigger for INSERT

Hi!
Good Database practice or not it is possible with a trigger like: You will wan't to ensure that the a,b,c fields are not null perhaps by create unique index or via a constraint.

 CREATE OR REPLACE TRIGGER yourname
 BEFORE INSERT OR UPDATE OF adr,a,b,c ON "MY_TAB"  FOR EACH ROW
   BEGIN
    IF INSERTING THEN
       :new.adr := :new.a+:new.b+:new.c;    ELSIF UPDATING THEN
   :new.adr := :new.a+:new.b+:new.c;
   END IF;
  END; As THE LAST POSTER SAYS there are problems with doing this. The main one I can see is that you could end up with the same adr on many records if you are not careful.

But this might give you a start...
SQL> insert into my_tab (a,b,c) values(1,2,3);

1 row created.

SQL> select * from my_tab;

      ADR A B C SALES --------- --------- --------- --------- ---------

        6 1 2 3

SQL> update my_tab set a=2 ;

1 row updated.

SQL> select * from my_tab;

      ADR A B C SALES --------- --------- --------- --------- ---------

        7 2 2 3

In article <7lq22b$gum$1_at_nnrp1.deja.com>,   lanzinge_at_in.tum.de wrote:
> Hello *,
>
> I have a table my_tab with the following structure:
> CREATE TABLE my_tab (
> adr INTEGER CONSTRAINT pk PRIMARY KEY,
> a INTEGER,
> b INTEGER,
> c INTEGER,
> sales INTEGER);
> Now the 'adr' column is a value that may be calculated from the
columns
> 'a','b','c'. Is it possible to set up an BEFORE INSERT TRIGGER to
> calculate that 'adr'? I tried to do so, but as the trigger body has to
> execute an INSERT statement on 'my_tab' there always is a recursion
:-(
>
> Any suggestions?
>
> Thanks
> J.Lanzinger
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jul 05 1999 - 10:10:07 CDT