March 11, 2009 at 11:04 pm
Hi,
How to build a trigger in the table, even having the SA, it’s won’t allow to drop the table. Unless the trigger is disabled.
ARUN SAS
March 11, 2009 at 11:18 pm
Where did you get the idea that a table that has a trigger can not be dropped? Such table can be dropped with no problems. If you can’t drop the table, check if there is any object that references the table and was created with schema binding.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 11, 2009 at 11:28 pm
Thanks ADI,
What I need is simple table and trigger (with out any ref), when the table drop by somebody in development, the trigger won’t allow to drop the table.
ARUN SAS
March 11, 2009 at 11:50 pm
Sorry, I misunderstood the first message (although the message was very clear). You can’t do it with DML trigger, but you can create a DDL trigger and rollback the drop table statement. Bellow is an example for such a trigger (taken from BOL, I only modified the event that the trigger responds for). This trigger will prevent droping any table in the database without disabeling the trigger:
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE
AS
RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
ROLLBACK
GO
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 12, 2009 at 12:55 am
Thanks ADI,
Is same works in 2k? If no means, how these handle by other way in 2k?
ARUN SAS
March 12, 2009 at 2:05 am
arun.sas (3/12/2009)
Is same works in 2k? If no means, how these handle by other way in 2k?
No. DDL triggers are SQL 2005 and higher.
To prevent people dropping tables, don't give them permissions. As for sa, you can deny nothing to sa. There's no way to prevent a sysadmin from doing what he pleases. A schemabound view will prevent accidental drops, but if the admin wants to drop the table, he just has to drop the view first then the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2009 at 9:11 pm
Thanks Gail Shaw & ADI
ARUN SAS
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply