Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2008-04-14 : 16:00:05
|
| Currently all of our tables in several databases have the following columns:user_added (this is nvarchar)host_added (this is nvarchar)date_added (this is datetime)user_modified (this is nvarchar)host_modified (this is nvarchar)date_modified (this is datetime)Right now our policy is that (a) the _added columns use defaults to populate the data on INSERTS and triggers are generated to update the _modified fields upon an UPDATE of the table.Our practice has been (a) to manually create these fields in our scripts as we create new tables in our system and (b) create triggers to perform the update anytime we create a new table.This practice has been fine until recently where we have been outsourcing some of our development and not all of our standards have been adhered to, including this one. I'd like to look at alternatives for somehow maintaining these concepts outside of our development workflows.The first thing I'd like to inquire about is regarding options to eliminate having developers include these columns in the CREATE TABLE statements. Is it possible in SQL Server 2005 to capture when an CREATE TABLE statement is executed and override/append to the initial CREATE TABLE statement?The second thing I'd like to inquire about is regarding options to eliminate having developers write the initial trigger that maintains the _modified fields. I guess if there are options to capture when a CREATE TABLE statement is executed, we could possibly generate a CREATE TRIGGER statement against that object as well?Another idea I would like thoughts on are using some sort of 'table inheritence' to store this information for all objects in our database? This idea come up when I saw this article - http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server. Do you think the situation I explained here would fall into this concept?I'm also open to any other thoughts and/suggestions. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-14 : 16:18:05
|
| You can do everything you suggest but I would run a script that checks every table in the database for these columns and the triggers. If they don't exist then you flag it as an issue or can add them as part of the script.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-04-15 : 11:58:09
|
| nr,Thanks for the info. We currently do have a script that runs nightly to report these issues (and will be cleaned up). The goal though is to prevent the situations from occurring in the first place. I'll toy with capturing the event data in a global trigger to capture CREATE TABLE statements. From what I can tell (read), the event data is just an XML document which I should be able to capure, manipulate and push back into the event data.Any other thoughts anyone? |
|
|
|
|
|
|
Suggestions on maintaining audit fields