Post reply

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

  • November 5, 2008 at 8:42 am

    #894297

    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]

  • November 5, 2008 at 8:56 am

    #894315

    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://qa.sqlservercentral.com/Forums/FindPost597326.aspx

  • November 5, 2008 at 9:20 am

    #894336

    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]

  • November 5, 2008 at 9:37 pm

    #894664

    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)
    Intro to Tally Tables and Functions

  • December 3, 2008 at 9:00 am

    #906950

    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

  • December 3, 2008 at 9:16 am

    #906964

    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?

  • December 3, 2008 at 10:23 am

    #907024

    Awesome, thanks Matt!

  • December 3, 2008 at 10:55 am

    #907038

    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.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • December 3, 2008 at 6:52 pm

    #907280

    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)
    Intro to Tally Tables and Functions

  • December 3, 2008 at 7:11 pm

    #907289

    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?

  • December 3, 2008 at 7:23 pm

    #907298

    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.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • December 5, 2008 at 9:14 am

    #908406

    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

  • December 6, 2008 at 5:47 pm

    #908876

    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]

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

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