designing tables to track data changes

[netw3rker]

hi all, i'm trying to set up a system where data isnt deleted in certain tables in the database (i know.. visions of lotus notes are popping through your heads ... )

here's an example of why:

tblUsers (userID, firstname, lastname, DOB, username, password, enabled, dteModified)

i'd like to set a trigger up so that whenever an update statement is run against the table, rather than have it update the data, it inserts a new record with 'updated' data, and disabled the old record.

this works well for things like password uniqueness tracking.. because i'll have a simple lookup of all that users changes to check against.

my other option was to create a seperate table that stores every password and relates back to the user that used it. but that then requires new modules of code to be written, and, what happens when we decide that now other information about the user needs to be kept track of... new tables? bad idea...

similarly if the user is constantly changing their other information for example address details, i need to always know what the address was at the time that information was used.

the best i can work out is a change like this (assuming that i can find out the command to stop the update in the update trigger):

tblUsers (userID, userUID, firstname, lastname, DOB, username, password, enabled, dteModified)

where userID is the ID refrenced by all other tables that dont care about the user information at the time, and userUID is the ID refrenced by tables that do.

now, when establishing the primary key for this i'm running into trouble because i need both fileds as a composite key. but when i do that, i'm forced in every related table to have userID and userUID in there. technically this doesnt stop me from doing what i want, but it does add an element of 'usless' data to my database that i'm trying to avoid.

any thoughts or suggestions on this?
thanks!
-Chris

 

[SSesham]

just my thoughts:

if you are looking for user history, creating the table with UID column i think is good. you can avoid the primary key problem by choosing to create a unique index on columns(userID, userUID, enabled)

If its just to do with accessing data one level up
you can choose to create a couple of new columns 'lastpassword' or 'lastaddress' etc., and update the latest record with last latest details into these columns..

 

[netw3rker]

ya i definately want it set up so that in the case of passwords, the same password cant be used 2x. or at least a user specified amount (for a group policy). Adding a 'lastPassword' column would become an issue as the various 'policy makers' in the system would have different #'s to use. so at some point i'd have to draw the line when doing
'lastPassword', 'lastLastPassword', 'lastLastLastPassword' ect ect ect would wind up being a prob

any ideas on the 2nd part which would be allowing the programmers to write update statements to the table, but having the table convert the update to an insert and canceling the actual change?

-Chris

 

[netw3rker]

well, for anyone who was following this thread, the triger format i was looking for is:

create trigger triggername on tablename
instead of update
as
begin

update tablename
set enabled=0
from tablename inner join inserted on tablename.[idfield]=inserted.[idfield]

insert into tablename ([all fields except identity field])
select ([all fields except identity field])
from inserted

end


this lets developers script update statements like they are comfortable with, and the db rather than actually update the record, creates a new record with the updated information. the only criteria here that the developer needs to follow is that there will always be 1 record with 'enabled' set to true.

the next step is to create an 'INSTEAD OF DELETE' trigger that just sets enable to 0 across the entire ID's change history.

thanks for everyone's help on this'n and hope this solution helps others out.
-Chris