Re: How to update a column to its default value?

kielhd wrote:

> Hi,
> I am looking into this problem for a while without solving it.
> HELP!!!!!!!!!!
>
> The following table is defined:
>
> CREATE TABLE DEFAULT_TABLE(
> TEXT varchar2(32),
> LAST_UPDATE date default sysdate
> );
>
> After setting an initial value to the column TEXT with the command
>
> insert into DEFAULT_TABLE ( TEXT ) values ( 'John' )
>
> the table looks like:
>
> select text, to_char(last_update, 'dd-mon-yyyy hh24:mi:ss') from
> DEFAULT_TABLE
>
> TEXT TO_CHAR(LAST_UPDATE,
> -------------------------------- --------------------
> John 28-apr-2003 15:53:01
>
> Here is my Problem:
> -------------------
> I need to update the row, where the column TEXT equals the value
> 'John' to the value 'George'. I expect the value in the column
> LAST_UPDATE to change automatically to the current value of SYSDATE.
>
> Here is what I did:
>
> update DEFAULT_TABLE set TEXT = 'George' where TEXT = 'John';
>
> Afterwards, the table looks like this:
>
> select text, to_char(last_update, 'dd-mon-yyyy hh24:mi:ss') from
> DEFAULT_TABLE;
>
> TEXT TO_CHAR(LAST_UPDATE,
> -------------------------------- --------------------
> George 28-apr-2003 15:53:01
>
> The value of the column TEXT has changed as expected, but the value of
> LAST_UPDATE remained the same.
> I want the value of the column LAST_UPDATE to change when I update the
> column TEXT.
>
> Hope there is some help to this problem!
>
> Regards from rainy Germany
> Henning

CREATE OR REPLACE TRIGGER bi_<table_name> BEFORE INSERT
ON <table_name>
FOR EACH ROW DECLARE BEGIN
   :NEW.last_update := SYSDATE;

END bi_<table_name>;
/

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
(remove one 'x' from my email address to reply)