Need to add the datetime stamp to indicate when the row was updated 1

[somanivivek]

Hi,

In a table, I have 'lastupdateddate' column.
This has to get updated with current datetime when a row is inserted.
The insert statement will not insert anything into this column...
How to go about it...
For e.g
I have a table

Create table test
( name varchar(20), lastupdateddate datetime())

I have set the default value of lastupdateddate to getdate().

When I run the insert statement, it gives error that the Column name or number of supplied values does not match table definition...
My insert statement is :
Insert into test values ('vivek')

How can I use the above statement and still get the current datetime inserted into the lastupdated field ??

Thanks

Vivek

 

[MissyEd]

Hi Vivek

You will need to set the default value for LastUpdatedDate. To do this, go into the table design. Clicking on the field will give you the properties which should include one named default - not sure of the exact name. Each property has a blank field next to it, in this field type in the function name: getdate() in the and save the table.

Modify your INSERT INTO statement to:

INSERT INTO test([Name]) VALUES('vivek') Missy Ed - Bolton, UK

 

[somanivivek]

Thanks Missy,

But my problem is that I can't specify the field names for the values to be inserted into...

This is a classic example of revamping the old code in which I don't know at what all places the insert statements have been used... the insert statements that have been used are " Insert into values ('xxx','yyy',..etc.) wthout any regard to column names in the table...

Now if I have to add a datetime stamp field that gets automatically set to current date, I have issue because I can't modify the insert statements...have no idea at where all the inserts have been used...

Hence I'm stuck...

I'm searching for some other alternative than modifying Insert statements....something like Datetime-Identity!!

Vivek

 

[hubud]

vivek,
Try changing the syntax of your insert to:

Insert into test (name)
values ('bob')

This should work, you need to specify where it's to be inserted.

matt

 

[hubud]

Looks like my reply was old hat, that'll teach me to refresh my browser more often.
The problem doesn't appear to be the insert of the getdate() it is the insert of [name]. If you can't change the code I'm not sure how you would get around this.
What do you mean by using Identity. Can't modify identity unless you specify [set identity_insert on] and I don't think you can insert getdate() to an identity column as these only accept int datatypes

matt

 

[somanivivek]

matt,

you hit the nail on butt...

that's exactly where i'm stuck...

Need something that behaves like identity col. i.e get updated automatically on 'insert into values(...)' command but accepts current datetime as value..


Through enterprise manager, I can modify the table and add another column as to indicate lastupdated date...
But can't modify any code in the ASP pages that populates this database to insert into this...
Unfortunately for me, the code for inserts in ASP pages is not with col names as explained earlier..

Still searching for some other alternatives option..


vivek

 

[MeanGreen]

Here is your insert statement to use your default of getdate().

insert into test ('Whatever',default)

This will use your default you have defined on the table. What you really need is a trigger to populate the lastupdatetime whenever someone updates the row.

Hope this helps.

 

[MissyEd]

No, cant see any other way of doing it apart from specifying the columns. Missy Ed - Bolton, UK

 

[MissyEd]

Well, thats me show, you learn something new every day Missy Ed - Bolton, UK

 

[MeanGreen]

Since you can't change the original table, create another table with the lastupdatetime. Then create an insert/update trigger on the original table to add/update the rows of your new table.

Hope this helps.

 

[swampBoogie]

What you can do is

create a new table that includes the new column with default value getdate()
move the old data to this table
drop the old table
create a view with the same name and columns as the old table
create an instead of trigger for the view that inserts into the new table

Instead of triggers requires sql server 2000

 

[mabicamp]

just try this, why not use a timestamp column datatype?

marlon NGAS