What is the preferred way of setting a default value in MS SQL Server?

[Blackbird Crow Raven]

I want a timestamp column ("Created") to be the current time.

In Firebird, I did that with a "Before Insert" trigger, but SQL Server only
seems to have an "Instead Of" (bizarre/prapodivne!) and AfterInsert trigger.

If one of these is the way to go, what is the syntax? I tried: "SET CREATED
= GetDate()" but that didn't compile.

Alternately, should I give the column a default value? If so, does anybody
know how to do this in EMS SQL Manager Lite (give a timestamp value a
default value of "Now")?

[Craig]

Post by Blackbird Crow Raven
I want a timestamp column ("Created") to be the current time.
In Firebird, I did that with a "Before Insert" trigger, but SQL Server only
seems to have an "Instead Of" (bizarre/prapodivne!) and AfterInsert trigger.
If one of these is the way to go, what is the syntax? I tried: "SET CREATED
= GetDate()" but that didn't compile.
Alternately, should I give the column a default value? If so, does anybody
know how to do this in EMS SQL Manager Lite (give a timestamp value a
default value of "Now")?

This is my standard scripts for adding changed columns to tables in SQL
Server. Replace <Tablename> with the actual table name obviously.

ALTER TABLE <tablename> ADD DateMod DATETIME
GO

ALTER TABLE <tablename> ADD DateIns DATETIME
GO

CREATE TRIGGER trg_<tablename>_Insert
ON <tablename> FOR INSERT
AS
BEGIN
UPDATE <tablename>
SET
DateIns = GETDATE(),
DateMod = GETDATE()
FROM <tablename>
INNER JOIN Inserted ON <tablename>.<tablename>Key =
Inserted.<tablename>Key
END
GO

CREATE TRIGGER trg_<tablename>_Update
ON <tablename> FOR UPDATE
AS
BEGIN
UPDATE <tablename>
SET
DateMod = GETDATE()
FROM <tablename>
INNER JOIN Inserted ON <tablename>.<tablename>Key =
Inserted.<tablename>Key
END
GO

[Helmut Woess]

Am Thu, 13 Jul 2006 18:43:50 -0500 schrieb Blackbird Crow Raven:
...

Post by Blackbird Crow Raven
In Firebird, I did that with a "Before Insert" trigger, but SQL Server only
seems to have an "Instead Of" (bizarre/prapodivne!) and AfterInsert trigger.

Yes, and this kind of trigger is very helpfull for updating views which
consists of more than one table or calculated values. With the "instead of"
you can capture the event and do whatever you want/need to do.

Post by Blackbird Crow Raven
Alternately, should I give the column a default value? If so, does anybody
know how to do this in EMS SQL Manager Lite (give a timestamp value a
default value of "Now")?

In EMS Manager open table, select field with doubleclick, goto "default",
select "value" and type "getdate()" (without quotes) in the field below.
That's all, you don't need a trigger.

bye, Helmut

[Blackbird Crow Raven]

Post by Helmut Woess
In EMS Manager open table, select field with doubleclick, goto "default",
select "value" and type "getdate()" (without quotes) in the field below.
That's all, you don't need a trigger.

--It won't let me do that. I can only get at the "Default" tab using
"User-defined data type" (not Standard Data Type or Computed). When I select
User-defined | Default tab | select "Value" | enter "getdate()" (without the
quotes), I get:

--------------- SQL ---------------

ALTER TABLE [dbo].[ITEMS_DIVERTED]
ALTER COLUMN [CREATED] [timestamp] NOT NULL
GO

ALTER TABLE [dbo].[ITEMS_DIVERTED]
ADD DEFAULT getdate() FOR [CREATED]
GO

---------- ERROR MESSAGE ----------

Cannot alter column 'CREATED' to be data type timestamp

=======
Apparently the problem is that SQL Server doesn't allow timestamp vals to be
defaulted; using datetime instead works, though (what the heck? what's the
diff?)

[Helmut Woess]

Am Fri, 14 Jul 2006 20:46:17 -0500 schrieb Blackbird Crow Raven:
...

Post by Blackbird Crow Raven
Apparently the problem is that SQL Server doesn't allow timestamp vals to be
defaulted; using datetime instead works, though (what the heck? what's the
diff?)

The SQL Server timestamp data type has nothing to do with times or dates.
SQL Server timestamps are binary numbers that indicate the relative
sequence in which data modifications took place in a database. The
timestamp data type was originally implemented to support the SQL Server
recovery algorithms.

See more in the OnlineManual:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_6fn4.asp

bye, Helmut

[Blackbird Crow Raven]

Post by Helmut Woess
The SQL Server timestamp data type has nothing to do with times or dates.
SQL Server timestamps are binary numbers that indicate the relative
sequence in which data modifications took place in a database. The
timestamp data type was originally implemented to support the SQL Server
recovery algorithms.

I'm beginning to think some Firebird, Interbase, and maybe even Oracle reps
should go up to Redmond and slap a few people around a bit. A timestamp
that's not a timestamp--what a bunch of doofuses!

[John Herbster]

... should go up to Redmond and slap a few people around
a bit.
A timestamp that's not a timestamp

And TForm.PixelsPerInch that is *not* pixels/inch.

-what a bunch of doofuses!

[Helmut Woess]

Post by Blackbird Crow Raven
I'm beginning to think some Firebird, Interbase, and maybe even Oracle reps
should go up to Redmond and slap a few people around a bit. A timestamp
that's not a timestamp--what a bunch of doofuses!

By the way, they should ask MS why even in SQL2005 there are no datatypes
"date" and "time", only datetime.

bye, Helmut

[Oliver Townshend]

Post by Blackbird Crow Raven
I'm beginning to think some Firebird, Interbase, and maybe even Oracle
reps should go up to Redmond and slap a few people around a bit. A
timestamp that's not a timestamp--what a bunch of doofuses!

Originally implemented by Sybase, so send them there.

Some people read the documentation to find out what these things are.

Oliver Townshend

[Viatcheslav V. Vassiliev]

CREATE TABLE <MyTable>
(
...
Stamp DATETIME DEFAULT GetDate(),
...
)


//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)

Post by Blackbird Crow Raven
I want a timestamp column ("Created") to be the current time.
In Firebird, I did that with a "Before Insert" trigger, but SQL Server
only seems to have an "Instead Of" (bizarre/prapodivne!) and AfterInsert
trigger.
If one of these is the way to go, what is the syntax? I tried: "SET
CREATED = GetDate()" but that didn't compile.
Alternately, should I give the column a default value? If so, does anybody
know how to do this in EMS SQL Manager Lite (give a timestamp value a
default value of "Now")?
--
Be the first one on your block to read the uproariously hilarious novel
"the Zany Time Travels of Warble McGorkle" (Book 2 in a trilogy). Download
it here: http://codecentral.borland.com/item.aspx?id=24002