yes.

i just created and tested this trigger as a prototype.

i've created 4 tables, and tried to delete them, only one is successful, the others raise the expected error.

CREATE TRIGGER [TR_ProtectCriticalTables]

ON DATABASE

FOR

DROP_TABLE

AS

DECLARE @eventData XML,

@uname NVARCHAR(50),

@oname NVARCHAR(100),

@otext VARCHAR(MAX),

@etype NVARCHAR(100),

@edate DATETIME

SET @eventData = eventdata()

SELECT

@edate=GETDATE(),

@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),

@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),

@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)'),

@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

IF @oname IN ('tblBananas','tblApples','tblOranges')

BEGIN

DECLARE @err varchar(100)

SET @err = 'Table ' + @oname + ' is super duper protected and cannot be dropped.'

RAISERROR (@err, 16, 1) ;

ROLLBACK;

END

GO

ENABLE TRIGGER [TR_ProtectCriticalTables] ON DATABASE

CREATE TABLE [dbo].[tblBananas] (

[myguid] uniqueidentifier NULL,

[myguid2] uniqueidentifier NULL)

CREATE TABLE [dbo].[tblCherries] (

[myguid] uniqueidentifier NULL,

[myguid2] uniqueidentifier NULL)

CREATE TABLE [dbo].[tblApples] (

[myguid] uniqueidentifier NULL,

[myguid2] uniqueidentifier NULL)

CREATE TABLE [dbo].[tblOranges] (

[myguid] uniqueidentifier NULL,

[myguid2] uniqueidentifier NULL)

DROP TABLE tblBananas

DROP TABLE dbo.[tblCherries]

DROP TABLE [dbo].[tblApples]

DROP TABLE [tblOranges]