OT; Triggers and Transactions

cash_pat posted on Saturday, October 14, 2006

Hi,

I Have 3 Tables
          Parent
              Child
         
          Balance

Now in DataPortal_Insert, the Parent BO is updated, then the Child BOs are updated. The Child BO Insert Stored Procedure starts a trigger which references a Column from the Parent Table to update the Balance table.

I am getting run time errors from SQL on saving my BO. Could someone tell me

1) has the parent table been updated or the row is temporarily stored somewhere till the transaction commits.
2) How can i get the value from the parent table in the child table's trigger

regards

Pat cash

Brian Criswell replied on Saturday, October 14, 2006

1) Anything that happens under the auspices of a transaction will be rolled back if a transaction finishes without being committed.
2) If you are talking about SQL Server, the Inserted table will give you the values that have been added/updated.

cash_pat replied on Saturday, October 14, 2006

How can a child tables' trigger access rows from the parent table's insert . I mean the inserted in the chlild table's trigger would only contain columns from the child table.

guyroch replied on Saturday, October 14, 2006

Are you using the attribute [Transactional(TransactionScope)]  or [Transactional(Manual)].

If you are using "Manual", then you have to _start_ your transaction yourself and because your are updating then you need to make sure that it is started with the IsolationLevel.Serializable AND NOT IsolationLevel.ReadCommitted.

If you use either TransactionScope OR IsolationLevel.Serializable  then ALL of you update/insert, including triggers, will be part of your db transaction and you should not have any problems.

Would help if you can include the error your are having.