Visual Basic code to write the last time a Table was updated in a DB

Question

Visual Basic code to write the last time a Table was updated in a DB

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Thanks for the feedback.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

OP_HELP SSCrazy Points: 2618 More actions · Answer 2

Just out of curiousity, I asked this other question on this board, but under another topic forum

I just thought this was pertinent to my subject. Do you have any thoughts on this:

http://www.sqlservercentral.com/Forums/FindPost597326.aspx

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Answered at that thread.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

umanpowered (10/28/2008)
Jonathan,
Thanks very much for the detailed row-update solution. I have to go through it and ascertain if I can implement it for my purposes. (Still a newbie...).
I implemented the getdate() function successfully. But rather than have such data appear on every single row in the table, is there some code I can use to have the updated table trigger the creation of another table (like a one-row table) which shows the Date Time?
I'd rather use such a one-row/datetime solution because I can easily "Objectify" its content on the ASP pages I write .
Once again, thanks for your help.

Yes... create another table with table name and a "last updated" column. Write a trigger on every table that updates that "last updated" column for all inserts, updates, deletes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

OP_HELP SSCrazy Points: 2618 More actions · Answer 5

rbarryyoung (11/3/2008)
As Jonathan said: "getdate()" needs to be the definition of the column's default value, and not the definition of the column itself.

Hi,

As a side question, do you know of a resource online which lists all the functions that act as definitions on columns like the "getdate()"? thnx.

-uman

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

umanpowered (12/3/2008)
rbarryyoung (11/3/2008)
As Jonathan said: "getdate()" needs to be the definition of the column's default value, and not the definition of the column itself.
Hi,
As a side question, do you know of a resource online which lists all the functions that act as definitions on columns like the "getdate()"? thnx.
-uman

Just about any scalar function can be used in this way. The idea is you're creating what is called a computed column (other DB's I've worked with in the past call them "formula columns"). A computed column doesn't hold data per se, but rather hold a function or equation it calculates to show you the result.

So - just like you had set this one up (which would have actually shown you the current date each and eery time you pulled that data), you could have columns compute results at any time.

Take a look through Books Online for "computed columns" for more details on them.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

OP_HELP SSCrazy Points: 2618 More actions · Answer 7

OP_HELP

SSCrazy

Points: 2618

December 3, 2008 at 10:23 am

#907024

Awesome, thanks Matt!

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

Matt Miller (12/3/2008)
Just about any scalar function can be used in this way. The idea is you're creating what is called a computed column (other DB's I've worked with in the past call them "formula columns"). A computed column doesn't hold data per se, but rather hold a function or equation it calculates to show you the result.
So - just like you had set this one up (which would have actually shown you the current date each and eery time you pulled that data), you could have columns compute results at any time.
Take a look through Books Online for "computed columns" for more details on them.

Matt,

Just to clarify one thing, I was recommending a default column constraint and not a computed column for the solution. The fundamentals are pretty much the same, but they are different things.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

umanpowered (12/3/2008)
rbarryyoung (11/3/2008)
As Jonathan said: "getdate()" needs to be the definition of the column's default value, and not the definition of the column itself.
Hi,
As a side question, do you know of a resource online which lists all the functions that act as definitions on columns like the "getdate()"? thnx.
-uman

Lookup "functions" in Books Online... it actually breaks all of the fuctions into several categories.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Jonathan Kehayias (12/3/2008)
Matt Miller (12/3/2008)
Just about any scalar function can be used in this way. The idea is you're creating what is called a computed column (other DB's I've worked with in the past call them "formula columns"). A computed column doesn't hold data per se, but rather hold a function or equation it calculates to show you the result.
So - just like you had set this one up (which would have actually shown you the current date each and eery time you pulled that data), you could have columns compute results at any time.
Take a look through Books Online for "computed columns" for more details on them.
Matt,
Just to clarify one thing, I was recommending a default column constraint and not a computed column for the solution. The fundamentals are pretty much the same, but they are different things.

I understand - I was actually answering the follow-on question:

As a side question, do you know of a resource online which lists all the functions that act as definitions on columns like the "getdate()"? thnx.

I should have been a bit more specific.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 11

I actually didn't mean that post to seem like I was addressing you, but re-reading it, it sure does look like I was. Sorry about that. The use of a function as a computed column or a default value on a column as a constraint will basically be the same.

I just wanted to point out that my recommendation was to use a default constraint with a value of getdate() which is persisted with the date and time that the change occured. Whereas a computed column would have the current datetime for when the query was being run.

OP_HELP SSCrazy Points: 2618 More actions · Answer 12

Jonathan Kehayias (12/3/2008)
I just wanted to point out that my recommendation was to use a default constraint with a value of getdate() which is persisted with the date and time that the change occured. Whereas a computed column would have the current datetime for when the query was being run.

Actually, this is pretty useful information that I didn't initially consider....ie., these two implementations you've brought up.

Could you render an example of a CREATE TABLE statement with PERSISTENCE on the column in a SQL script? or is this implemented somewhere else?

Many thanks, as always

-uman

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

umanpowered (12/5/2008)
Jonathan Kehayias (12/3/2008)
I just wanted to point out that my recommendation was to use a default constraint with a value of getdate() which is persisted with the date and time that the change occured. Whereas a computed column would have the current datetime for when the query was being run.
Actually, this is pretty useful information that I didn't initially consider....ie., these two implementations you've brought up.
Could you render an example of a CREATE TABLE statement with PERSISTENCE on the column in a SQL script? or is this implemented somewhere else?

Jonathan was talking about setting a Default column value. This is how you do it:

CREATE TABLE [dbo].[Test1](

[Name] [varchar](50) NULL,

[BirthDate] [datetime] NULL CONSTRAINT [DF_Test1_BirthDate] DEFAULT (getdate())

)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]