I recently read the MSDN article Add a Recycle Bin to Windows SharePoint Services for Easy Document Recovery by Maxim V. Karpov and Eric Schoonover. It was an interesting read, but I was pretty amazed at the lengths they had to go to in order to get something as simple as a recycle bin. Obviously, this biggest setback was the fact that
events are processed asynchronously. As a result, a registered event sink will only be notified about the document deletion after that document has already been deleted from the SQL Server™ backend database. As a result, the event sink can't simply copy the document to the recycle bin library because the deleted document no longer exists.
Sounds like a YASPQ (or YASQ if you prefer) to me. So in order to create the recycle bin they Maxim and Eric end up mirroring the document libraries in order to add the recycle bin functionality. While reading the article my mind couldn't help of trying to come up with a simplier method. Here is my own version of a SharePoint recycle bin (note: don't play around with your SharePoint databases if you don't know what you're getting yourself into. I always build and test my scripts out on test servers and sites and this is what I would call a pre-alpha release).
Connect to the _SITE database using Query Analyzer and run the following SQL Scripts.
1) Create the RecycledDocs table which is basically a copy of the Docs table:
-- create the RecycledDocs table select * into RecycledDocs from Docs where 1 = 0
2) Create an instead of trigger on the docs table that will redirects requests to delete documents.
create trigger doc_recycle on docs instead of delete as delete RecycledDocs where id in (select id from deleted) insert into RecycledDocs select id, siteid, dirname, leafname, webid, listid, doclibrowid, type, size, metainfosize, version, uiversion, dirty, cacheparseid, docflags, thicketflag, charset, timecreated, timelastmodified, nexttolasttimemodified, metainfotimelastmodified, timelastwritten, setuppath, checkoutuserid, checkoutdate, checkoutexpires, checkoutsize, versioncreatedsincestcheckout, ltcheckoutuserid, virusvendorid, virusstatus, virusinfo, metainfo, content, checkoutcontent from Docs where type = 0 and Id in (select Id from deleted) delete docs where id in (select id from deleted)
That is basically it. Of course, there is a lot of functionality that needs to be added from a user perspective, but as an admin you can now restore any document that gets deleted.
I'm not sure how this would affect Microsoft's support of a SharePoint installation, it may or may not. Your RecycledDocs table will also fill up (much like the Windows Recycle Bin) until you manually empty it. I'm hoping to do a longer write up on this along with either a web part or an ASPX page that can manage it.
So there is my simple method of creating a Recycle Bin in SharePoint. It isn't very fancy, but it works. :)