"Data Type" in SQL 2000 2

[perryair]

Hi,
I would like to create column with data type of 'date'
that will get updated automatically when updating the row
so that I can 'SELECT' the newer item via ASP.

Any idea how to start?

Thanks

 

[JohnDTampaBay]

Create the column in the table with data type datetime or smalldatetime. To have the column updated automatically when a row is updated, create a trigger on the table like so:

CREATE TRIGGER trg[b]TableName[/b]UpdateDate
ON [b]TableName[/b]
AFTER INSERT, UPDATE
AS
BEGIN
  UPDATE t
  SET [b]DateColumn[/b] = GetDate()
  FROM [b]TableName[/b] AS t
    INNER JOIN Inserted AS i
    ON t.[b]KeyColumn[/b] = i.[b]KeyColumn[/b]

END

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[mrdenny]

Or instead of using a trigger have the code that updates the record(s) update the DateColumn instead.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[perryair]

Thanks John,
It says "Incorrect syntax near 'KeyColumn'"

My KeyColumn is 'id' ..

Please advice.

 

[JamesLean]

Can you post your actual code then?

--James

 

[perryair]

Well, I don't have any code..
I have SQL table named: MarqueeTop with the following Columns:

linkid (int)
summary (ntext)
url (ntext)
updated (datetime)

on the ASP form:
SELECT * FROM MarqueeTop ORDER BY updated DESC

The error message appear when I try to add a trigger.

 

[JohnDTampaBay]

I imagine it did since I don't know the schema of your database. Denny's concept is much better, but doesn't address ad hoc updates to the table. KeyColumn represents the column(s) necessary to join the two tables.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[JohnDTampaBay]

Ah. It looks like LinkID is your KeyColumn.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[JamesLean]

What I mean is, can you post your actual CREATE TRIGGER code?

--James

 

[perryair]

CREATE TRIGGER trgMarqueeTopUpdateDate
ON MarqueeTop
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE t
SET updated = GetDate()
FROM MarqueeTop AS t
INNER JOIN Inserted AS i
ON t.KeyColumn = i.KeyColumn

 

[JamesLean]

You need to replace "keycolumn" in your trigger code with the actual column name of your identifying column (LinkID?).

Also, I would probably define a DEFAULT constraint on the updated column to insert the current datetime and then just have the trigger handle the UPDATEs.

--James

 

[perryair]

Thanks !!!!! you've got a star.