Extend SQL Server DDL Triggers for more functionality: Part 2
By: Aaron Bertrand | Updated: 2013-05-21 | Comments (2) | Related: 1 | 2 | More > Triggers
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.
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
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)', 'NVARCHAR(MAX)'), @t SYSNAME = @EventData.value('(/EVENT_INSTANCE/ObjectName)', 'NVARCHAR(255)'); SELECT @sql = REPLACE(@sql, @t, N'Prefix' + @t); EXEC sp_executesql @sql; END GO
In theory, you could combine this with the
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)', 'NVARCHAR(MAX)'), @t SYSNAME = @EventData.value('(/EVENT_INSTANCE/ObjectName)', '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
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.
- Review the following tips and other resources:
- Extending SQL Server DDL Triggers for more functionality: Part 1
- SQL Server DDL Triggers to Track All Database Changes
- Creating custom T-SQL conditions and policies for SQL Server Policy Based Management
- Using the EventData() Function with DDL triggers in SQL Server 2005
- Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005
- Designing DDL Triggers
- Implementing DDL Triggers
About the author
View all my tips
Article Last Updated: 2013-05-21