March 23, 2010 at 8:49 am
Hello
I'm designing an application with business objects which has to be audited.
For example I have a customer table and an order table like follow:
Customer: ID, Name, Mobilenumber
Order: ID, IDCustomer, Amount, Product, OrderDate
The IDCustomer column of the order table refers to the ID of the customer table.
For both tables I created also an audit table with insert, update and delete triggers with all needed audit information.
My problem is to know the exact content of the referred customer row after inserting a new order. In this case the goal is to know which mobile number the customer had on each of his order.
I know I just can create a new column in the order table with the latest mobile number, but this example should show the problem with auditing entity data.
Unfortunately I always found who to do an audit trail on simple tables, but not on complex table structures which reflects a business entity.
Does anyone know a guide or solution or any ideas? I will be very thankful.
Cheers
March 24, 2010 at 7:47 am
SwissDeveloper (3/23/2010)this example should show the problem with auditing entity data.
How ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 24, 2010 at 11:26 am
Not sure I completely follow what you're asking, but let's see if this helps...
Inside of your Order table trigger, you could always do a lookup on the Customer table to retrieve the mobile number at that point in time. (Something like: Select @CurrentMobileNumber = MobileNumber from Customer where CustomerID = Inserted.CustomerID). Then, you could add that to the audit table (and to the insert statement for your audit table) for the inserted order.
Making insert/update triggers do a lot of work is not highly recommended, however, as it can slow down or impede your inserts and updates. Also, you'll have to be very sure you handle any potential errors so as not to mess up the insert/update if there's a problem retrieving the MobileNumber.
Does that help at all?
Rob Schripsema
Propack, Inc.
March 25, 2010 at 1:34 am
Hi Rob Schripsema
Thank you for you replay. I also thought about the procedure which you described and just wondered if there might be an other way to do it.
I will keep triggers very slim and use stored procedures to do the work.
Cheers
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply