MS SQL 2005 Trigger : Transaction Scope and Parameters

[wuz]

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!

[Ramy Mahrous]

Is there an easy way to tell the trigger: If the action which causes you is rolled back -> role back you action?

I think you determine that in the trigger you fire it after or before committing the action. read in triggers at MSDN.com

second issue you can pass any values to trigger rather you should maintain the DB design to save who does the transation

[wuz]

Thx, but

1) accessing the trigger sounds good, but cannot find any best practices in combination with transaction scope

2) "you can pass"
you mean I cannot pass values to a trigger right? Sounds reasonable, but how can i achieve that the trigger inserts a certiain history comment.

thx!

[Ramy Mahrous]

1- I don't know really what happen if .NET SQLTransaction rolls back, but you can also develop a trigger to keep track on deleted items from Father, Mother, Brother tables. (I think this solution is a bit nice)
2- You can have a temp table in your database called current user, holds the user currently run the application and then whenever your trigger triggered get the value from this table.
P.S I expect just one user running this application, tell me more about that. To get optimal solution.