Mirroring table changes through DDL triggers

Let’s say that we have an application that tracks and reports customer bills. Customers are able to view reports based on their bill. As time goes on, the customers have a need to create adjustments to their bill. It should be simple enough to add some identifying data to the bill to determine which bill is an adjustment, right? Well, not quite. It turns out that the Bill table is being loaded automatically on a regular (hourly) basis by an SSIS package. If a modification comes through the SSIS package, the existing records are removed from the Bill table and a new record is inserted. Changing this process to account for adjustments from the web would require additional development time and slow down the load process. So, if we can’t add new records to the Bill table, how should we store the adjusted records? We determine that the best method is to put them in a second table that will mirror the columns in the Bill table. This table will be on a separate filegroup to allow for frequent back ups. A new table is created with the exact same columns and the word ‘Web’ is appended to the table name (these are web adjustments). Additionally, a cross-reference table is created between the two with a unique constraint on the BillId and WebBillId columns, thus ensuring that only one adjustment can be associated with one bill. So, what happens when we need to add an additional column, index, or constraint to the Bill table? The exact same changes need to be applied to the BillWeb table. If they aren’t, there may be performance differences, data integrity problems, or an important column might not get added and then our reports won’t be accurate or else won’t run at all. This presents a problem. Customers want to be able to modify all of the fields in the billing tables and we need to make sure that they can. Short of being very careful every time we make a change, what other option is available to us? DDL triggers! DDL (data definition language) triggers are triggers that fire when database level events occur – a table is added, removed, columns are added, an index is altered, etc. In SQL Server 2005 it’s possible to trap and respond to these events. I went ahead and scripted this out and tested it on my local machine. Everything worked. Special thanks to Brent Ozar for reviewing this code. So, without further ado, here is the DDL trigger to modify a mirrored table when changes are made to the source table. Feel free to hit me up in the comments with any questions.``` -- Create pvt and pvtWeb as test tables CREATE TABLE [dbo].[pvt]( [VendorID] [int] NULL, [Emp1] [int] NULL, [Emp2] [int] NULL, [Emp3] [int] NULL, [Emp4] [int] NULL, [Emp5] [int] NULL ) ON [PRIMARY]; GO

CREATE TABLE [dbo].[pvtWeb]( [VendorID] [int] NULL, [Emp1] [int] NULL, [Emp2] [int] NULL, [Emp3] [int] NULL, [Emp4] [int] NULL, [Emp5] [int] NULL ) ON [PRIMARY]; GO

IF EXISTS(SELECT * FROM sys.triggers WHERE name = ‘ddl_trigger_pvt_alter’) DROP TRIGGER ddl_trigger_pvt_alter ON DATABASE; GO

– Create a trigger that will trap ALTER TABLE events CREATE TRIGGER ddl_trigger_pvt_alter ON DATABASE FOR ALTER_TABLE AS DECLARE @data XML; DECLARE @tableName NVARCHAR(255); DECLARE @newTableName NVARCHAR(255); DECLARE @sql NVARCHAR(MAX);

SET @sql = ‘’; – Store the event in an XML variable SET @data = EVENTDATA();

– Get the name of the table that is being modified SELECT @tableName = @data.value(’(/EVENT_INSTANCE/ObjectName)[1]’, ‘NVARCHAR(255)’); – Get the actual SQL that was executed SELECT @sql = @data.value(’(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’, ‘NVARCHAR(MAX)’);

– Figure out the name of the new table SET @newTableName = @tableName + ‘Web’;

– Replace the original table name with the new table name – str_replace is from Robyn Page and Phil Factor’s delighful post on – string arrays in SQL. The other posts on string functions are indispensible – to handling string input

– http://www.simple-talk.com/sql/t-sql-programming/tsql-string-array-workbench/ – http://www.simple-talk.com/sql/t-sql-programming/sql-string-user-function-workbench-part-1/ – http://www.simple-talk.com/sql/t-sql-programming/sql-string-user-function-workbench-part-2/ SET @sql = dbo.str_replace(@tableName, @newTableName, @sql);

– Debug the SQL if needed. –PRINT @sql;

IF OBJECT_ID(@newTableName, N’U’) IS NOT NULL BEGIN BEGIN TRY – Now that the table name has been changed, execute the new SQL EXEC sp_executesql @sql; END TRY BEGIN CATCH – Rollback any existing transactions and report the full nasty – error back to the user. IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

  DECLARE @ERROR\_SEVERITY INT,
          @ERROR\_STATE    INT,
          @ERROR\_NUMBER   INT,
          @ERROR\_LINE     INT,
          @ERROR\_MESSAGE  NVARCHAR(4000);

  SELECT @ERROR\_SEVERITY = ERROR\_SEVERITY(),
         @ERROR\_STATE    = ERROR\_STATE(),
         @ERROR\_NUMBER   = ERROR\_NUMBER(),
         @ERROR\_LINE     = ERROR\_LINE(),
         @ERROR\_MESSAGE  = ERROR\_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR\_SEVERITY,
            @ERROR\_STATE,
            @ERROR\_NUMBER,
            @ERROR\_LINE,
            @ERROR\_MESSAGE);
END CATCH

END GO