Block to drop any object with ddl triger

20 May 2006 1:19 AM

retf

Hi all,

I want block attempt to drop any objects(tables, sp, views...) of my data
base.

Have way to do this?

I did see same thing about DDL trigers, any one can sed-me one sample?

Thanks to all

20 May 2006 1:23 AM

retf

Hi all,
Thanks,

I find one sample....

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT 'You must disable Trigger "safety" to drop or alter tables!'

ROLLBACK ;

Thanks again

Show quote

"retf" <re***@terra.com.br> escreveu na mensagem
news:%23cQ9yq6eGHA.1272@TK2MSFTNGP03.phx.gbl...
> Hi all,
>
> I want block attempt to drop any objects(tables, sp, views...) of my data
> base.
>
> Have way to do this?
>
> I did see same thing about DDL trigers, any one can sed-me one sample?
>
> Thanks to all
>
>

20 May 2006 11:46 PM

David Browne

This should really be

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS
BEGIN

RAISERROR('You must disable Trigger "safety" to drop or alter
tables!',16,1)
ROLLBACK ;
END

Without an error, a client may think the statement suceeded.

David

Show quote

"retf" <re***@terra.com.br> wrote in message
news:%23P2uRt6eGHA.3484@TK2MSFTNGP02.phx.gbl...
> Hi all,
> Thanks,
>
> I find one sample....
>
> CREATE TRIGGER safety
>
> ON DATABASE
>
> FOR DROP_TABLE, ALTER_TABLE
>
> AS
>
> PRINT 'You must disable Trigger "safety" to drop or alter tables!'
>
> ROLLBACK ;
>
>
>
> Thanks again
>
> "retf" <re***@terra.com.br> escreveu na mensagem
> news:%23cQ9yq6eGHA.1272@TK2MSFTNGP03.phx.gbl...
>> Hi all,
>>
>> I want block attempt to drop any objects(tables, sp, views...) of my data
>> base.
>>
>> Have way to do this?
>>
>> I did see same thing about DDL trigers, any one can sed-me one sample?
>>
>> Thanks to all
>>
>>
>
>

21 May 2006 10:49 PM

Erland Sommarskog

David Browne (davidbaxterbrowne no potted m***@hotmail.com) writes:
Show quote

> This should really be
>
>
> CREATE TRIGGER safety
>
> ON DATABASE
>
> FOR DROP_TABLE, ALTER_TABLE
>
> AS
> BEGIN
>
> RAISERROR('You must disable Trigger "safety" to drop or alter
> tables!',16,1)
> ROLLBACK ;
> END
>
> Without an error, a client may think the statement suceeded.

Quibble: there will be an error message without the RAISERROR. To
wit, in SQL 2005 a ROLLBACK in a trigger raises an error message.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx