SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Logging INSERTS, UPDATES and DELETES on a database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

steppinthrax
Starting Member

25 Posts

Posted - 02/10/2010 :  22:59:10  Show Profile  Reply with Quote
I have a database I made for an application. It has several tables. I want users to be able to update and delete data from the database (via the application). I want to know the best way this can be done. I'm thinking simply duplicating the schema and using a insert trigger to insert every row into the duplicate database on each table. However, I don't think this approach is the most efficient. For example, how would I manaage updates????? This database will likely end up on sql 2000

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/11/2010 :  00:17:38  Show Profile  Reply with Quote
why do you need a duplicate database? Cant you use your main db itself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

steppinthrax
Starting Member

25 Posts

Posted - 02/11/2010 :  07:50:48  Show Profile  Reply with Quote
explain?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/11/2010 :  08:01:34  Show Profile  Reply with Quote
quote:
Originally posted by steppinthrax

explain?


didnt understand purpose of extra db? is it for audit purpose alone?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

steppinthrax
Starting Member

25 Posts

Posted - 02/11/2010 :  08:06:05  Show Profile  Reply with Quote
I saw examples of making an Audit table that has old val and new val and insert/update/delete triggers. You said auditing can be done with the main db?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/11/2010 :  08:13:06  Show Profile  Reply with Quote
quote:
Originally posted by steppinthrax

I saw examples of making an Audit table that has old val and new val and insert/update/delete triggers. You said auditing can be done with the main db?


yup inside same db itself you can create an audit table for tracking of changes in main table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/11/2010 :  09:05:48  Show Profile  Reply with Quote
We have Audit Tables in our main database. (Separate database would do, but you have to be able to synchronise the two databases if/whenever you have to do a Restore, which is harder with two databases than with one).

Also, you probably cannot just have a duplicate schema - for example: we have two additional columns on each Audit table: [Action] (Update or Delete) and [Audit_DateTime]; also, a PK with IDENTITY in the Main Table needs to store that same value in the Audit table - i.e. that column cannot also have IDENTITY attribute in the audit table

We prefix all column names (in the Audit tables) with "A_" - so if the main table has a column "FooBar" the corresponding column in the Audit table is "A_FooBar".

We have no foreign keys, no defaults, etc. in Audit tables; We have a clustered index on the normal PK + Audit_DateTime (however, that is not always unique, so consider using an additional IDENTITY column in Audit table). When a column (in a table) is changed from NOT NULL to NULL we do NOT change the Audit table - i.e. we maintain the least stringent data validity requirements necessary to store the data -including the format/length of any, earlier, historical data.

We do NOT store the current data in the archive table (that is, after all, in the main table, so would just double our storage).

When a row is updated, or deleted, we store the "before" data in the Audit table.

We put a trigger on each table as follows:

CREATE TRIGGER dbo.MyTrigger
ON dbo.MyTable
AFTER UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON 
SET ARITHABORT ON 

	INSERT dbo.MyAuditTable
	SELECT	[AuditType] = CASE WHEN I.MyPK IS NULL THEN 'D' ELSE 'U' END,
		[AuditDateTime] = GetDate(),
		D.*
	FROM	deleted D
		LEFT OUTER JOIN inserted I
			 ON I.MyPK = D.MyPK
GO

Note that we store all columns when a row changes, rather than trying to only store Before/After for columns that have actually changed. This makes reporting much easier, and the Triggers much less complex (they are firing for every row changed, so important that they are efficient.)

We purge Audit tables deleting rows older than X days/months/years etc but ONLY where a newer audit record already exists - i.e. where an edit HAS been made to a record there will ALWAYS be at least one record in the AuditTable - even if older than the purge cutoff date


i]Edit:[/i] The latest code for this routine, including detailed discussion and experience gained over the years, is now the Scripts Forum: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215
That link also describes Reporting, Purging stale data, how to only "log" some of the columns in the table, and so on.

Edited by - Kristen on 01/14/2012 08:22:21
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 02/11/2010 :  10:26:07  Show Profile  Visit russell's Homepage  Reply with Quote
I usually add 5 columns to my auditing tables to capture: SYSTEM_USER, host_name(), APP_NAME(), user_name(), getdate()

If you're capturing some of that info on insert, then you only need to trigger update and delete

Edited by - russell on 02/11/2010 10:27:11
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/11/2010 :  12:32:23  Show Profile  Reply with Quote
Good point. We are storing that in the actual record (and thus that also gets transferred to the Audit table when the record is next updated, or deleted)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000