Post reply

Row Modified timestamp

  • December 28, 2010 at 11:18 am

    #244809

    I'm creating an "audit mechanism". Where I can track changes to data that interests me. I created a "ChangeLog" table like so:

    CREATE TABLE [dbo].[ChangeLog](

    [LogItemId] [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL,

    [AppName] [varchar](15) NOT NULL,

    [TableName] [varchar](128) NOT NULL,

    [ColName] [varchar](64) NOT NULL,

    [OldVal] [varchar](8000) NULL,

    [NewVal] [varchar](8000) NULL,

    [ChangeDateTime] [datetime] NOT NULL,

    )

    My thinking here is that for any data that I'm interested in, I'm going to create a trigger on that table that inserts a record in the ChangeLog table which identifies the old value, the new value, who changed it, and when it changed for any columns I'm interested in. So I have a couple questions here:

    ** First of all, does this seem reasonable? Or is there some SQL Server feature I should be looking at instead?

    ** Second, is an "instead of" trigger what I'm looking for here?

    Thanks!

    .

  • December 28, 2010 at 11:24 am

    #1266844

    Hi

    Have a look at CDC[Change Data Capture]

    http://msdn.microsoft.com/en-us/library/bb522489.aspx

    This will help u out ,which is added new feature in SQL Server 2008

    Thanks

    Parthi

    Thanks
    Parthi

  • December 28, 2010 at 11:32 am

    #1266849

    I wrote a couple of articles on that subject. They're here: http://www.sqlservercentral.com/articles/Auditing/63247/ and http://www.sqlservercentral.com/articles/Auditing/63248/.

    If you read them, make sure to read the discussions as well. There's a lot of good data in there.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • December 28, 2010 at 11:33 am

    #1266850

    That is EXACTLY what I'm looking for. Unfortunately, right off the bat it says only available on Enterprise, Developer, and Eval. I should have mentioned that this is for SQL Azure. In fact, now that I think about that, I should have posted this in the Azure forum.

    I wonder if Azure supports CDC. My guess is that it doesn't, not yet anyway. I'll go verify that for sure. That's too bad, that's a great feature that I've always needed throughout my career.

    .

  • December 28, 2010 at 11:46 am

    #1266855

    Keep in mind that CDC locks down table definitions the same way replication does. This means you cannot alter or remove columns without turning off CDC in some cases. That can be worked around/with, but it's something to keep in mind.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • December 28, 2010 at 12:21 pm

    #1266876

    Thanks GSquared, but CDC not available on Azure, so that's off the table for me. I'm going to have to ponder Crazy 8's discussions, and derive a solution from there. --Thanks Crazy 8!!

    I sure do love the concept of SQL Azure, and I think it will put MS head and shoulders above the rest of the cloud providers out there if they can pull it off, but there is SO MUCH missing. I've been working with if for quite a while now, and there are roadblocks around every stinking turn. IMHO, SQL Azure is NOT ready for production / enterprise level use yet. Right now, about the only real thing it has going for it vs. Tables/Blobs is the relational bit. That's a biggie alright, but at this point, I think I'm going to have to start pushing the company away from SQL Azure for a while.

    But I digress... 🙂

    .

  • December 28, 2010 at 12:43 pm

    #1266891

    Not sure who you mean by "Crazy 8", but I'm going to hazard a guess you mean me, since that's my current "title" for this page. (It says, "SSCrazy Eights" under my avatar because of the number of posts I've made on this site.)

    SQL Azure is really meant for small shops that don't want their own server farm or to rent a dedicated server from a provider. It's not meant to be a reasonable substitute for Enterprise or even Standard Edition, it's more meant to be a completely different solution entirely, and is more aimed at the businesses that are somewhere above using Express, but not up to using Standard yet. Even so, they're not really meant to be different flavors of the same product.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • December 28, 2010 at 2:38 pm

    #1266936

    Got it GSquared. Sorry my fault. I don't see the original post when I write my reply and Crazy 8 just stuck in my head. 🙂

    I completely agree with you assessment of SQL Azure. I believe that MS is trying to position SQL Azure as an enterprise class solution, and I believe it will be some day, but it's not there yet. In the mean time, budget mined Project Managers are pushing us Developers toward SQL Azure in situations where it's just not appropriate yet.

    Thanks for the articles. All great stuff. I will spend the rest of the afternoon trying to mix/match the strategies you suggest.

    .

  • December 29, 2010 at 7:28 am

    #1267157

    Makes sense.

    Let us know if you have any questions.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 9 posts - 1 through 8 (of 8 total)

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