Prev: Log backup file is larger than the .ldf file
Next: SQL server 2005 multicore problem with 'where column like' sqlcommand
From: Pat on 5 May 2010 14:03
The SQL 2005 server I am using has mulitple databases. The database MyDB is
one of them. I make a DDL trigger to prevent deleting columns in tables in
MyDB. Here is the script to make the trigger:

CREATE TRIGGER Safety
ON DATABASE
FOR ALTER_TABLE
AS
RAISERROR ('Table schema cannot be modified in database. You must disable
Trigger "Safety" to drop or alter tables!', 16, 1);
ROLLBACK
;

The trigger works well for MyDB. Whenever deleting a column in a table
inside MyDB, the transaction is not allowed. Now, I want to set up the same
trigger not only monitering MyDB but also monitering other databases in the
server (i.e., apply this trigger to all databases). I have put the trigger
(same script) in the master or model database, but the trigger does not kick
off whenever deleting a column in table in whatever databases.

Pat
From: Uri Dimant on 6 May 2010 12:07
Pat
This kind of trigger is part on database level, not a server level, so you
will need to add this trigger to every database


"Pat" <Pattt(a)newsgroups.nospam> wrote in message
news:5AF33093-061E-4152-BEEA-C9D6A3BFAF1F(a)microsoft.com...
> The SQL 2005 server I am using has mulitple databases. The database MyDB
> is
> one of them. I make a DDL trigger to prevent deleting columns in tables in
> MyDB. Here is the script to make the trigger:
>
> CREATE TRIGGER Safety
> ON DATABASE
> FOR ALTER_TABLE
> AS
> RAISERROR ('Table schema cannot be modified in database. You must disable
> Trigger "Safety" to drop or alter tables!', 16, 1);
> ROLLBACK
> ;
>
> The trigger works well for MyDB. Whenever deleting a column in a table
> inside MyDB, the transaction is not allowed. Now, I want to set up the
> same
> trigger not only monitering MyDB but also monitering other databases in
> the
> server (i.e., apply this trigger to all databases). I have put the trigger
> (same script) in the master or model database, but the trigger does not
> kick
> off whenever deleting a column in table in whatever databases.
>
> Pat


 | 
Pages: 1
Prev: Log backup file is larger than the .ldf file
Next: SQL server 2005 multicore problem with 'where column like' sqlcommand