Hi all,
I was thinking of using Triggers to take care of my history tables. Two questions remain:
1) Transaction Scope
I handle complex Operations in my DataAccessLayer (C#,ASP.NET) with Transaction Scopes. For example:
private void InsertFamily(FamilyDTO)
{
using (TransactionScope scope = new TransactionScope())
{
try
{
InsertFather();
InsertMother();
InsertBrother();
}
catch (Exception ex)
{
logger.LogError(ex);
throw new Exception(ex.Message);
}
}
}The Methods InsertFather, InsertMother may have TransactionScopes as well. All the tables (Father, Mother, Brother) have an HistoryTable (Father_History, Mother_History, Brother_History) with Triggers on Insert, Update, Delete to coy the current antry to the History table and so offer the possibility to keep track of all changes.
Now let’s assume that InsertFather has already commited to the database (for example we need autoids to be generated), then the Father_History_Trigger would have been triggeered. Now InsertMother throws the Exceptio and due to TransactionScope the Father-insert action will be roled back, but what happened to the History entry made by the trigger.
Is there an easy way to tell the trigger: If the action which causes you is rolled back -> role back you action?
2) Passing Parameters to the Trigger
There is only a few information which is not accessable from the last inserted/updated/deleted record like a HistoryComment or a PersonID who performed the CRUD Operation. So What are recommended approaches to pass thes information from my DAL tot he trigger?
These are two crucial points to use triggers for my history table otherwise i will do it with my generic crud functions in the dal.
Big Thx!
