New Poll is up and a silly mistake…

One of the big reasons I redesigned the poll component, was so that I could track votes over time… I did this by adding a ‘dateVoted’ column to my vote table, and then using an “On Insert” trigger to set that column to the current date/time…. peachy keen, except when I exported the tables and stored procs to my live SQL box, I forgot to include my triggers… so all the votes on my previous poll have no date/time info. Yeesh. Oh well, not a big deal since I’m still in the testing/building stage, but still a very silly mistake.

I’ve put up a new poll though… I have a lot of article ideas for my Coding 4 Fun columns and I thought I’d ask people what topic they would like to see sooner rather than later…


Comments (9)

  1. David Hayden says:

    Hey Duncan,

    I am curious as to why you are using a trigger as opposed to just setting the ‘dateVoted’ column in code? On a lot of my tables I often have a ‘CreateDate’ field with the date the row was added to the table and a ‘ModifyDate’ when the row was last updated. I normally just populate these fields via code as opposed to a trigger, but I always wondered what I would gain if I used a trigger. Assuming the application I created is the only way rows are added and modified, does a trigger help me in any way?

    BTW, why do you mention "Tea" in the name of your blog? 🙂 Are you a fan of tea, the drink?

  2. David… I prefer to use triggers for the date created and date modified because, even if your code is supposed to be the only way to edit the data, people often end up using new tools and/or things like SQL Enterprise Manager…

    oh, and yeah… big fan of the tea 🙂

  3. Sonu Kapoor says:

    Mistakes happens. After all we are just humans 🙂

    BTW: I love tea too especially indian tea.

  4. David Hayden says:

    Thanks for the answer, Duncan!

    I will use triggers on my personal project to get a feel for the difference in functionality and maintenance. My customers are not technically savvy enough to modify the data outside of the web application, but I agree that ideally these "rules" should be in the database itself.

    I am a big fan of tea as well. I drink green tea daily and am enjoying it so much I told my wife we have to start an online store by year’s end 🙂

  5. Hi Duncan,

    How about just setting the default value for the dateVoted column to getDate()? As good as a trigger (albeit the value can be overwritten when accessnig the table directly), but will be included when you export/script the table…

    P.S. I’m an ex-tea drinker!!

  6. DeanG says:

    Where is the Question for the latest poll displayed at?

  7. Kent Sharkey says:

    An example of D’s tea fetish.

    I just went into his "supplier’s" shop in Winnipeg, and after ordering 2# of his brew of choice, I was asked, "Is this for … Mackenzie?"

    …Where everyone knows your name…

  8. Feroz says:

    Hi Duncan:

    I agree with Michael Falconer. Is there any specific reason to choose trigger over default value?