Hi, I'm running into a situation:
Code:
trx = session.BeginTransaction()
acc = session.Load(GetType(FundAcc), acc_id)
acc.LastUpdate = DateTime.Now
sess.Update(acc)
sess.Flush() 'acc Locked
request = session.Load(GetType(FundRequest), request_id)
request.LastUpdate = DateTime.Now
sess.Update(request)
sess.Flush() 'request locked
STEP 1: check and validate acc has sufficient funds
If (acc.Balance >= request.Amount) Then
STEP 2: update request status
request.Status = Status.APPROVED
sess.Update(request)
sess.Flush()
STEP 3: approve request - update acc balance
acc.Balance = acc.Balance - request.Amount
sess.Update(acc)
sess.Flush()
End If
trx.Commit()
This works fine. But one of our DBA insisted on
STEP 3 being done by UPDATE trigger - and the code became:
Code:
trx = session.BeginTransaction()
acc = session.Load(GetType(FundAcc), acc_id)
acc.LastUpdate = DateTime.Now
sess.Update(acc)
sess.Flush() 'acc Locked
request = session.Load(GetType(FundRequest), request_id)
request.LastUpdate = DateTime.Now
sess.Update(request)
sess.Flush() 'request locked
STEP 1: check and validate acc has sufficient funds
If (acc.Balance >= request.Amount) Then
STEP 2: update request status
request.Status = Status.APPROVED
sess.Update(request)
sess.Flush()
End If
trx.Commit()
The problem is, the trigger is trying to update
"acc", which is locked during the duration of the transaction. Is there anyway to get around this? Or is UPDATE TRIGGER a bad idea (TRIGGER isn't executed in the same transaction context)? What I found is that the UPDATE TRIGGER was just never fired.
I assume it was never fired because it's outside the transaction and was therefore blocked - correct me if I'm wrong. Under what context is TRIGGER being executed? However, I'm seeing conflicting information:
A trigger is part of the transaction started by the statement that fired it. Thus, if the trigger fails the firing statement fails and is rolled back.
If this is true, why the hell my UPDATE TRIGGER never updated "acc"??
Reference:
http://www.dotnetjohn.com/articles.aspx?articleid=133
which lead me to thinking:
1. are triggers fired on Flush? Or Commit?
2. When Flushed (but not yet committed), updates propagated to database, yet?
Thanks in advance.
Login
FAQ
Search