Re: trigger using a clause where with :NEW

From: ddf <oratune_at_msn.com>
Date: Tue, 8 Nov 2011 11:43:03 -0800 (PST)
Message-ID: <baa68b6c-711e-409c-9868-2674b34ff28e_at_h12g2000vbf.googlegroups.com>

On Nov 8, 10:30 am, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> ddf wrote:
> > I've guessed at the table structure and what I think you're trying to
> > do; your example will eventually compile but will update the columns
> > to NULL (I set the updated columns to NOT NULL to illustrate the
> > point):
> > SQL>  CREATE OR REPLACE TRIGGER NAMEOFTRIGGER
> >    2  BEFORE INSERT OR UPDATE OF columnname1, columnname2 ON TABLNAME
> >    3  FOR EACH ROW
> >    4
> >    5  BEGIN
> >    6          :new.columnname1 := to_char(:new.id);
> >    7          :new.columnname2 := to_char(:new.id);
> >    8  END;
> >    9  /
>
> > SQL>  select * from tablname;
>
> >          ID COLUMNNAME1          COLUMNNAME2          UPDATEDT
> > ---------- -------------------- -------------------- ---------
> >           1 1                    1                    09-NOV-11
> >           2 2                    2                    10-NOV-11
> >           3 3                    3                    08-NOV-11
> >           4 4                    4                    09-NOV-11
> >           5 5                    5                    10-NOV-11
> >           6 6                    6                    08-NOV-11
> >           7 7                    7                    09-NOV-11
> >           8 8                    8                    10-NOV-11
> >           9 9                    9                    08-NOV-11
> >          10 10                   10                   09-NOV-11
>
> > David Fitzjarrell
>
> When I first learned about relational database theory, there was something about removing
> redundancy.  That was a long time ago.  Times have changed.- Hide quoted text -
>
> - Show quoted text -

I agree; I don't recommend duplicating data in a table (or across tables if it can be avoided). I probably should have stated that but I treated this as an example.

David Fitzjarrell Received on Tue Nov 08 2011 - 13:43:03 CST