Logging INSERTS, UPDATES and DELETES on a database

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Logging INSERTS, UPDATES and DELETES on a database

Author

Topic

steppinthrax
Starting Member

27 Posts

Posted - 2010-02-10 : 22:59:10

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

52326 Posts

Posted - 2010-02-11 : 00:17:38

why do you need a duplicate database? Cant you use your main db itself?

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

steppinthrax
Starting Member

27 Posts

Posted - 2010-02-11 : 07:50:48

explain?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 08:01:34

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/

steppinthrax
Starting Member

27 Posts

Posted - 2010-02-11 : 08:06:05

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 08:13:06

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/

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 09:05:48

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.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-02-11 : 10:26:07

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

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 12:32:23

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources