UPDATE on a column with a set default

[Mike Husler]

If we have a column with a default value set, say GETDATE( ), how can we
assure that value is reset on an UPDATE (not an INSERT) without changing
the client code that does the updating?
I'd rather stay away from triggers, etc if possible.

Thanks,
Mike Husler

[David Portas]

Mike Husler wrote:

If we have a column with a default value set, say GETDATE( ), how can we
assure that value is reset on an UPDATE (not an INSERT) without changing
the client code that does the updating?
I'd rather stay away from triggers, etc if possible.

Thanks,
Mike Husler

If you actually put UPDATEs in client code then you are certainly
making life difficult for yourself. Had you used a stored procedure for
data access you could just have modified the proc. Any good reason why
you aren't using procs?

Otherwise I think you'll have to use a trigger or put the DEFAULT
keyword in your UPDATE:

UPDATE your_tbl
SET col = DEFAULT ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[me321701]

Unless you are controlling all avenues of database access, default
values do NOT ensure that a value is reset on insert or update.
Default only applies if no value is provided.

If you're looking to provide the date a row was inserted or updated,
then a trigger is a valid choice. And they work fine for this.

Mark

On Mon, 30 Jan 2006 12:47:03 -0700, Mike Husler
<Mi**************@noaa.gov> wrote:

If we have a column with a default value set, say GETDATE( ), how can we
assure that value is reset on an UPDATE (not an INSERT) without changing
the client code that does the updating?
I'd rather stay away from triggers, etc if possible.

Thanks,
Mike Husler