Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    52

    Unanswered: Updating modified/updated date column?

    If your tables contain created and modified/updated dates what is the best practice for these?

    1. Should you use UTC dates?
    2. Do you use a default for the creation date (I assume yes)?
    3. Should you create a trigger to handle the last update date? Or do you update the column directly in your stored procedures that modify data?

    Also, as an aside if you store the user who created/updated the record do you store a foreign key reference to the user table or do you store the username as a varchar? Of course I know you'd normally store the fk, but I wasn't sure if the "logging" nature of the column suggests storing a string value.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    1. are you worried about leap seconds.

    2. you assume correctly.

    3. depends. do you have one developer who has written one update stored procedure and there is no way else to update the table? then yes, do it in the proc. if you are in a larger crazier environment where the table can be updated many ways by many people, then perhaps you want this in a trigger.

    but hold on. I have never updated the same table the trigger was on. Does it then fire another update trigger?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2008
    Posts
    52
    Can you elaborate on the leap seconds? Was that tongue in cheek or real?

    Quote Originally Posted by Thrasymachus
    but hold on. I have never updated the same table the trigger was on. Does it then fire another update trigger?
    I believe this is a no. The trigger does not cause a recursive loop.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •