SQL Server 2008 R2 DDL Trigger to create DML Triggers on all new user tables

Question

As the title says I would like to create a DDL trigger which in turn creates DML triggers for most new tables.

The trigger I have so far will create triggers on ALL tables, which is not what I'm after:

CREATE TRIGGER [CreateReplTriggers] ON DATABASE 
 FOR CREATE_TABLE 
AS 
BEGIN
 SET NOCOUNT ON
 SET ANSI_PADDING ON
 SET ANSI_NULLS ON
 SET QUOTED_IDENTIFIER ON
 DECLARE @TABLE_NAME SYSNAME
 SELECT @TABLE_NAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
 EXEC sp_CreateReplTrigger @TABLE_NAME
END

sp_CreateReplTrigger is a stored proc which creates DML trigger for table with passed name.

Unfortunately this fires off when some System Tables are created as well, e.g. dbo.MSreplication_subscriptions.

There doesn't seem to be anything useful in EVENT_DATA() which would help me differentiate user tables from system tables.

There's also many System Tables and their names don't have much in common: http://msdn.microsoft.com/en-us/library/aa260604(v=sql.80).aspx ... I can't exclude table that have 'MS' or 'sys' in name, because that would be hacky and bad, and would definitely cause issues 5 years later when someone forgets that user tables aren't supposed to have 'MS' or 'sys' in name.

Edit:

The final solution was a combination of the answer below + adding a whitelist array or table. So the stored proc was only called if the table name was in the whitelist. is_ms_shipped was not used.

• Aaron Bertrand: I highly recommend not using the sp_ prefix in your procedure names (see sqlperformance.com/2012/10/t-sql-queries/sp_prefix) but adding the dbo. prefix when calling your stored procedure (see sqlblog.org/2019/09/12/…).

Answer 1

From SQL 2005 onwards I would use sys.tables which is scoped to user tables and includes the is_ms_shipped column, something like:

CREATE TRIGGER [CreateReplTriggers] ON DATABASE 
 FOR CREATE_TABLE 
AS 
BEGIN
 SET NOCOUNT ON
 SET ANSI_PADDING ON
 SET ANSI_NULLS ON
 SET QUOTED_IDENTIFIER ON
 DECLARE @table_name SYSNAME
 DECLARE @xml XML
 SET @xml = EVENTDATA() 
 SELECT 
 @table_name = QUOTENAME( t.c.value('(SchemaName/text())[1]', 'SYSNAME') ) + '.' + 
 QUOTENAME( t.c.value('(ObjectName/text())[1]', 'SYSNAME') )
 FROM @xml.nodes( 'EVENT_INSTANCE') t(c)
 IF EXISTS ( SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(@TABLE_NAME) AND is_ms_shipped = 0 )
 BEGIN
 RAISERROR( 'Creating replication trigger for table %s.', 10, 1, @TABLE_NAME )
 EXEC sp_CreateReplTrigger @TABLE_NAME
 END
 ELSE
 -- Raise low-level error which won't cause trigger to fail, but will print message to indicate which table has been ignored
 RAISERROR( 'Table %s ignored for replication trigger proc.', 10, 1, @TABLE_NAME )
END

In a quick test on some of my servers, this excluded MS* and sys* tables including MSreplication_subscriptions etc

• Aaron Bertrand: +1 sys.tables is a much better choice than sysobjects. sysobjects is only there for backward compatibility, and shouldn't be perpetuated unless a question is specifically about 2000. There is no reason to keep using it in 2005+.
• Mike Trusov: Thanks for RAISERROR bits too, didn't think of adding those.
• Mike Trusov: This seems to have the same issue as sysobjects-'category'... 'is_ms_shipped' is 0 for all tables at the time the trigger is fired :(

Answer 2

Use sysobjects to check if the passed name is a system table:

CREATE TRIGGER [CreateReplTriggers] ON DATABASE 
 FOR CREATE_TABLE 
AS 
BEGIN
 SET NOCOUNT ON
 SET ANSI_PADDING ON
 SET ANSI_NULLS ON
 SET QUOTED_IDENTIFIER ON
 DECLARE @TABLE_NAME SYSNAME
 SELECT @TABLE_NAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
 if exists (select name from sysobjects where name=@table_name and xtype='U')
 begin
 EXEC sp_CreateReplTrigger @TABLE_NAME
 end
END

This is using the fact that a system table should have an xtype of S and user tables xtype of U.

• Mike Trusov: This unfortunately doesn't work for tables which are prefixed with 'MS', e.g. MSreplication_subscriptions, there's a 'category' flag however, which is set to 2 for all system tables, but for some reason that gets set at a later stage, so it cannot be used in FOR or AFTER CREATE_TABLE triggers.