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

On Mon, 28 Apr 2003 10:54:27 -0500, Ed Stevens <nospam_at_noway.nohow> wrote:

>On 28 Apr 2003 07:22:12 -0700, kielhd_at_freenet.de (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
>
>
>If you want the value to change, then you are responsible for changing
>it yourself. You don't say how the table is defined, but I'd guess
>that LAST_UPDATE is defined with a default value of SYSDATE. That
>would explain you getting a value into LAST_UPDATE on the insert
>without you specifying it. But after that, it *has* *a* value so
>doesn't need a default value.
>
>This is a perfect application for a trigger to insure that LAST_UPDATE
>gets updated whenever the row is updated, regardless of if the
>application remembers to or not.

And, oopsies, on second reading I see the table def *was* supplied . .(mea culpa) . . and was exactly as I surmised. Received on Mon Apr 28 2003 - 12:24:46 CDT