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)', '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.
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.