Re: Oracle Hackers
Date: 15 Dec 1994 15:04:22 GMT
Message-ID: <3cpltm$djf_at_news.nynexst.com>
Although I don't consider myself a "hacker" - the answer is simple if I understand the question. Assuming the 3rd column is an effective date and that there won't be more than 1 NULL date at any point in time.
A PRE-Statement Trigger is used to stamp the last record - just before the new record is inserted.
I hope this helps!
SQL> CREATE TABLE hack
2 ( a number not null 3 , b varchar2(1) not null 4 , c date )
SQL> / Table created.
SQL> CREATE TRIGGER hack_stamp
2 BEFORE INSERT ON hack
3 BEGIN
4 UPDATE hack
5 SET c=SYSDATE 6 WHERE c IS NULL;
7 EXCEPTION
8 WHEN OTHERS THEN NULL;
9 END;
10 .
SQL> / Trigger created.
SQL> insert into hack values ( 1,'a','12-dec-94' ) SQL> / 1 row created.
SQL> c/12/13/
1* insert into hack values ( 2,'a','13-dec-94')
SQL> /
1 row created.
SQL> select * from hack;
A B C
---------- - ---------
1 a 12-DEC-94
2 a 13-DEC-94
SQL> insert into hack values ( 3, 'a', NULL );
1 row created.
SQL> select * from hack;
A B C
---------- - ---------
1 a 12-DEC-94
2 a 13-DEC-94
3 a
SQL> set null ~
SQL> /
A B C
---------- - ---------
1 a 12-DEC-94
2 a 13-DEC-94
3 a ~
SQL> REM ----- NOW IS THE PAYOFF!!!! -----
SQL> INSERT INTO HACK VALUES (4, 'a', NULL)
SQL> /
1 row created.
SQL> select * from hack;
A B C
---------- - ---------
1 a 12-DEC-94
2 a 13-DEC-94
3 a 15-DEC-94
4 a ~
SQL> REM ----- VOILA!!!! -----
SQL> spool off
Sincerely,
Martin Farber
Independent Oracle Consultant "A Jack of all trades and a slave to one."
| NYNEX Science & Technology
| 500 Westchester Ave, Rm 1B-23
| White Plains, NY 10604
| -----------------
| email: farber_at_nynexst.com
| Voice: 914/644-2656
| FAX: 914/644-2216
In article 8rd_at_master.di.fc.ul.pt, barreto_at_master.di.fc.ul.pt (Joao Barreto Fernandes) writes:
>
> Oracle Hackers all over the world .
>
> Imagine that one of my tables looks like this:
>
> 1 a 12/12/94
> 2 a 13/12/94
> 3 a NULL
>
> and I wanted to insert (4,a) and the SYSDATE is 14/12/94.
> The result I want is
>
> 1 a 12/12/94
> 2 a 13/12/94
> 3 a 14/12/94
> 4 a NULL
>
> Can anyone give me a solution on how to do this.
> If you can , please help me.
> Thanks in advance, Joao Fernandes.
>
>PS. One can use whatever techniques he wants, but please explain it.
>
>
>--
># Joao Carlos de Oliveira Barreto Fernandes #
># e-mail : barreto_at_master.di.fc.ul.pt #
># <a href=http://www.di.fc.ul.pt/~barreto> </a> #
Received on Thu Dec 15 1994 - 16:04:22 CET