We have some tables in our DB that manage history inside the table by only doing Inserts, adding an effective end date.

To do an 'Update' we actually enddate the existing record to Now and then insert a new record with the new data.  Were also using stored procs exclusivly for our CRUD.

Our table would look something like this:

-----------------
ProductPrimaryID
ProductID
ProductName
EffectiveEndDate
-----------------

The ProductPrimaryID would be unique for each record in the DB, and the ProductID would persist for each 'Updated' record.  So given all this what's the best way to approach this via SF?  An update in SF only returns an number for successful rows updated, I need to have it return the new ProductPrimaryID.  I thought of calling my Update proc with save() and then refilling my BO with the newly added record.  It's an extra trip to the DB but I think it should work.

Anyone else have any ideas or already have a more elegant way to fix this?

Thanks

I think using sprocs for CRUD would be the way to go. The sproc can handle the logic to add the row during update, there would be no extra trips to the db (btw, SF does an extra one to update the data during updates anyway...I think). There is a topic in help to show you the signature of the sproc.

We are already using sprocs for all the CRUD.  my real issue is that the signature for the Update sproc in SF only returns a rowcount, not a keyID.  This makes sense since why would SF expect the keyid to change in an Update.

So if call my Update sproc I'll then have a situation where my BO will have the correct data with an incorrect keyID.  Like this:

Table:
KeyID, ProductID, Name, Color, current

record 1
1, 1001, SomeThing, Blue, true

Fill my ProductBO as follows:
MyProduct.FillByPrimaryKey(1);

Now I change the MyProduct BO to this (the sproc manages the "current" part):
1, 1001, SomeThing2, Blue2

I call my Update sproc with MyProduct.Save(); 

Now the DB looks like:
1, 1001, SomeThing, Blue, false
2, 1001, SomeThing2, Blue2, true

This updates the data in the DB as I needed but leaves my BO with a bad KeyID (1 when it needs to be 2).  I havent implemented it yet but I am planning on refreshing the BO after an Update to get the new real record.  This seems inelegant and to easy to screw up so I was hoping someone else had a better strategy.

Thanks!

The first option would be to do what you mentioned about refreshing the record when you send an update.  In the OnBeforeSave(), save off the record(s) that need to be refreshed and then on the OnAfterSave() retrieve those records. 

The other option would be to create your own DbDataSourceItem class that would be used by just the business object(s) that need this functionality.  You would inherit the OracleDataSourceItem class and override the CreateDbCommand() and UpdateRow() methods.  On both of them, if the query is anything other than an UPDATE, just pass the command down to the base class, otherwise, handle it as needed.  You can use the DataRowState.Modified portion of the UpdateRow() method as a template.

Thanks Ben,



That was exactly what I was looking for.