SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating a recycle bin for SQL Server 2005\2008


Creating a recycle bin for SQL Server 2005\2008

Author
Message
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44397 Visits: 14925
doveb (5/20/2009)
What a great idea. Thank you for the tip. I did have one question. Are there any additional considerations for items that are schema bound or have foreign keys associated to them?


You can't drop the parent table in a foreign key relationship, but you can drop the child.

You can't drop a table that has a schema-bound view associated with it.

Here's some tests:


CREATE TABLE parent
(
Id INT PRIMARY Key
)
GO

CREATE TABLE child
(
id INT,
parent_id INT REFERENCES parent(id)
)
GO
-- will not work because it is the parent table
DROP TABLE parent;

GO
-- you can drop the child table
DROP TABLE child;
GO
CREATE TABLE child
(
id INT,
parent_id INT REFERENCES parent(id)
)
GO

CREATE VIEW vw_child
WITH SCHEMABINDING
AS
SELECT id, parent_id FROM dbo.child
GO

-- can't drop the table if there is schema bound view
DROP TABLE child;

Go





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Akeel.Mughal
Akeel.Mughal
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 634
Fantastic idea, thanks.
IN_Sandeep
IN_Sandeep
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1407 Visits: 1255
"Excellent Script By Chris Kinley".

A Special Thanks goes to Steve & SQL Guru like {Gail, Jeff & others} for posting this kind of article in the forum which specify the future technologies of SQL Server.

Cheers!
Sandy.

--
paddymullaney
paddymullaney
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 27
Superb idea
ab5sr
ab5sr
SSC Eights!
SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)

Group: General Forum Members
Points: 900 Visits: 703
Very handy! Good job, here.

Thanks for sharing
Lee

Lee Everest


ggoble
ggoble
Right there with Babe
Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)

Group: General Forum Members
Points: 792 Visits: 1324
Looks like this would be great for when rollback scripts aren't provided during deployment time!
ab5sr
ab5sr
SSC Eights!
SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)

Group: General Forum Members
Points: 900 Visits: 703
greggoble2 (5/20/2009)
Looks like this would be great for when rollback scripts aren't provided during deployment time!


No kidding. And much easier to implement as well. How many times have I written those scripts (rollbacks) where they've taken longer to develop than the frickin' code or project that is getting pushed.

Lee Everest


Noel McKinney
Noel McKinney
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2259 Visits: 798
Very impressive Chris, thanks for sharing this!

Regarding "... not naming stored procedures with the sp_ prefix ..." I have also, on rare occasion, placed administrative/maintenance stored procedures in the master database so that they can be called on user databases. However, I use two underscores, for example sp__whyamihere, to avoid collision with current or future stored procedures provided by Microsoft.
Tim Mitchell
Tim Mitchell
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3859 Visits: 3056
Good post. A very creative and useful implementation of DDL triggers.



Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices


Joseph-465703
Joseph-465703
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 465
Hi Chris,

Good topic & idea. Chris, I tried to create the trigger which is shown in the article. but i am getting syntax error. could you please provide me the complete script for trigger and stored procedure as well.

thanks
Joseph
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search