Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Extend SQL Server DDL Triggers for more functionality: Part 2


By:   |   Updated: 2013-05-21   |   Comments (2)   |   Related: 1 | 2 | More > Triggers

Problem

A previous tip, SQL Server DDL Triggers to Track All Database Changes, generated a lot of discussion involving ways to make the provided DDL trigger more useful.  Check out this tip to expand your knowledge on DDL Triggers.

Solution

I've posted one follow-up tip already, which dealt with questions about permissions on the audit table, the ability to query the two most recent versions of a logged stored procedure, and the ability to capture a CREATE TABLE statement and replay that statement to make a mirror table. There were a couple more issues I wanted to treat:

Jaya asked how a copy of a SQL Server table could be maintained when the original table was subsequently altered.

This can get a little tricky. Like the CREATE_TABLE event, ALTER_TABLE might introduce things that you can't simply replicate on a copy of the table: a constraint with a specific name, a change that relies on an underlying partition scheme, a foreign key, and a host of other things. So please take this code with a grain of salt: it is designed to handle simple changes like adding/removing columns and changing data types.

CREATE TRIGGER DDL_AlterTable
    ON DATABASE
    FOR ALTER_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA();
    DECLARE
      @sql NVARCHAR(MAX) = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
      @t   SYSNAME       = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)');
    SELECT @sql = REPLACE(@sql, @t, N'Prefix' + @t);
    EXEC sp_executesql @sql;
END
GO

In theory, you could combine this with the CREATE_TABLE event discussed in my previous tip, since the code is exactly the same. Remember that if you need to make a change to the source table that can't be - or that you don't want to be - replicated to the copy, you can always disable this trigger and re-enable it:

DISABLE TRIGGER [DDL_AlterTable] ON DATABASE;
GO
ALTER TABLE ...
GO
ENABLE TRIGGER [DDL_AlterTable] ON DATABASE;

Or temporarily change it to filter out the table you're working on, so that you don't have to worry about losing changes other people are making to other tables:

ALTER TRIGGER DDL_AlterTable
    ON DATABASE
    FOR ALTER_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA();
    DECLARE
      @sql NVARCHAR(MAX) = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
      @t   SYSNAME       = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)');
    IF @t <> N'YourTableName'
    BEGIN
      SELECT @sql = REPLACE(@sql, @t, N'Prefix' + @t);
      EXEC sp_executesql @sql;
    END
END
GO

Jim Curry asked for a follow-up article on how to set up policy to verify the SQL Server DDL triggers exist and are enabled on all databases?

There is a challenge with using Policy-Based Management's built-in functionality for this: there is no way to perform the union of a double negative - that we're after here - e.g. fail if a trigger with a certain name doesn't exist *or* if it does and it is disabled. PBM does not have a straightforward way to use the facets directly to enforce that a certain object exists.

Instead, we need to create a policy that uses ExecuteSQL() to check each database for the trigger (and can check at the same time that the trigger is disabled). We can check this manually using:

SELECT COUNT(*)
  FROM sys.triggers
  WHERE name = N'DDLTrigger_Sample'
  AND parent_class = 0
  AND is_disabled = 0;

But in order to make use of PBM for this, we need to create a condition. In Object Explorer, expand Management > Policy Management, right-click Conditions, and choose New Condition... Give it a name, choose the facet of Database, and then click on the little ... button to launch the advanced editor.

Create condition dialog

The code there is:

ExecuteSql('Numeric', 'SELECT COUNT(*) FROM sys.triggers
WHERE name = N''DDLTrigger_Sample''
AND parent_class = 0 AND is_disabled = 0;')

Now, click OK, enter 1 into the Value field, and click OK again:

Create condition dialog

Now we create a policy, by right-clicking Policies and selecting New Policy:

Create policy dialog

We give it a name, and pick the condition we just created. For the targets, you can create an additional condition so that only non-system databases are checked, or only databases that meet certain criteria, but for brevity I am just going to use the default "Every database" condition. And again, for brevity, I am just going to select Evaluation Mode of "On demand."

Once you click OK, you can evaluate the policy, by right-clicking it in Management Studio and choosing "Evaluate." After dismissing warnings that the policy contains scripts, the result is:

Policy evaluation results

Unfortunately, the UI design is poor, and you need to scroll around and resize just to see which databases succeeded or failed. But when you automate this you won't have to deal with any of the UI deficiencies.

Next Steps


Last Updated: 2013-05-21


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, June 10, 2013 - 3:36:52 PM - Aaron Bertrand Back To Top

Jürgen, you need to an additional DDL event, RENAME, to the trigger definition.

ALTER TRIGGER DDL_AlterTable
   ON DATABASE
   FOR ALTER_TABLE, RENAME

And then if want to filter on only tables, you'll have to check EVENTDATA() because there is no independent RENAME_TABLE event - it will capture any rename.


Monday, June 10, 2013 - 5:11:03 AM - Jürgen hintze Back To Top

What if someone would use the stored procedute sp_rename.

Is this covered as well ?


Learn more about SQL Server tools