Extend SQL Server DDL Triggers for more functionality: Part 2

By: Aaron Bertrand | Comments (2) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > 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.

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:

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

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:

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

Review the following tips and other resources:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist 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 also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips