I have developed a scheme to Deactivate my objects as opposed to deleting them from the database. When I first started asking questions about this concept, a couple people said they were thinking about this too. The solution is not overly complex but it is not short either. So before I post it up here and waste my time, I want to know if anyone is still interested?
P.S. I have also worked through the sub-classing of the framework to ease future upgrades of the framework while still keeping the deactivate functionality.
I know I posted about this at some point.
I have been using a bool / bit flag and my main app logic is that a user's "delete" is a sql update that marks the active flag to false.
my fetch procs for normal use assume the rows to fetch are active=true
I will be adding some "management" functions that select where active=false and can then act on that to update the active flag if that's a valid action. and some kind of "Pack" / "Cleanup" function that can walk the tables and remove non-active records where that will not break things.
in my view there are concepts like "Closed" / "Open" that also come into play.
for example you may view Invoices, All, Open,Closed or ???
Open invoices are not paid and not cancled.
Cancel / Void are "closing actions" also and mark the row with datetime,who did it and why.
so some things in my view "can be deleted" at some point but then can not and must be closed or voided.
for example a payment on an invoice means that the invoice, it's details and the payment are all now non-deleteable objects. but if the invoice was paid in full then it's marked as "closed" etc...
I think we could generalize that kind of thinking a bit and perhaps have a CSLA addon / extension ??
In my framework, Delete still deletes, but I added two methods Deactivate and Reactivate. They go through the <object>.save()/DataPortal_Update/Update Stored Procedure path.
In the Update Stored Procedure there is a variable used to indicate "ActiveStatusNoChange", "Deactivated" or "Reactivated". At the end of the update function it will kick off another stored procedure if this variable is either "Deactivated" or "Reactivated"
In addition to the IsActive bit field in the SQL server, I also have ReactivatedPersion, ReactivatedDate, DeactivatedPerson and DeactivatedDate. The only reason I mention this is because there is a scenario where a contact has 15 previeouly deactivated addresses and the user accidentally reactivates the contact, thus also deactivating the final address. So you want to programatically reactivate the contact and address (and whatever other info) that was deactivated by accident. My Reactivated Stored Procedure goes through the addresses related to this contact and looks at the DeactivatedDate and DeactivatedPerson and only reactivates the ones who match the same fields in the contact.
Like you said, I added functionality to the business objects and stored procedures to retrieve "ActiveOnly", "InActiveOnly" and "AllRecords".
My original version of this made for hacking the framework because it added Reactivate and Deactivate channels in the framework which ruled out subclassing to a major degree. Because my final version uses the Update route through the framework, which is already existant, it is completely subclassing friendly.
Well, I guess that wasn't so long after all. Honestly though my original thought was to post example code as I had been asked to to this previously, but if you get all this, I think you can work through it no problem.
ajj3085:
Just to be clear; your end result was to add a Deactive / Reactivate method to your class, which modified a state variable? Then in your update you set that flag? Sounds like a great solution to me.
Yea, the objects also have a state variable identifying a change in the Active state. It holds a values of:
1. ActivationStatusNotChanged
2. Reactivated
3. Deactivated
This variable is passed to the update stored procedure. After the update is completed, it looks at the value passed to it. Based on that it kicks off one of the other two stored procedures designed to Reactivate or Deactivate the object, or it just finishes the stored procedure.
By the way, the variable that identifies the state is not the same as the field in the database (or property on the object) IsActive. IsActive identifies the current state of the object. The variable mentioned above identifies that the object is changing state and what it is changing too.
I think that is the same thing you said. :)
I'd be curious to see your solution.
I would like to ask though; why didn't you take the route of having your BOs 'interperate' delete as inactivate, if the BO truely can never be deleted, which in my experience is typically the case? Obviously if you do allow the data to be deleted at some point then this won't work.
The answer is because there are times when you actually DO want to delete an object. As an example.
A user has the ability to modify addresses for a contact. For the user, when they hit DELETE, it is actually Deactivating the object. So the user types in a new address. She deletes the old address, but enters a new address that is obviously screwed up. She doesn't catch her mistake until tomorrow which is after this has already been save. So she deletes the screwed up address (which is deactivated) and adds the correct one.
Then I come along and I have my object set to show all records, regardless of their Active Status. I notice the record that she (I picked a she because in our company the data entry is done by a woman) screwed up and it has an obvious mistake. So I click on Delete, but I want it to REALLY delete it. I don't want it to try to deactivate it.
So I can use the same objects as the user to perform maintenance. This is just one example and I haven't sat and thought of everyone one, but it seems much better to Add the Reactivate/Deactivate functionality than to replace the Delete functionality and create an Undelete functionality. Which ultimately would have me doing half of the same thing I already am doing.
Another added benefit is that I now have a pattern for adding functionality to my BOs. Now I can do Open/Closed functions on invoice objects for example. I can also use this pattern to add process flow to my objects.
I've playing with the idea of keeping a full log: who made changes and when.
The solution I came up was:
- have extra userID and timeStamp fields on every row;
- have a log database - a different database with the same data structure but no indexes (maybe SQLite on the application server);
- copy the old version of every changed row to the log database.
The row delete question is: if a row is deleted, it should not exist on the main database but the log database should know it was deleted, when and by whom.
When deleting a row, one should take these steps:
1) copy the old version to the log database (just like when updating)
2) delete the row on the main database
3) take the old version, mark it deleted, time stamp it and write it to the log database
Step 3) above is a bit out of sync with the global working logic. Maybe the right thing to do is to skip step 3) and keep the deleted version on the main database until undelete(?) or cleanDeadObjects(?)
On the later case, it would be time to take step 3).
The point is: why do I need to keep on the main database information of deleted objects? I think this only makes sense if I allow undelete. Otherwise there is no point in delaying stpe 3).
just a few things to think about:
if you do that for every row and table you can have quite a lot of old data and often duplicate data.
one method I have used to do tracking is like this:
have a single table to track changes.
have each table that needs tracing define triggers for update and delete
the trigger fires and evaluates what to store in the "audit table"
so the outside app has no control over the audit function and thus any outside app can not "subvert" it.
the table has
ID(int,PK), creationTimeStamp(datetime,default(GetDate()),TableName,ColumnName,Old,New,WhoChanged,OldWhoChanged
that table format alows the capture of chnages on a single column in a table and the "before,after" status. allows the tigger to store any number of items, but does not require that you duplicate all the content of a row.
and you can determine who to "Age" or "Purge" that history, never for some items, every week or so for other items or whatever you need to do for example to meet hippa, sarbanes-oxley, Goverment specs etc...
and often I find that say 75% of a row is not needed, that perhaps 3 or 4 things need tracking. In that case this model keeps your audit trail data size way down. If you "over do it" you can have way to much junk. I had a system I had to export data from one time that had 25,000 accounts but the accounts table had around 75,000 rows !
What I found was that they kept the audit in the table with a dual part key.... what a mess to get a record you had to use a select like this:
Select *
From Accounts
Where ID=1234 and Edits=(Select Max(Edits) From Accounts Where ID=1234)
talk about a mess.... that I think was part of why the client left that system, it got very slow .... simple account edit / read screens took a LONG time to load!
the same was done one sveral tables that all had to be read to talk to a customer. and this was running on a SUN box with Oracle 7.1 (at that time it was the current version) but I found later that sun box had only 256 megs of ram and was also tasked with serving an office with about 16 users!
so ok maby that also made it too slow ![Smile [:)]](/emoticons/emotion-1.gif)
I have set up a generic trigger to do something simliar to this.
http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk
I have set up a generic trigger to do something simliar to this.
http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk
Hmmm... Nice, I'll have to look at that one and see if I want to add it to my "times when SQL CLR is good" list. So far I only have 2 or 3 times when I found it was the right thing, this sounds like a new one that makes sence to me.
must tear into that soon...
Copyright (c) Marimer LLC