Geek Answers Handbook

Audit logging for objects divided into several tables

We have an entity divided into 5 different tables. Entries in 3 of these tables are required. Entries in the other two tables are optional (based on the subtype of the object).

One of the tables is assigned by the entity master. Entries in four other tables are entered using a unique identifier from the wizard.

After starting the update / deletion, the trigger is present in each table, and changing the record saves the history (from the deleted table inside the trigger) to the corresponding history table. Each history table contains associated object fields + timestamps.

So, live records are always in live tables, and history / changes are in history tables. Historical records can be ordered based on the timestamp column. Obviously, timestamp columns are not related between history tables.

Now, for the more difficult part.

  • Entries are first inserted into one transaction. Either 3 or 5 records will be recorded in one transaction.
  • Individual updates can occur with any or all of the 5 tables.
  • All records are updated as part of a single transaction. Again, 3 or 5 records will be updated in one transaction.
  • The number 2 can be repeated several times.
  • The number 3 can be repeated several times.

It is assumed that the application should display a list of points in time history records based on records recorded only as separate transactions (only points 1,3 and 5)

, timestamp.

HISTORYMASTER , , . HISTORYMASTER . HISTORYMASTER.ID . , HISTORYMASTER.ID( )

(UPDATE, DELETE) TRIGGER , ?

+3
3

HistoryMaster , . , , . , .

EntityAudit ( "" ), EntityHistory, . -, . , , . , ( ), , , , .

( , , , - ), "", . , , , , , (, , ). , . .

, Audit/Master , History : AuditID ( Guid, autonumbers ).

+1

TimeStamp/RowVersion ?

"" , TimeStamp/RowVersion : (

GUID, , .

, , , , 1:1 Master Entity, Master Entity ID - TimeSTamp/RowVersion, GUID, incremented - .

0

, "" - .

-. , . / , , ( ) .

I understand that you asked how to do this in database triggers. I do not know about SQL Server, but in Oracle you can overcome this by using the DBMS_TRANSACTION.LOCAL_TRANSACTION_ID system package to return the identifier of the current transaction. If you can get the equivalent SQLServer value, you can use this to bundle record updates for the current transaction together in a logical package.

0
source

More articles:


All Articles