Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Creating a recycle bin for SQL Server 2005\2008 Expand / Collapse
Author
Message
Posted Wednesday, May 20, 2009 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #720603
Posted Wednesday, May 20, 2009 8:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 6:45 AM
Points: 105, Visits: 633
Fantastic idea, thanks.
Post #720639
Posted Wednesday, May 20, 2009 9:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 7, 2011 3:15 AM
Points: 375, Visits: 1,255
"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.


--
Post #720686
Posted Wednesday, May 20, 2009 10:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 5, 2009 7:26 AM
Points: 1, Visits: 27
Superb idea
Post #720742
Posted Wednesday, May 20, 2009 12:47 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 29, 2014 9:29 AM
Points: 214, Visits: 647
Very handy! Good job, here.

Thanks for sharing
Lee





Lee Everest

Post #720822
Posted Wednesday, May 20, 2009 2:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:37 AM
Points: 378, Visits: 1,000
Looks like this would be great for when rollback scripts aren't provided during deployment time!
Post #720885
Posted Wednesday, May 20, 2009 3:05 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 29, 2014 9:29 AM
Points: 214, Visits: 647
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

Post #720938
Posted Wednesday, May 20, 2009 5:47 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768
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.
Post #721040
Posted Wednesday, May 20, 2009 8:11 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 1,043, Visits: 2,716
Good post. A very creative and useful implementation of DDL triggers.



Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #721064
Posted Thursday, May 21, 2009 2:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 6, 2012 10:04 AM
Points: 144, Visits: 455
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
Post #721720
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse