Re: trigger using a clause where with :NEW
From: Gerard H. Pille <ghp_at_skynet.be>
Date: Tue, 08 Nov 2011 19:30:53 +0100
Message-ID: <4eb97516$0$5040$ba620e4c_at_news.skynet.be>
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
Date: Tue, 08 Nov 2011 19:30:53 +0100
Message-ID: <4eb97516$0$5040$ba620e4c_at_news.skynet.be>
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. Received on Tue Nov 08 2011 - 12:30:53 CST