June 5, 2009 at 3:21 am
Article: Creating a recycle bin for SQL Server 2005\2008
Hi,
Thanks everybody for the great feedback (in both the forums and to my personal email) on my article. I’m glad it was well received – makes the effort worth while.
I’ve been quite busy since the post but now have time to reply.
I’ve been asked a few questions so I’ll attempt to answer all of them here in the one post.
Chris
Q: Can I have the complete code?
A: Both scripts are with the original article. See the resources section right at the bottom.
Q: What about renaming a procedure, view etc?
A: Good point. Chad Folden emailed me and said he was trying to modifiy the code to do just this. He ran into an issue and hasn't got back to it. He's sent me the code to have a look too. Stay tuned if I get time I'll work on it and post what I find.
Q: Are there any additional considerations for items that are schema bound or have foreign keys associated to them?
A: Yes. As a table is only renamed, not dropped, the FKs will stay on the renamed table. This could get nasty quite quickly. Enhanced code in the DDL trigger could ignore the table rename (pseudo-drop) if it has FKs and warn the DBA.
Jack Corbett has kindly addressed this in the posts.
Q: Why you didn’t do it at the database level?
A: I thought about it for databases. Of course it’s possible but I was more interested in achieving object level UNDROP functionality at the time.
Tip: Great tip by Noel McKinney for putting double underscores in the SP name when in the master. From Noel "However, I use two underscores, for example sp__whyamihere, to avoid collision with current or future stored procedures provided by Microsoft."
Bug:
Chad Folden was kind enough to find a bug and send a fix to me, Chad’s also been working on modifying the script to cater for ALTERs.
From Chad:
"There were two small things that screwed me up here:
We use windows authentication for SQL users and our domain name contains a dash. The dash in @login_name causes the ALTER SCHEMA statement to fail (with incorrect syntax near ‘-‘)
We use windows authentication for SQL users and our user names contains a dots. This also causes the ALTER SCHEMA statement to fail because it thinks the dots are part of the delimeters in the object name
So once I replaced the dots and dashes for the @Login_name variable, your trigger worked flawlessly! Here’s the line in question and the fix I used"
SET @Login_name = @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname') --who dropped
SET @Login_name = REPLACE(REPLACE(@Login_name,'-','_'),'.','_')
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply