locked
Can I build one DDL trigger to prevent deleting column for multiple databases in the same server? RRS feed

  • Question

  • 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 DDL 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. Would be appreciated if you could help.

    NSIG

    Thursday, May 6, 2010 4:07 PM

Answers

  • A DDL trigger for ALTER_TABLE in 2005 cannot be specified at the server level.  It is a database DDL trigger and must be implemented in each database. 

    The problem that remains is one of deployment.  If you are concerned that a database will not be protected, you could create a stored procedure (in master or some other appropriate database) that examines each database on the server and creates the trigger there if it is missing (and maybe enables it if it is disabled).  Or the procedure could always replace the existing triggers, so that it could also be used to deploy an updated trigger.

    Schedule a job to run it periodically.

    One note, depending on the purpose of the server, this trigger could be a real protection, or a real nuisance.  Don't deploy it on a development server where programmers are changing things all day long.

    FWIW,
    RLF

    Thursday, May 6, 2010 9:58 PM
  • Yes, this is available in SQL Server 2008 at the server level.   The code of the trigger would have to decide which databases to restrict and which to ignore. 

    RLF

    Friday, May 7, 2010 11:50 PM

All replies

  • A DDL trigger for ALTER_TABLE in 2005 cannot be specified at the server level.  It is a database DDL trigger and must be implemented in each database. 

    The problem that remains is one of deployment.  If you are concerned that a database will not be protected, you could create a stored procedure (in master or some other appropriate database) that examines each database on the server and creates the trigger there if it is missing (and maybe enables it if it is disabled).  Or the procedure could always replace the existing triggers, so that it could also be used to deploy an updated trigger.

    Schedule a job to run it periodically.

    One note, depending on the purpose of the server, this trigger could be a real protection, or a real nuisance.  Don't deploy it on a development server where programmers are changing things all day long.

    FWIW,
    RLF

    Thursday, May 6, 2010 9:58 PM
  • CREATE TRIGGER Safety
    ON ALL SERVER    --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
    ;

    ON ALL SERVER this option should work on server level for all the databases it should work.

    Friday, May 7, 2010 1:46 PM
  • Sekhara,  This does not work on SQL Server 2005, only on SQL Server 2008 where the scope can be database or server. 

    So for NSIG this does not work.   The Version matters.

    RLF

    Friday, May 7, 2010 2:45 PM
  • Hi Russel, thanks for your comments. It is the 1st time I use DDL trigger to do some tasks. It is understandable that database DDL trigger should be implemented on the database level. But I think it is too restrict. It is very common to apply the same rule (e.g., not allow altering table, or not allow dropping table, etc) to varied databases, especially production databases. Why not making a DDL trigger on the server level but it can handle database DDL events? The concept looks like this:

    Create trigger Safety On Server ApplyTo [MyDB1], [MyDB2], [MyDB3] For Drop_Table

    MyDB1, 2, or 3 is the database name. This way makes the same trigger attached to whatever databases you want to select. Do you think SQL 2008 has similar concept like this?

     

    NSIG

    Friday, May 7, 2010 2:52 PM
  • Hi Sekhara, alter_table is the DDL event on database level. So, your script (CREATE TRIGGER Safety ON ALL SERVER   FOR ALTER_TABLE) does not work.

    NSIG

    Friday, May 7, 2010 2:56 PM
  • Yes, this is available in SQL Server 2008 at the server level.   The code of the trigger would have to decide which databases to restrict and which to ignore. 

    RLF

    Friday, May 7, 2010 11:50 PM