working with Triggers

nfung · **Joined:** Mon May 30, 2005 11:20 pm **Posts:** 66

Hi, I'm working with a DBA on a project. During the course of the project he suggested that updating "Status" field of "ItemRequest" entity would fire a trigger, which in turn, perform further updates (ie. calculate balances... etc).

Code:

Class ItemRequest
   ... other stuff ...
   Public Property Status As Int16
     ...
   End Property
   ... other stuff ...
End Class

In client code:

Code:

Protected Sub UpdateRequestStatus()
Dim req As ItemRequest
Dim sess As ISession
Dim trx As ITransaction

sess = GetSession()
trx = sess.BeginTransation()
req = GetRequest()
req.RequestedAmount = 12345
req.Status = COMMIT_REQ
sess.Update(req) 'Update database -- in turn fires trigger
trx.Commit()
End Sub

Thing is, we need to rollback transaction if "acc balance" < 12345, then inform user "Insufficient funds in account".

How can I get this information from trigger?? How should this be implemented...?? I was thinking (hopefully not), that the only course available is RAISERROR() from within my TSQL trigger...

Code:

Protected Sub UpdateRequestStatus()
Dim req As ItemRequest
Dim sess As ISession
Dim trx As ITransaction

sess = GetSession()
trx = sess.BeginTransation()
req = GetRequest()
req.RequestedAmount = 12345
req.Status = COMMIT_REQ

Try
sess.Update(req) 'Update database -- in turn fires trigger
trx.Commit()
Catch ex As SqlException '[b]RAISERROR[/b] from within [b]TSQL [/b]trigger
  'If insufficient balance, inform user...
End Try

End Sub

Any pointer?? Thanks in advance, any suggestion would be greatly appreciated!

martijnb · **Joined:** Sat May 14, 2005 8:40 am **Posts:** 130

Putting business logic in triggers like this doesn't work very well with a more OO approach. You'll have the business logic scattered all over the place, not to mention the problems you described with raising exceptions.

I will strongly advice to not put any business logic like this in triggers, but let your business objects take care of this. That's what they are meant for.

nfung · **Joined:** Mon May 30, 2005 11:20 pm **Posts:** 66

Yes, the dilema is, the triggers has already been implemented and there's not enuf time to rewrite the logic in application layer. :(

RAISERROR and SqlException is slow -- but, since it's not done over a thousand times, this is acceptable penalty... although the setup and syntax would look ugly... I mean, trying to catch an exception for something that'd happen all the time...

What I was hoping however is, is there any way to salvage this without:
1. RAISERROR
or
2. rewrite code in application layer.

Thanks anyway.