Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Splendor Not SQL
Starting Member
1 Post |
 Posted - 2011-10-17 : 20:41:46
|
| Hi everyone,Is there a way to prevent a specific table from being dropped? I know I can add a trigger to the entire database but that will prevent ALL tables from being dropped. That would be OK except it also prevents temporary tables from being dropped! Therefore, I need a way to prevent specific tables from being dropped.I have tried to add a trigger and use INSTEAD OF DROP and INSTEAD OF DROP_TABLE but I get errors for both. I believe it isn't possible to do this when referencing a specific table. :(Thanks! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-10-17 : 23:18:18
|
You can target a specific table from within a DDL trigger by using EVENTDATA():One way is to put something like this in the body of your DDL trigger for the Drop_Table event.if eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') = N'<yourTable>'begin print 'can not drop this table' rollbackendBe One with the OptimizerTG |
 |
|
|
|
|
|
Preventing DROP on a specific table