April 8, 2009 at 9:45 am
Hi,
I have the following table
CREATE TABLE tblUser
(
UserID INT IDENTITY(1, 1)
,FName VARCHAR(50)
)
I have created a delete trigger on this table because I would like to store the deleted users in a history table for future reference. I cannot come to a decision on the best schema for the history table.
CREATE TABLE xtblUser
(
UserID INT
,FName VARCHAR(50)
)
CREATE TABLE xtblUser
(
UserID INT IDENTITY(1, 1)
,FName VARCHAR(50)
)
CREATE TABLE xtblUser
(
xUserID INT IDENTITY(1, 1)
,UserID INT
,FName VARCHAR(50)
)
Any help with history tables would be appreciated.
April 8, 2009 at 10:32 am
My preference, and I emphasis preference, is this:
CREATE TABLE dbo.User_Log
(
User_Log_Id INT IDENTITY(1,1) PRIMARY KEY,
UserId INT NOT NULL,
Fname VARCHAR(50) NOT NULL,
Action CHAR(1) CONSTRAINT CK_Action CHECK (ACTION IN ('U', 'D')),
Action_Date SMALLDATETIME Constraint DF_Action_Date Default (getdate())
)
Or this
CREATE TABLE dbo.User_Log
(
UserId INT NOT NULL,
Fname VARCHAR(50) NOT NULL,
Action CHAR(1) CONSTRAINT CK_Action CHECK (ACTION IN ('U', 'D')),
Action_Date SMALLDATETIME Constraint DF_Action_Date Default (getdate())
CONSTRAINT PK_User_Log PRIMARY KEY NONCLUSTERED (UserId, ACTION, Action_Date)
)
I know you just mentioned logging deletes, but if the data can be updated you may want to log that in the future.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 14, 2009 at 3:16 am
Hi Jack,
Both your examples would work well in this case, the first table schema you presented seems good for me, it will track what action as well as when it happened for the data. I will also be logging for updates. Thanks for the help.
April 23, 2009 at 6:49 am
Because of my auditing needs I have a structure that is something like the following
UserID
VersionNumber
UserName
... additional fields to identify who did what and when.
The primary key is across the first two fields. The table becomes its own audit source.
Any attempt to update a record results in a new version.
We steered clear of having separate history tables because over time we ended up with thousands of them and any alteration to the schema of the main table required alterations to multiple triggers and the history table.
We added views that exposed the last version of any record.
April 24, 2009 at 3:10 am
You might want to take a look at table partitioning.
April 30, 2009 at 9:55 am
Partitioning might be appropiate for a time-driven periodic archiving scheme, but that is not what the OP is describing here.
[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]
April 30, 2009 at 9:59 am
I wrote a couple of articles on audit logging a while back. You might want to take a look at them, and (more importantly) at the discussions attached to them.
http://www.sqlservercentral.com/articles/Auditing/63247/
http://www.sqlservercentral.com/articles/Auditing/63248/
- 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 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply